Client Activity Summary Report

  • Article

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)
    • lastLogonTimestamp
      • 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)
    • pwdLastSet
      • This tells us when the domain member last set its computer account object in AD
    • whenChanged
      • 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 'Computer Name' ,sysValid.Resource_Domain_OR_Workgr0 AS 'Domain' ,ConUsr.TopConsoleUser0 AS '[AI] Top Console User' ,sysValid.User_Domain0 + '\' + sysValid.User_Name0 AS 'Last Logged on User' ,wsStatus.LastHWScan AS 'Last HW Inventory Time' ,agentDisc.AgentTime AS 'Last AD System Discovery' ,cliSumm.LastDDR AS '[CH] Last Heartbeat Discovery' ,cliSumm.LastHW AS '[CH] Last HW Inventory' ,cliSumm.LastSW AS '[CH] Last SW Inventory' ,cliSumm.LastPolicyRequest AS '[CH] Last Policy Request' ,cliSumm.LastStatusMessage AS '[CH] Last Status Message' ,cliSumm.LastPingTime AS '[CH] Last Ping' ,cliSumm.LastSuccessfulPing AS '[CH] Last Ping Success' ,CAST((sysAgent.lastLogonTimestamp0 / 864000000000.0 - 109207) AS DATETIME) AS '[AD] Computer Last Logon' ,CAST((sysAgent.pwdLastSet0 / 864000000000.0 - 109207) AS DATETIME) AS '[AD] Password Last Set' ,sysAgent.whenChanged0 AS '[AD] Object Last Modified'

  FROM v_R_System_Valid AS sysValid

  LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP AS ConUsr ON sysValid.ResourceID = ConUsr.ResourceID LEFT OUTER JOIN v_GS_WORKSTATION_STATUS AS wsStatus ON sysValid.ResourceID = wsStatus.ResourceID LEFT OUTER JOIN v_AgentDiscoveries AS agentDisc ON sysValid.ResourceID = agentDisc.ResourceId LEFT OUTER JOIN v_CH_ClientSummary AS cliSumm ON sysValid.ResourceID = cliSumm.MachineID LEFT OUTER JOIN v_R_System AS sysAgent ON sysValid.ResourceID = sysAgent.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.