SCCM SQL Query to Find Last Restart Time of Devices

In this post, you’ll learn how to use SQL query to find last restart time of devices in SCCM. You can query and report all workstations that haven’t rebooted since last X days using SCCM and SQL.

Configuration Manager console has a feature that can identify Windows devices that require a restart. The pending restart column in SCCM console shows the Windows computers that require reboot. If you have to find the last restart time of a device, you have to use SQL query in SCCM.

When you deploy Software Updates using SCCM, the client reboots are usually suppressed with Suppress restart on Workstations option available under device restart behavior. With this setting enabled, the updates are installed on the device, but the device will not restart. If you assume that a user will restart the device, it may not happen so.

Consider another case of VM’s, where you deploy the updates using ADR and VMs are set to not reboot after updates installation. This will cause issues with overall compliance, and you have to list down the not rebooted workstations for a specific time manually.

In many organizations, SCCM admins find it challenging to find the computers that were not rebooted since specific time and days. By not rebooting a device, other new updates installation could fail, and even application installation will fail.

The SQL query can help you find days since last restart of all Windows devices. You can also create a SCCM report to list all the computers based on last restart date and time.

SQL queries in SCCM are super useful in getting quick information about device details. Some useful SQL queries that you can run in SCCM setup include:

• Use SQL query to find Windows 11 versions
• Find Last Heartbeat Time Stamp of Clients
• Find Application Deployment Details using SQL Query
• List All SCCM Packages using SQL Query

Use SQL Query to Find Last Restart Time of Devices

Follow the below steps to run the SQL query and find the last restart time of devices in SCCM:

• 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 SQL query to find last restart time of devices.

SELECT SYS.Name0 as [Computer Name], SYS.User_Name0 as [User Name], USR.Full_User_Name0 as [Full Name],
USR.mail0 as [Email Address], Convert(VarChar(10), os.LastBootUpTime0) [Last Restart Date],
OS.lastBootUpTime0 as [Last Restart], DATEDIFF(dd, LastBootUpTime0, GETDATE()) AS [Days Since Last Restart]
FROM v_R_System SYS
JOIN v_Gs_Operating_System OS on SYS.ResourceID = OS.ResourceID
LEFT JOIN v_R_User USR on SYS.User_Name0 = USR.User_Name0
WHERE SYS.Operating_System_Name_and0 like '%workstation%'
AND (DATEDIFF(dd, LastBootUpTime0, GETDATE())) >= 0
ORDER BY [Days Since Last Restart] DESC

Running the above SQL query will list the following details about your SCCM devices:

• Computer Name
• User Name
• Full Name
• Email Address
• Last Restart Date
• Last Restart Date and Time
• Days Since Last Restart

When you run this SQL query in your SCCM lab, you may find only a few non-restarted devices because lab VMs are restarted often. If you run this same query in production, you’ll find more computers that haven’t been rebooted for many days.

In the above SQL query, you can modify the LastBootUpTime value and enter a custom value, for example, 7 days, 30 days etc. The below screenshot shows the SQL query that finds all the devices that have not been rebooted for 7 days or more.

The Last Restart column in SQL query output shows the date and time of a device that was last restarted. The Days Since Last Restart column shows days that the device hasn’t been restarted.

Once you find the devices that haven’t been restarted for many days, you can restart these devices from Configuration Manager console.