Workstation SCCM Report

[jacob_bks]

You can edit this one for servers as well... i'm too lazy to change the query aliases 🙂



Add the following to file/registry scanning:

%windir%\CCMSetup\ccmsetup.exe

HKLM/SOFTWARE\Microsoft\CCM\CcmEval LastEvalTime
HKLM/SOFTWARE\Microsoft\CCM\CcmEval LastSiteCode
HKLM/SOFTWARE\Microsoft\CCMSetup LastValidMP




Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysIPLocations.IPLocation,
Case When fifthtable.Found = 0 Then 'Not Found' Else 'Yes'
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

[Mister_Nobody]

In your script was issue:

Case When fifthtable.Found = 1 Then 'Yes'
When fifthtable.Found = 0 Then 'Not Found' Else 'Not scanned'
End As [Has Agent EXE]

[Mister_Nobody]

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