Scan History - using case to evaluate scan success

[Jeff_Henze]

I cut/pasted/modified a report to show recent scans done with the below report (which works):

Select Top 1000000 tblScanHistory.ScanServer,
tsysScanningMethods.ScanningMethod,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Description,
tblScanHistory.ScanTime,
tblAssets.Lastseen,
tblAssets.AssetID,
tblScanHistory.Description As Description1
From tblAssets
Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Right Join tblScanHistory On tblAssets.AssetID = tblScanHistory.AssetId
Inner Join tsysScanningMethods On tsysScanningMethods.ScanningMethodId =
tblScanHistory.ScanningMethodId
Where tsysAssetTypes.AssetTypename Like 'Windows'
Order By tblScanHistory.ScanTime Desc

I want to add a column which simply says if the last scan attempt was a sucess or not. If the datetime stamps in tbl.scanhistory.scantime is the same as tblassets.lastseen then it was a good scan, otherwise it was not sucessful.

I believe the way to do this is with a case statement, but my syntax keeps getting kicked back. Can anyone tell me where/how to stick this case statement in? Here's the code that doesn't work:

Select Top 1000000 tblScanHistory.ScanServer,
tsysScanningMethods.ScanningMethod,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Description,
tblScanHistory.ScanTime,
tblAssets.Lastseen,
tblAssets.AssetID,
tblScanHistory.Description As Description1
From tblAssets
Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Right Join tblScanHistory On tblAssets.AssetID = tblScanHistory.AssetId
Inner Join tsysScanningMethods On tsysScanningMethods.ScanningMethodId =
tblScanHistory.ScanningMethodId ,
case when tbl.scanhistory.scantime = tblassets.lastseen then 'success' else 'fail'
End As [scanresluts]
Where tsysAssetTypes.AssetTypename Like 'Windows'
Order By tblScanHistory.ScanTime Desc

tia

[Jeff_Henze]

Thank you for pointing that out - I can't believe I missed that. Using that information, the report works without error now. The finished report, in case anyone is interested, is

Select Top 1000000 tblScanHistory.ScanServer,
tsysScanningMethods.ScanningMethod,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Description,
tblScanHistory.ScanTime,
tblAssets.Lastseen,
tblAssets.AssetID,
tblScanHistory.Description As Description1,
Case When tblScanHistory.ScanTime = tblAssets.Lastseen Then 'success'
Else 'fail' End As scanresluts
From tblAssets
Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Right Join tblScanHistory On tblAssets.AssetID = tblScanHistory.AssetId
Inner Join tsysScanningMethods On tsysScanningMethods.ScanningMethodId =
tblScanHistory.ScanningMethodId
Where tsysAssetTypes.AssetTypename Like 'Windows'
Order By tblScanHistory.ScanTime Desc

Thank you again for your insight!
-Jeff

[RCorbeil · Accepted solution]

case
  when tbl.scanhistory.scantime = tblassets.lastseen
  then 'success'
  else 'fail'
End As [scanresluts]

Should be tblScanhistory.

[Jeff_Henze]

That unfortunately didn't work for me (code below). Also of note - I looked for examples of case utilization and found a report that works fine. The working sample report has it's case statement located within the from statements.

Here's my modified code where I moved the case statement above the from statement. This unfortunately still does not work - it returns <Error while saving: "There was an error parsing the query. [ Token line number = 1,Token line offset = 319,Token in error = scanhistory ]">

Select Top 1000000 tblScanHistory.ScanServer,
tsysScanningMethods.ScanningMethod,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Description,
tblScanHistory.ScanTime,
tblAssets.Lastseen,
tblAssets.AssetID,
tblScanHistory.Description As Description1,
case when tbl.scanhistory.scantime = tblassets.lastseen then 'success' else 'fail'
End As [scanresluts]
From tblAssets
Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Right Join tblScanHistory On tblAssets.AssetID = tblScanHistory.AssetId
Inner Join tsysScanningMethods On tsysScanningMethods.ScanningMethodId =
tblScanHistory.ScanningMethodId
Where tsysAssetTypes.AssetTypename Like 'Windows'
Order By tblScanHistory.ScanTime Desc

This below is *not* my code - it is an example report which uses the case statement and works, and the case statement is located within the "from" statement. I was basing my original code upon how this worked, but of course, my version did not work at all:

Lansweeper report with case in it:
Select Top 1000000 tsysIPLocations.IPLocation,
tAssettype.[asset type],
Count(tblAssets.AssetID) As number
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join (Select tblAssets.AssetID,
Case When tblAssets.Assettype = -1 Then Case
When tblComputersystem.Domainrole > 1 Then 'Server' Else Case
When Coalesce(tblPortableBattery.AssetID, 0) <> 0 Then 'Laptop'
Else 'Desktop' End End Else tsysAssetTypes.AssetTypename
End As [asset type]
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
Left Join tblPortableBattery On tblPortableBattery.AssetID =
tblAssets.AssetID) tAssettype On tAssettype.AssetID = tblAssets.AssetID
Left Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where tblAssetCustom.State = 1
Group By tsysIPLocations.IPLocation,
tAssettype.[asset type]
Order By tsysIPLocations.IPLocation,
tAssettype.[asset type]

[RCorbeil]

I've not checked the rest of the query, but to answer your specific question, your CASE needs to be part of the SELECT list, before the FROM.

SELECT
  blah,
  blah,
  CASE WHEN x THEN y ELSE z END As blah
FROM
  blah
WHERE
  blah
ORDER BY
  blah