Sunday, February 10, 2013

SCCM Collection query for inactive workstations

In a bigger organizations, where we deal with tens of thousands of computers sometimes it is hard to maintain the assets in the environment.
When a rollout project takes place, sometimes whole floor will be replaced in overnight. Most of the times the technicians will do the good job in cleaning up the old records from the AD so everything is nice and clean. What happens if they don’t clean the AD? Well in next inventory the machine count will be high then you will be questioned why the numbers are different now?.
To avoid this situation, we need to identify the workstations which are not active. Create a collection using the following query and will get all the workstations which were inactive for 90 days. The 90 days can be modified as required by editing the last line of the query.
***************************************************************
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.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=90) and AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT")) and SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=90) and AgentName = "Heartbeat Discovery"))
****************************************************

More SCCM collection queries can be found here

No comments:

Post a Comment