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
2 Responses
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
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.