Query and report all workstations that haven’t rebooted the last 7 days
3rd October 2009 10:51
I recently got a very good question from a student – how can we check the workstations that haven’t restarted during the last 7 days. The intention is to automatically restart the computers. However not until the end-users have been contacted. For that reason I need to create a report and a query.
The report
The SQL statement
Create a new empty report and copy this SQL code into the SQL statement box:
SELECT TOP (100) PERCENT dbo.v_R_System.Name0, dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, DATEDIFF(Day,
dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, GETDATE()) AS [Days since last boot], dbo.v_GS_OPERATING_SYSTEM.Caption0
FROM dbo.v_GS_OPERATING_SYSTEM INNER JOIN
dbo.v_R_System ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE (DATEDIFF(Day, dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, GETDATE()) > 7) AND (dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE '%xp%') OR
(dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE '%Windows 7%') OR
(dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE '%vista%')
ORDER BY [Days since last boot]
The Query
To use this in a query based collection or normal query paste this WQL into the query statement:
SELECT DISTINCT SMS_R_System.Name, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, SMS_G_System_OPERATING_SYSTEM.Caption FROM SMS_R_System INNER JOIN SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId WHERE (SMS_G_System_OPERATING_SYSTEM.Caption like "%xp%" or SMS_G_System_OPERATING_SYSTEM.Caption like "%vista%" or SMS_G_System_OPERATING_SYSTEM.Caption like "%windows 7%") and (DateDiff(day, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GetDate()) >7)
This post was originally posted to: http://scug.dk/blogs/configurationmanager/archive/2009/10/02/report-all-workstations-that-haven-t-rebooted-the-last-7-days.aspx
Subscribe to Kent's RSS Feed
Hi guys,
firstly great site with some good info on for us SCCM learners.
The report code above does not work when pasted into an SCCM report – although if I go to the original post and copy their code all works OK.
Just thought you should know
Mike
Hi Mike,
Thanks for pointing it out. It was the ‘ thas was formatted wrongly.
Hey Kent. When pasting the query into an collection i get all computers that havent been rebooted the last 7 days, but also computers/virtual machines that havent been turned on for quiet a while >2 weeks…. Why is that?
Hi Carlos,
The query looks at the lastbootuptime in the database, if that value is greater the 7 it will disply the computer. Your virtual computers are in the database with a value greater than 7 which is why they will be displayed in the report. You could modify the query to rule out virtual machines.
I guess i am looking for a different query then. What we need is to find computers with an uptime of more than 48 hours and reboot them. But thanks for your reply anyway:)
The computer Uptime in Windows XP displayed as Days,Hours,min and seconds like(System Up Time: 1 Days, 21 Hours, 42 Minutes, 21 Seconds) where as in Windows 7 and above ,it showed as System Boot Time:9/16/2010, 9:29:11 PM
When I run an SCCM Report based on the SQL statement, I get a list of 650 computers with “Days since last boot” from 0 to 349. My computer is on the list as not being restarted since 10/13/2010. I’ve restarted my computer at four times since then with the last time being 10/15/2010. I’m not sure what the “> 7″ parameter in the sql statement is actually doing. I expected that with that parameter set to > 7 I would not see any computers that have been restarted in the past seven days.