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

image

The SQL statement

Create a new empty report and copy this SQL code into the SQL statement box:

The Query

To use this in a query based collection or normal query paste this WQL into the query statement:

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 | 2009-10-03T10:51:02+00:00 October 3rd, 2009|Configuration Manager (SCCM)|15 Comments

About the Author:

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.

15 Comments

  1. bigMIKE October 16, 2009 at 11:53 - Reply

    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

  2. Kent Agerlund October 16, 2009 at 13:05 - Reply

    Hi Mike,

    Thanks for pointing it out. It was the ‘ thas was formatted wrongly.

  3. Carlos February 9, 2010 at 14:52 - Reply

    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?

  4. Kent Agerlund February 9, 2010 at 15:07 - Reply

    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.

  5. Carlos February 9, 2010 at 15:35 - Reply

    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:)

  6. Eswar September 24, 2010 at 8:36 - Reply

    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

  7. Ron October 18, 2010 at 21:56 - Reply

    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.

  8. Bruce July 30, 2012 at 17:34 - Reply

    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

  9. Ian September 4, 2012 at 15:35 - Reply

    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

  10. Soso May 31, 2013 at 18:26 - Reply

    Thanks for query it helped me a lot 😉

  11. Sang July 18, 2016 at 15:46 - Reply

    Is there one for server?

  12. Florian September 13, 2016 at 13:48 - Reply

    @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

  13. Social Network January 25, 2017 at 2:27 - Reply

    Case Dislike Something but Crowd 398361155 of times !! Great Post 🙂

  14. Daniel McLaughlan October 3, 2017 at 23:53 - Reply

    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)

  15. zaid February 11, 2018 at 7:42 - Reply

    can you post something on getting the booting time duration (actual & average) taken by a computer / collection.

Leave A Comment