All Windows 2000 Systems
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 OperatingSystemNameandVersion like
'%Workstation 5.0%'
All Windows XP Systems
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 OperatingSystemNameandVersion like
'%Workstation 5.1%'
All Windows XP 64 Bit Systems
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 OperatingSystemNameandVersion like
'%Workstation 5.2%'
All Windows Vista Systems
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 OperatingSystemNameandVersion like
'%Workstation 6.0%'
All Windows 7 Systems
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
"Microsoft Windows
NT Workstation 6.1"
All Windows 2000 Server Systems
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 OperatingSystemNameandVersion like '%Server
5.0%'
All Windows 2003 Server Systems
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 OperatingSystemNameandVersion like '%Server
5.2%'
All Windows 2008 Server Systems
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 OperatingSystemNameandVersion like '%Server 6%'
All Windows 2008 Server R2 Systems
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 OperatingSystemNameandVersion like '%Server
6.1%'
SQL Server 2005 Installed
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_INSTALLED_SOFTWARE on
SMS_G_System_INSTALLED_SOFTWARE.ResourceID
= SMS_R_System.ResourceId where
SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%SQL Server 2005%"
SQL Server 2008 Installed
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_INSTALLED_SOFTWARE on
SMS_G_System_INSTALLED_SOFTWARE.ResourceId
= SMS_R_System.ResourceId where
SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%SQL Server 2008%"
All Windows XP SP 3 Systems (There are 2 alternatives)
select distinct SMS_R_System.Name,
SMS_G_System_OPERATING_SYSTEM.Caption,
SMS_G_System_OPERATING_SYSTEM.CSDVersion from SMS_R_System inner join
SMS_G_System_OPERATING_SYSTEM on
SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where
SMS_G_System_OPERATING_SYSTEM.Caption like "Microsoft Windows XP%" and
SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 3"
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 where SMS_G_System_OPERATING_SYSTEM.Caption
like "%Windows XP%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion =
"Service Pack 3"
All Windows XP SP 2 Systems (There are 2 alternatives)
select distinct SMS_R_System.Name,
SMS_G_System_OPERATING_SYSTEM.Caption,
SMS_G_System_OPERATING_SYSTEM.CSDVersion from SMS_R_System inner join
SMS_G_System_OPERATING_SYSTEM on
SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where
SMS_G_System_OPERATING_SYSTEM.Caption like "Microsoft Windows XP%" and
SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 2"
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 where SMS_G_System_OPERATING_SYSTEM.Caption
like "%Windows XP%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion =
"Service Pack 2"
All Windows XP SP 1 Systems (There are 2 alternatives)
select distinct SMS_R_System.Name,
SMS_G_System_OPERATING_SYSTEM.Caption,
SMS_G_System_OPERATING_SYSTEM.CSDVersion from SMS_R_System inner join
SMS_G_System_OPERATING_SYSTEM on
SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where
SMS_G_System_OPERATING_SYSTEM.Caption like "Microsoft Windows XP%" and
SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 1"
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 where SMS_G_System_OPERATING_SYSTEM.Caption
like "%Windows XP%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion =
"Service Pack 1"
X bir software' ün kurulu olmadığı sistemleri listeler (Örnek olarak
Adobe Reader 8 girilmiş durumda, değiştirilerek kullanılabilir)
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.Name not
in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System
inner join SMS_G_System_COMPUTER_SYSTEM on
SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner
join SMS_G_System_ADD_REMOVE_PROGRAMS on
SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Adobe Reader
8%")
All Windows Server 2003 SP 1 Systems
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 where SMS_G_System_OPERATING_SYSTEM.Caption
like "%Server 2003%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion =
"Service Pack 1"
All Windows Server 2003 SP 2 Systems
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 where SMS_G_System_OPERATING_SYSTEM.Caption
like "%Server 2003%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion =
"Service Pack 2"
All Windows Server 2008 SP 1 Systems
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 where SMS_G_System_OPERATING_SYSTEM.Caption
like "%Server 2008%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion =
"Service Pack 1"
All Office 2007 Installed Systems
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 where
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office
Professional Plus 2007" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName =
"Microsoft Office Enterprise 2007" or
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName =
"Microsoft Office Standard 2007"
All Windows Server 2000 SP1 Systems
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 where
SMS_R_System.OperatingSystemNameandVersion like "%Server 5.0%" and
SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 1"
All Windows Server 2000 SP2 Systems
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 where
SMS_R_System.OperatingSystemNameandVersion like "%Server 5.0%" and
SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 2"
All Windows Server 2000 SP3 Systems
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 where
SMS_R_System.OperatingSystemNameandVersion like "%Server 5.0%" and
SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 3"
All Windows Server 2000 SP4 Systems
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 where
SMS_R_System.OperatingSystemNameandVersion like "%Server 5.0%" and
SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 4"
Источник: http://gallery.technet.microsoft.com/SCCM-queries-We-can-use-d6aa4e09#content