Create an SCCM collection containing inactive clients excluding certain computers

Create a new collection and use the following query to select inactive agents, the part in bold is to exclude all computers with name stating with PCNAME% from it if needed, % is equal to * in sql so if your naming convention is pcname01, pcname02  and so on pcname06% will exclude pcname060, pcname061, pcname0600 and so on. If you want to remove one or two names only you can use (“pcname01″,”pcname02”) instead of pcname%

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_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 0 and SMS_G_System_SYSTEM.Name not like “PCNAME%”

One of the fixes for inactive clients could be found here

First part of the query taken from 4sysops