Monitor SCCM 2012 Client

[ifm]

I have added a custom scan to see what version of the SCCM2012 Client is installed.
(It checks for the file %windir%\CCM\SCClient.exe)

A second custom scan checks for registry HKLM\SOFTWARE\Microsoft\CCM" value "SMSSLP" to see which site it uses.


Can you help me to create a report to list all computers in "IP location X" and show the result of these two custom scans in the same report?

If I click the report for the custom scan, I will only get computers where this file exists and not the computers that does not have it.

[ifm]

Thank you!

[Daniel_B · Accepted solution]

You can do that by adding a filter criterion on tblAssets.Assettype. It is -1 for Windows assets.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Lastseen,
  tsysOS.Image As icon,
  tFileVersions.Found,
  tFileVersions.FileVersion,
  tRegistry.Value As Datum,
  t2Registry.Value As Tid
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join (Select tblFileVersions.AssetID,
    tblFileVersions.Found,
    tblFileVersions.FileVersion
  From tblFileVersions
  Where tblFileVersions.FilePathfull Like '%SCClient.exe') tFileVersions
    On tblAssets.AssetID = tFileVersions.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join (Select tblRegistry.AssetID,
    tblRegistry.Value
  From tblRegistry
  Where tblRegistry.Valuename Like 'SCCMLastRunDate') tRegistry
    On tblAssets.AssetID = tRegistry.AssetID
  Left Join (Select tblRegistry.AssetID,
    tblRegistry.Value
  From tblRegistry
  Where tblRegistry.Valuename Like 'SCCMLastRunTime') t2Registry
    On tblAssets.AssetID = t2Registry.AssetID
Where tsysIPLocations.IPLocation Like 'EDU.IFM.LIU.SE' And
  tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Order By tblAssets.AssetName

[ifm]

I made some modifications to your report and would like to add a filter so that it only show Windows clients and nothing else. (so I don't have to see servers and linux/mac clients in the report)

Can you assist with that? 🙂

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tsysOS.Image As icon,
tFileVersions.Found,
tFileVersions.FileVersion,
tRegistry.Value As Datum,
t2Registry.Value As Tid
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join (Select tblFileVersions.AssetID,
tblFileVersions.Found,
tblFileVersions.FileVersion
From tblFileVersions
Where tblFileVersions.FilePathfull Like '%SCClient.exe') tFileVersions
On tblAssets.AssetID = tFileVersions.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select tblRegistry.AssetID,
tblRegistry.Value
From tblRegistry
Where tblRegistry.Valuename Like 'SCCMLastRunDate') tRegistry
On tblAssets.AssetID = tRegistry.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Value
From tblRegistry
Where tblRegistry.Valuename Like 'SCCMLastRunTime') t2Registry
On tblAssets.AssetID = t2Registry.AssetID
Where tsysIPLocations.IPLocation Like 'EDU.IFM.LIU.SE' And
tblAssetCustom.State = 1
Order By tblAssets.AssetName

[ifm]

Thank you! I will try it right away! 🙂

[Daniel_B]

You can do this by creating subqueries in the report which pull relevant data from tables tblFileVersions and tblRegistry. This can't be done through the GUI of the report editor, you need to directly modify the SQL query. Please find an example report below:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Lastseen,
  tsysOS.Image As icon,
  tFileVersions.Found,
  tFileVersions.FileVersion,
  tRegistry.Value
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join (Select tblFileVersions.AssetID,
    tblFileVersions.Found,
    tblFileVersions.FileVersion
  From tblFileVersions
  Where tblFileVersions.FilePathfull Like '%SCClient.exe') tFileVersions
    On tblAssets.AssetID = tFileVersions.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join (Select tblRegistry.AssetID,
    tblRegistry.Value
  From tblRegistry
  Where tblRegistry.Valuename Like 'SMSSLP') tRegistry On tblAssets.AssetID =
    tRegistry.AssetID
Where tsysIPLocations.IPLocation Like 'Example Location' And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName