SCCM Collection Querys für Filter

Eine kleine nützliche Sammlung an SCCM Collection Querys für das Anlegen von Collections für Updates, Management, Antimalware Rules und andere Filter für den Microsoft SCCM Server.

earvine95 / Pixabay

Übersicht möglicher SCCM Collection Querys

Alle Windows 7 32 und 64Bit Clients

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.OperatingSystemNameandVersion like “%Workstation 6.1%”

Alle Windows 8 32 und 64Bit Clients

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.OperatingSystemNameandVersion like “%Workstation 6.2%”

Alle Microsoft Exchange Server

select
* from  SMS_R_System inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceId =
SMS_R_System.ResourceId where SMS_G_System_SERVICE.Name like “Microsoft Exchange %”

Alle Active Directory Domain Controller

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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Roles like “%Domain_Controller%”

Alle Microsoft SQL Server

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “Microsoft SQL Server 20%” or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like “Microsoft SQL Server 20%”