SCCM: Collection Query to get all machines who haven’t rebooted in X amount of Days

Here’s a query that I use to see all machines who haven’t rebooted in 7 days. You can change the 7 to however many days you want.

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where DATEDIFF(DD, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GETDATE()) > 7

This entry was posted in sccm and tagged , , . Bookmark the permalink.

2 Responses to SCCM: Collection Query to get all machines who haven’t rebooted in X amount of Days

  1. WAMBO says:

    Hi,

    thanks for this, very good info.
    Could you please help me?

    How do I create a new collection of All Systems and exclude machine that doesn’t have an sccm client?

    The script below will report machhie that Client is Null or disabled:

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from sms_r_system where Client = 0 or Client is null

    How do I create a new Collection of All System and include the script above?

    P.S. I don’t want to edit the current All System Collection because I want it to remain as is.

    Thanks

  2. smithw2004 says:

    This is how I did it, there may be a better way but this worked/works great for me. I am still new to SCCM 2007 and learning as I go, hope this helps you out.

    First I created a new collection named “SCCM Client Not Installed” , added the following SQL Query and then Updated the Collection Membership to populate all the machines that did not have a client installed.

    SELECT
    SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client

    FROM
    SMS_R_System

    WHERE
    SMS_R_System.Client is null

    I then edited the “All Systems” SQL Query by adding – not in (select ResourceID from SMS_CM_RES_COLL_XXXXXXX). Where XXXXXXX was the collection ID from “SCCM Client Not Installed”. The collection ID can be found by selecting properties/general tab of a collection.

    SELECT
    SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client

    FROM
    SMS_R_System

    WHERE
    SMS_R_System.ResourceId not in (select ResourceID from SMS_CM_RES_COLL_XXXXXXX)

    Lastly, I updated the collection membership of the All Systems Collection and all the machines that did not have a client installed were removed from the All Systems collection.

Leave a Reply

Your email address will not be published. Required fields are marked *