cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Jeff_Henze
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
case
when tbl.scanhistory.scantime = tblassets.lastseen
then 'success'
else 'fail'
End As [scanresluts]

Should be tblScanhistory.

View solution in original post

4 REPLIES 4
Jeff_Henze
Engaged Sweeper III
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
Honored Sweeper II
case
when tbl.scanhistory.scantime = tblassets.lastseen
then 'success'
else 'fail'
End As [scanresluts]

Should be tblScanhistory.
Jeff_Henze
Engaged Sweeper III
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
Honored Sweeper II
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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now