SQL query to find Last Reboot Time for SCCM Clients

Below is the query i had to put together to retrieve the last reboot times for the list of SCCM clients. To use the query, please ensure to change the list of computer names.

/* ————————-
This query verifies the Last reboot time for the list of assets.
Ensure to change the list of Client/Server Computer Names below.
Created by Talha Qamar.
http://www.talhaqamar.com
—————————- */

SELECT DISTINCT
a.Netbios_name0 as ‘Computer Name’,
a.Creation_date0 as ‘Object Created On’,
a.ad_site_name0 as ‘AD Site’,
b.sms_installed_sites0 as ‘Site Code’,
c.Lasthwscan as ‘Last HW Scan’,
e.LastBootUpTime0 as ‘Last Boot Up Time’,DATEDIFF(dd, LastBootUpTime0, GETDATE())AS ‘Number of Days since last bootup’
from v_r_system a
left join v_RA_System_SMSInstalledSites b on a.resourceid=b.resourceid
left join v_GS_WORKSTATION_STATUS c on a.resourceid=c.resourceid
Left join v_AgentDiscoveries d on d.resourceid=a.resourceid
left join v_gs_operating_system e on e.resourceid=a.resourceid
where d.AgentName=’Heartbeat Discovery’ and (a.client0=1 and a.Obsolete0=0 and a.Active0=1)
and a.Name0 IN (SELECT Name0
FROM v_r_system
WHERE a.Name0 IN
(‘ComputerName1’, –Change the Computer Name
‘ComputerName2’, –Change the Computer Name
‘ComputerName3’, –Change the Computer Name
‘ComputerName4’, –Change the Computer Name
‘ComputerName5’, –Change the Computer Name
‘ComputerName6’) –Change the Computer Name
GROUP BY Name0
HAVING COUNT(*) < 2)
order by e.LastBootUpTime0 asc

One comment

  1. great article talha, can you post something on recording a computer or collection’s booting time (for win7 & 10) as that’s a KPI we want to measure. the information is stored in event viewer , event ID “100”, task category “Boot Performance Monitor” as boot duration.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s