In this post, we will look at SQL query to find last heartbeat time stamp of SCCM client agents. The Heartbeat Time Stamp of SCCM agents is very useful while troubleshooting client health issues.
There are many SQL queries available with which you can query the Configuration Manager database and get quick information about clients and infrastructure.
When you install a primary site, Configuration Manager enables the Heartbeat Discovery method by default. If you want to use the default schedule of every seven days, there’s nothing else to configure.
If you wish to configure it, the only option you have is to configure the schedule for how often SCCM clients send the Heartbeat Discovery data record to a management point. The default schedule for heartbeat discovery is 7 days.
What is Heartbeat Discovery Method?
The heartbeat discovery is a discovery method in SCCM that runs on each client instead of on a site server to create a DDR. This method can force discovery of a computer as a new resource record. It can also re-populate the database record of a computer that was deleted from the database.
Heartbeat discovery is essential to keep client activity status current. It makes sure that the site doesn’t accidentally age out the resource records from the site database. Hence, you must never disable the Heartbeat discovery method in SCCM because it is useful in troubleshooting client health issues
When you are troubleshooting the client installation status issues, finding out the heartbeat time stamp of clients is useful. All actions for heartbeat discovery are logged on the client computer in the InventoryAgent.log file in the %Windir%\CCM\Logs folder.
In SCCM, you can find last heartbeat time stamp of clients using a SQL query.
Find Last Heartbeat Time Stamp of Clients using SCCM SQL Query
Let’s see how to find last heartbeat time stamp of all the clients existing in SCCM using a SQL query:
- Launch the SQL Server Management studio (SSMS) and connect to database engine.
- Expand Databases and right-click SCCM database and select New Query.
- In the query window, paste and execute the below SCCM SQL query to find the Last Heartbeat Time Stamp of clients.
Select
vrs.Name0 as 'ComputerName',
vrs.Client0 as 'Client',
vrs.Operating_System_Name_and0 as 'Operating System',
Vad.AgentTime as 'LastHeartBeatTime'
from v_R_System as Vrs inner join v_AgentDiscoveries as Vad on Vrs.ResourceID=Vad.ResourceId
where vad.AgentName like '%Heartbeat Discovery'
The above SQL query returns the last heartbeat time stamp of all the clients present in SCCM. The ‘LastHeartBeatTime‘ field in the SQL query output shows the time when the SCCM client last contacted the management point server.
You can now begin the troubleshooting by collecting the SCCM log files from those computers which haven’t contacted management point since a long time.
