→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎02-24-2018 09:50 AM
‎02-28-2018 04:37 AM
‎02-28-2018 04:36 AM
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysIPLocations.IPLocation,
Case When fifthtable.Found = 1 Then 'Yes'
When fifthtable.Found = 0 Then 'Not Found' Else 'Not scanned'
End As [Has Agent EXE],
IsNull(scndtbl.Value, '-') As [Last Check-In DateTime],
IsNull(thrdtbl.Value, '-') As [SCCM Server],
IsNull(frthtbl.Value, '-') As [Site Code],
Case When fifthtable.Found = 0 Then 'red' End As foregroundcolor,
tblAssets.Lastseen
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Regkey
From tblRegistry
Where tblRegistry.Valuename = 'LastEvalTime' And
tblRegistry.Regkey Like '%SOFTWARE\Microsoft\CCM\CcmEval') scndtbl
On tblAssets.AssetID = scndtbl.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Regkey
From tblRegistry
Where tblRegistry.Valuename = 'LastSiteCode' And
tblRegistry.Regkey Like '%SOFTWARE\Microsoft\CCM\CcmEval') frthtbl
On tblAssets.AssetID = frthtbl.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Regkey
From tblRegistry
Where tblRegistry.Valuename = 'LastValidMP' And
tblRegistry.Regkey Like '%SOFTWARE\Microsoft\CCMSetup') thrdtbl
On tblAssets.AssetID = thrdtbl.AssetID
Left Join (Select tblFileVersions.AssetID,
tblFileVersions.Found,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName,
tblFileVersions.Filesize,
tblFileVersions.Lastchanged
From tblFileVersions
Where tblFileVersions.FilePathfull Like '%ccmsetup.exe%') fifthtable
On tblAssets.AssetID = fifthtable.AssetID
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblAssets.Lastseen <> '' And tsysAssetTypes.AssetTypename = 'windows' And
tblAssetCustom.State = 1
Order By tblAssets.AssetName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now