Share This Post

Query to identify non Domain Controller and non ConfigMgr Primary Servers

Here is a ConfigMgr query to create a collection including all the window server 2003 and window 2000 SP4 servers but exclude domain controllers and ConfigMgr primary servers. It also excludes servers in certain subnet. It excludes 64 bit servers as well.

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 inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like "%Server 5.0%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion >= "Service Pack 4" and SMS_R_System.IPSubnets != "10.1.1.0" and SMS_G_System_COMPUTER_SYSTEM.DomainRole < 4 and SMS_R_System.ResourceDomainORWorkgroup = "ADDOMAIN" and SMS_R_System.ResourceId not in (select SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Microsoft Systems Management Server 2003 Primary Site") or SMS_R_System.OperatingSystemNameandVersion like "%Server 5.2%" and SMS_R_System.IPSubnets != "10.1.1.0" and SMS_G_System_COMPUTER_SYSTEM.SystemType != "x64-based PC" and SMS_G_System_COMPUTER_SYSTEM.DomainRole < 4 and SMS_R_System.ResourceDomainORWorkgroup = "ADDOMAIN" and SMS_R_System.ResourceId not in (select SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Microsoft Systems Management Server 2003 Primary Site")

Share This Post

Ying Li obtained a Master degree of Medicine in China and worked in the medical field before he came to the states in 1996. He made the career switch from troubleshooting people to troubleshooting computers after he came to the states. Not entirely a surprise there, if you know that his second preference for college is Mathematics. Ying is two times Admin Framework MVP awardee (2007-2008).His previous employment experience in the US includes UUNET, MCI WorldCom. MMC (Marsh & McLennan Companies), Merrill Lynch. He is currently employed by Related Companies as a Senior Infrastructure Engineer. He is proficient in virtually all Microsoft products (Office/Back office). His craving for efficiency naturally leads him to scripting, automation and before you know it, PowerShell! He is currently a MCSE (NT4, 2000, 2003) and MCITP Server Administrator. Ying enjoys wines and love to travel. His non work related passion is to advocate Health Living!

Leave a Reply