The following T-SQL query will generate a report that shows the status of ConfigMgr clients. It uses various sources for this information, including:
- Last Hardware Inventory timestamp
- Last AD Discovery agent timestamp
- ConfigMgr 2007 R2 Client Health views data, including:
- Last Heartbeat DDR
- Last HW Inventory
- Last SW Inventory
- Last Policy Poll to the MP (Very useful for finding clients that are alive but cannot send other forms of communication)
- Last Status Message
- Last Ping (Client Health tool ‘ping’ not an ICMP echo)
- Last Successful Ping
- Active Directory System Discovery Additional Attributes (these have to be added to the discovery method)
- Use this instead of lastLogon, as lastLogon is not replicated throughout a domain. This tells us when the domain member last logged on (part of computer start up)
- This tells us when the domain member last set its computer account object in AD
- This tells us when the computer object was last modified in Active Directory. Password changes, disabling the account, resetting the account and OU moves are among the ways this attribute is updated.
The lastLogonTimestamp and pwdLastSet atttributes are usual ways to determine stale machines accounts in AD. Unfortunately, they are returned as AD time integer by the AD System Discovery, as opposed to datetime values like whenChanged. I use the following CAST statement to convert the integers into usable datetime values (where @columnName is the column we want to work with):
|CAST((@columName / 864000000000.0 - 109207) AS DATETIME)|
This reports provides a large output that can then be sorted in Excel or modified to include a WHERE clause to filter it to only machines where one or more sources is out of date x number of days. Here is the sample report query and I’ve highlighted the source views used for easier viewing:
SELECT sysValid.Netbios_Name0 AS
FROM v_R_System_Valid AS sysValid
LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP AS ConUsr ON sysValid.ResourceID = ConUsr.ResourceID
WHERE agentDisc.AgentName = 'SMS_AD_SYSTEM_DISCOVERY_AGENT'
ORDER BY sysValid.Netbios_Name0 DESC
Hope this helps as a starting point at looking at your client base’s health!
This post was contributed by Saud Al-Mishari, a Premier Field Engineer with Microsoft Premier Field Engineering, UK.