Query and report all workstations that haven’t rebooted the last 7 days

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

By Kent Agerlund|2009-10-03T10:51:02+01:00oktober 3rd, 2009|Configuration Manager (SCCM)|16 Comments

Om forfatteren: Kent Agerlund

Microsoft Regional Director, Enterprise Mobility MVP. Microsoft Certified Trainer and Principal consultant. I have been working with Enterprise client management since 1992. Co-founder of System Center User Group Denmark in 2009. Certified MCITP: Enterprise Administrator, MCSA+Messaing, and much more. Member of: Microsoft Denmark System Center Partner Expert Team The Danish Technet Influencers program System Center Influencers Program.

16 kommentarer

bigMIKE oktober 16, 2009 af 11:53

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
Kent Agerlund oktober 16, 2009 af 13:05

Hi Mike,

Thanks for pointing it out. It was the ‘ thas was formatted wrongly.
Carlos februar 9, 2010 af 14:52

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?
Kent Agerlund februar 9, 2010 af 15:07

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.
Carlos februar 9, 2010 af 15:35

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:)
Eswar september 24, 2010 af 8:36

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
Ron oktober 18, 2010 af 21:56

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.
Bruce juli 30, 2012 af 17:34

Hello – can you change this query to somehow also include a requirement for a recent agent contact time? I think if we could add that in I could be fairly certain all the members of the resulting collection are actually online. I just want to exclude Pc’s that are offline.

Thanks
Ian september 4, 2012 af 15:35

There is a slight error in this. ” GETDATE()) > 7) AND (dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE ‘%xp%’) OR (dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE ‘%Windows 7%’) OR ECT ECT..

This will select computers with reboot date greater then 7 days and (XP) OR (any windows 7) OR (Windows Vista PC’s). What we want is computers who haven’t rebooted in 7 days And their OS is (XP, W7,Vista.) a east fix for this is to remove the parentheses “(” leaving the Statement to look more like this ..

********
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]
*********

I am not the Best with SQL Statements but the above has helped me
Soso maj 31, 2013 af 18:26

Thanks for query it helped me a lot 😉
Sang juli 18, 2016 af 15:46

Is there one for server?
Florian september 13, 2016 af 13:48

@Sang: Just replace the Caption (dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE ‘%server%’)

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 ‘%server%’)

/*AND dbo.v_R_System.Name0 LIKE ‘%’*/

ORDER BY [Days since last boot] DESC
Social Network januar 25, 2017 af 2:27

Case Dislike Something but Crowd 398361155 of times !! Great Post 🙂
Daniel McLaughlan oktober 3, 2017 af 23:53

Stumbled across this today whilst resolving an issue in our SCCM 2012 R2 environment.

Found that for the WQL Query, I need to use the > operator rather that ‘>7’.

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 (DateDiff(day, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GetDate()) > 7)
zaid februar 11, 2018 af 7:42

can you post something on getting the booting time duration (actual & average) taken by a computer / collection.
Adrian april 27, 2019 af 21:57

Here is the T-SQL working query to actually show days > 7 instead of days 0 to X:

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()) > 10) AND (dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE ‘%Windows 7%’ OR dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE ‘%Windows 10%’)

ORDER BY [Days since last boot]