SCCM collections for Monthly activities

Collections used for systems clean-up: Few collections for Monthly activities

All decomessioned systems: -SCCM Collections
select
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Clie
nt from SMS_R_System where SMS_R_System.Decommissioned = 1

Computers in AD with no recent AD discovery - SCCM Collections
select
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Clie
nt from SMS_R_System where (ResourceDomainORWorkgroup = "MyDomainName3" or ResourceDomainORWorkgroup = "MyDomainName2" or ResourceDomainORWorkgroup =
"MyDomainName1") and ResourceId not in (select ResourceId from  SMS_R_System where AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT" and DATEDIFF
(day,AgentTime,GetDate())<=7) and ResourceId not in (select ResourceId from  SMS_R_System where AgentName = "Heartbeat Discovery" and DATEDIFF
(day,AgentTime,GetDate())<=31) and Client is not null

Computers no Heartbeat Discovery with SCCM - SCCM Collections
select
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Clie
nt from SMS_R_System where ResourceId not in (select ResourceId from  SMS_R_System where AgentName = "Heartbeat Discovery") and ResourceId not in (select
ResourceId from  SMS_R_System where AgentName = "Manual Machine Entry")

Computers with only AD Group Discovery - SCCM Collections
select
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Clie
nt from SMS_R_System where ResourceId not in (select ResourceId from  SMS_R_System where AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT") and ResourceId not in
(select ResourceId from  SMS_R_System where AgentName = "Heartbeat Discovery") and ResourceId not in (select ResourceId from  SMS_R_System where AgentName =
"Manual Machine Entry")and ResourceId in (select ResourceId from  SMS_R_System where AgentName = "SMS_AD_SYSTEM_GROUP_DISCOVERY_AGENT" )

Devices With Incorrect Names (Where the device name is not the same as the inventory name (could be duplicate guids)) - SCCM Collections

select
SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Clie
nt from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Name  <>
SMS_G_System_COMPUTER_SYSTEM.Name

Duplicate Devices Without Client -SCCM Collections
select
SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Clie
nt from SMS_R_System where Client is NULL
Manual Entry Reference
select
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Clie
nt from SMS_R_System where SMS_R_System.AgentName = "Manual Machine Entry"

All Obsolete Clients from SCCM Collections
select
SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Clie
nt from SMS_R_System where Obsolete = 1