ConfigMgr (SCCM) – Very Handy Report for Software Update (Patching) Client Health

  • Article

Thought of sharing one of the very HANDY report which we use for client health with respect to patching or software update.

All the credit goes to my colleague Neetu.

This report will provide you below details

http://anoopmannur.files.wordpress.com/2011/10/snap5.jpg?w=600&h=35

SQL Statement

SELECT v_R_System.Name0, v_R_System.Active0, v_R_System.Obsolete0, v_R_System.Client_Version0,
v_GS_WORKSTATION_STATUS.LastHWScan as LastHardwareInventory,
v_GS_LastSoftwareScan.LastScanDate as LastSoftwareInventory,
LastHeartbeat=(select top 1 DATEADD(ss,@__timezoneoffset,AgentTime) from v_AgentDiscoveries where AgentName = ‘Heartbeat Discovery’ and v_AgentDiscoveries.ResourceID = v_R_System.ResourceID order by AgentTime desc),
DATEADD(ss,@__timezoneoffset,uss.LastScanTime) as LastScanTime,
uss.LastScanPackageLocation as LastScanPackageLocation,
uss.LastScanPackageVersion as LastScanPackageVersion,
sn.StateName as Status,
Convert(VarChar(10), OS.LastBootUpTime0, 101) ‘Last Boot Date’,
DateDiff(D, OS.LastBootUpTime0, GetDate()) ‘Last Boot (Days)’

FROM v_R_System LEFT JOIN v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
LEFT JOIN v_GS_LastSoftwareScan ON v_R_System.ResourceID = v_GS_LastSoftwareScan.ResourceID
LEFT JOIN v_UpdateScanStatus uss on v_R_System.ResourceID = uss.ResourceID
LEFT JOIN v_StateNames sn on sn.TopicType = 501 and sn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(v_R_System.Client_Version0, ’4.0′), 1)<’4′) then 7 else isnull(uss.LastScanState, 0) end)
LEFT JOIN v_Gs_Operating_System OS on v_R_System.ResourceID = OS.ResourceID
LEFT JOIN v_FullCollectionMembership fcm on v_R_System.ResourceID = fcm.ResourceID

where (@CollID=” or fcm.CollectionID=@CollID)

order by v_R_System.Name0

Prompt SQL statement

begin
if (@__filterwildcard = ”)
select CollectionID as CollectionID, Name as CollectionName from v_Collection order by Name
else
select CollectionID as CollectionID, Name as CollectionName from v_Collection
WHERE CollectionID like @__filterwildcard or Name like @__filterwildcard
order by Name
end

MOF file Download – Here

The report will look like

http://anoopmannur.files.wordpress.com/2011/10/snap7.jpg?w=600&h=34