‎02-15-2016 08:22 PM
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
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
Solved! Go to Solution.
‎02-16-2016 04:43 PM
case
whentbl.scanhistory .scantime = tblassets.lastseen
then 'success'
else 'fail'
End As [scanresluts]
‎02-16-2016 09:34 PM
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
‎02-16-2016 04:43 PM
case
whentbl.scanhistory .scantime = tblassets.lastseen
then 'success'
else 'fail'
End As [scanresluts]
‎02-15-2016 10:09 PM
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
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]
‎02-15-2016 09:41 PM
SELECT
blah,
blah,
CASE WHEN x THEN y ELSE z END As blah
FROM
blah
WHERE
blah
ORDER BY
blah
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now