SCCM – SQL Queries

Also see SQL Certification

You can test your SQL queries in the SQL Server Management Studio or WQL queries under the CM Queries node.

 

Create SQL Query in SQL Server Management Studio

 

Create WQL Query under SCCM Monitoring Workspace

 

 

WQL Queries

* tested in the SCCM Console, under the Queries node

* many other queries in PDF

Return Specific Windows Build Version

SELECT DISTINCT
SMS_R_System.NetbiosName, SMS_G_System_OPERATING_SYSTEM.BuildNumber
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.BuildNumber = "16299" and SMS_R_System.OperatingSystemNameandVersion like "%Workstation%"

 

Join System, User, and Computer_System…return data

SELECT v_R_System.Name0,v_R_System.Operating_System_Name_and0, v_R_User.User_Name0,v_R_User.displayName0,v_R_User.Mail0, v_GS_COMPUTER_SYSTEM.Model0
FROM v_R_System
INNER JOIN v_R_User ON v_R_User.User_Name0 = v_R_System.User_Name0
INNER JOIN v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId

 

Return Machines with PST Data

select distinct
SMS_R_System.Name,
SMS_G_System_SoftwareFile.FileName,
SMS_G_System_SoftwareFile.FilePath,
SMS_G_System_SoftwareFile.FileSize,
SMS_G_System_SoftwareFile.ModifiedDate
from
SMS_R_System
inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
where
SMS_G_System_SoftwareFile.FileName like "%.pst%"

 

 Join SMS_R_System and SMS_R_User: Post

SELECT * FROM SMS_R_System INNER JOIN SMS_R_User ON SMS_R_User.Username = SMS_R_System.LastLogonUserName

 

SQL Queries

* tested in SQL Server Management Studio

 

Return Maintenance Windows General Info

SELECT c.Name, c.Comment,SW.IsEnabled, SW.CollectionID, c.MemberCount, SW.Description, SW.StartTime, SW.Duration
FROM v_ServiceWindow SW
JOIN v_Collection C ON C.CollectionID = SW.CollectionID
ORDER BY c.Name

 

Return ResourceID, Email Address, Computer Name

SELECT v_R_User.ResourceID, User_Principal_Name0, v_GS_COMPUTER_SYSTEM.Name0
FROM v_GS_COMPUTER_SYSTEM
LEFT JOIN v_R_User ON v_GS_COMPUTER_SYSTEM.UserName0 = v_R_User.Unique_User_Name0

 

Return Collection ID from Software Name

select distinct
v_Collection.Name as 'Software Name',
v_Collection.CollectionID
from v_Collection
join vSMS_CollectionDependencies on vSMS_CollectionDependencies.DependentCollectionID = v_Collection.CollectionID
where v_Collection.Name = 'Visio Pro 2019'

 

Return Collection Name from Collection ID

select distinct
v_Collection.Name as 'Software Name',
v_Collection.CollectionID
from v_Collection
join vSMS_CollectionDependencies on vSMS_CollectionDependencies.DependentCollectionID = v_Collection.CollectionID
where v_Collection.CollectionID = 'ABC00188'

 

Return SCCM Folders with IDs

select vsms_folders.Name as[FolderName], vFolderMembers.ContainerNodeID as [FolderID]
from vcollections
inner join vFolderMembers
on vCollections.siteid = vFolderMembers.InstanceKey
inner join vsms_folders
on vFolderMembers.ContainerNodeID = vSMS_Folders.ContainerNodeID

 

Return All Collections Inside a Specific Folder

select vcollections.siteid as [Software Title], vcollections.collectionname as [CollectionName], vsms_folders.Name as[FolderName], vFolderMembers.ContainerNodeID as [FolderID]
from vcollections
inner join vFolderMembers
on vCollections.siteid = vFolderMembers.InstanceKey
inner join vsms_folders
on vFolderMembers.ContainerNodeID = vSMS_Folders.ContainerNodeID
where vFolderMembers.ContainerNodeID = '12345678'
ORDER BY
'Software Title'

 

Return Computer and User

SELECT sys.Netbios_Name0, TopConsoleUser0
FROM v_R_System sys
LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP um
ON um.ResourceID = sys.ResourceID
WHERE TopConsoleUser0 IS NOT NULL
ORDER BY sys.Netbios_Name0

 

Return All Available Packages

SELECT
Program.PackageID,
Package.Name 'Package',
Program.ProgramName 'Type',
Program.CommandLine,
Program.Comment,
Program.Description,
Package.PkgSourcePath
FROM
[v_Program] as Program
LEFT JOIN
v_Package as Package on Package.PackageID = Program.PackageID
WHERE Program.ProgramName != '*'
--WHERE Program.ProgramName = 'Install'
ORDER BY
Package.Name

 

Create Custom Report with operating systems and serials

SELECT DISTINCT
a.ResourceID,
a.Name0 AS[Name],
a.User_Name0 AS[Username],
b.System_OU_Name0,
a.Last_Logon_Timestamp0 AS[Last Logon],
c.Manufacturer00 AS [Manufacturer],
c.SerialNumber00 AS[Serial],
c.SMBIOSAssetTag00 AS[Asset],
d.Caption00 AS[OS],
d.InstallDate00 AS[Install Date],
e.SMBIOSBIOSVersion00 AS[BIOS],
f.Model00 AS[Model]

FROM
v_R_System a

LEFT OUTER JOIN
System_System_OU_Name_ARR b ON b.ItemKey = b.ItemKey
INNER JOIN
System_Enclosure_DATA c ON c.MachineID = a.ResourceID
INNER JOIN
Operating_System_DATA d ON d.MachineID = a.ResourceID
INNER JOIN
PC_BIOS_DATA e ON e.MachineID = a.ResourceID
INNER JOIN
Computer_System_DATA f ON f.MachineID = a.ResourceID

 

Computers that have 30+ days old hardware information

SELECT
a.ResourceID,
a.Netbios_name0 AS[Name],
b.LastHWScan
FROM
v_R_System a
INNER JOIN
v_GS_WORKSTATION_STATUS b
ON
a.ResourceID = b.ResourceID
WHERE
b.LastHWScan <= Dateadd(day, -30, getdate())

 

Computers that have 30 days or less hardware information

SELECT
a.ResourceID,
a.Netbios_name0 AS[Name],
b.LastHWScan
FROM
v_R_System a
INNER JOIN
v_GS_WORKSTATION_STATUS b
ON
a.ResourceID = b.ResourceID
WHERE
b.LastHWScan >= Dateadd(day, -30, getdate())

 

Collection of clients not approved

SELECT
a.ResourceID,
a.Netbios_name0 AS[Name],
a.ResourceType,
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.IsApproved
FROM
v_R_System a
INNER JOIN
v_CM_RES_COLL_SMS00001 b
ON
a.ResourceID = b.ResourceID
WHERE
b.IsApproved = '2'

 

Collection of clients that have been approved

SELECT
a.ResourceID,
a.Netbios_name0 AS[Name],
a.ResourceType,
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.IsApproved
FROM
v_R_System a
INNER JOIN
v_CM_RES_COLL_SMS00001 b
ON
a.ResourceID = b.ResourceID
WHERE
b.IsApproved = '1'

 

Collection of clients requiring a reboot

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System AS SMS_R_SYSTEM
INNER JOIN
vSMS_Update_ComplianceStatus AS c
ON
c.MachineID=SMS_R_SYSTEM.ResourceID
WHERE
c.LastEnforcementMessageID = 9

 

Collection of ConfigMgr clients waiting for another installation to finish

SELECT
SMS_R_SYSTEM.ResourceID AS[ResourceID],
SMS_R_SYSTEM.ResourceType AS[Type],
SMS_R_SYSTEM.Name0 AS[Name],
SMS_R_SYSTEM.SMS_Unique_Identifier0 AS[UniqueID],
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0 AS[Domain],
SMS_R_SYSTEM.Client0 AS[Name]
FROM
v_R_System AS SMS_R_System
INNER JOIN
vSMS_Update_ComplianceStatus AS c
ON
c.MachineID=SMS_R_System.ResourceID
WHERE
c.LastEnforcementMessageID = 3

 

Return list of computers with C: drive space great than 100 MB

SELECT
a.ResourceID,
a.ResourceType AS[ResourceID],
a.Name0 AS[Name],
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.DeviceID0 AS[DriveLetter],
b.Size0 AS[Size]
FROM
v_R_System a
INNER JOIN
v_GS_LOGICAL_DISK b
ON
a.ResourceID = b.ResourceId
WHERE
b.DeviceID0 = 'C:' AND ((b.Size0)> 100)

 

All SCCM client computers with less than 1 GB free disk space on C:

SELECT
a.ResourceID,
a.ResourceType AS[ResourceType],
a.Name0 AS[Name],
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.DeviceID0 AS[DriveLetter],
b.FreeSpace0 AS[FreeSpace],
b.Size0 AS[Size]
FROM
v_R_System a
INNER JOIN
v_GS_LOGICAL_DISK b
ON
a.ResourceID = b.ResourceId
WHERE
b.DeviceID0 = 'C:' AND ((b.FreeSpace0) > 1000)

 

All SCCM client computers with less than 10 GB free disk space on C:

SELECT
a.ResourceID,
a.ResourceType AS[ResourceType],
a.Name0 AS[Name],
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.DeviceID0 AS[DriveLetter],
b.FreeSpace0 AS[FreeSpace],
b.Size0 AS[Size]
FROM
v_R_System a
INNER JOIN
v_GS_LOGICAL_DISK b
ON
a.ResourceID = b.ResourceId
WHERE
b.DeviceID0 = 'C:' AND ((b.FreeSpace0)< 10000)

 

All SCCM client computers with greater than 50 GB free disk space on C:

SELECT
a.ResourceID,
a.ResourceType AS[ResourceID],
a.Name0 AS[Name],
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.DeviceID0 AS[DriveLetter],
b.FreeSpace0 AS[FreeSpace],
b.Size0 AS[Size]
FROM
v_R_System a
INNER JOIN
v_GS_LOGICAL_DISK b
ON
a.ResourceID = b.ResourceId
WHERE
b.DeviceID0 = 'C:' AND ((b.FreeSpace0)> 50000)

 

All computer objects with client installed in the specific site “001”

SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
b.SMS_Installed_Sites0 AS[SiteCode]
FROM
v_R_System a
LEFT OUTER JOIN
v_RA_System_SMSInstalledSites b
ON
a.ResourceID = b.ResourceID
WHERE
b.SMS_Installed_Sites0 = '001' AND a.Client0 = '1'

 

All SCCM Clients Matching Version

Build 1702

SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
a.SMS_Unique_Identifier0,
a.Client0,
a.Client_Version0
FROM
v_R_System a
WHERE
a.Client_Version0 like '5.00.8498.1007' --OR a.Client_Version0 like '5.00.8498.1008'

Build 1610

SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
a.SMS_Unique_Identifier0,
a.Client0,
a.Client_Version0
FROM
v_R_System a
WHERE
a.Client_Version0 like '5.00.8458.1005' --OR a.Client_Version0 like '5.00.8458.1005'

Build 1606

SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
a.SMS_Unique_Identifier0,
a.Client0,
a.Client_Version0
FROM
v_R_System a
WHERE
a.Client_Version0 like '5.00.8412.1006' --OR a.Client_Version0 like '5.00.8412.1006'

Build 1511

SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
a.SMS_Unique_Identifier0,
a.Client0,
a.Client_Version0
FROM
v_R_System a
WHERE
a.Client_Version0 like '5.0.8325.1000' --OR a.Client_Version0 like '5.0.8325.1000'

 

Collection with all computers without a ConfigMgr client

SELECT
a.ResourceID,
a.ResourceType,
a.Name0,
a.SMS_Unique_Identifier0,
a.Resource_Domain_OR_Workgr0,
a.Client0
FROM
V_R_System as a
WHERE
a.Client0 is null

 

Return machine name, serial, and AD container

SELECT
a.Name0 AS[Name],
b.SerialNumber0 AS[Serial],
c.System_Container_Name0
FROM
v_R_System a
LEFT JOIN
v_GS_PC_BIOS b on b.ResourceID = a.ResourceID
INNER JOIN
System_System_Container_Name_A c on c.ItemKey = a.ResourceID
WHERE b.SerialNumber0 IS NOT NULL

 

Return computers in a specific domain

SELECT
a.Name0
FROM
v_R_System a
WHERE
a.Resource_Domain_OR_Workgr0 = 'AWESOME'

 

Collection for all Workstations

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%workstation%'

 

Collection of all Windows 10 clients

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%workstation% 10.0'

 

Collection of all Windows 8.1 clients

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%workstation% 6.3'

 

Collection of all Windows 8 clients

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%workstation% 6.2'

 

Collection of all Windows 7 clients

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%workstation% 6.1'

 

Use this query to create collections for your various computer models

SELECT DISTINCT
a.Name0,
b.Manufacturer0,
b.Model0
FROM
v_R_System a
INNER JOIN
v_GS_COMPUTER_SYSTEM b
ON
b.ResourceID = a.ResourceId
WHERE
b.Manufacturer0 like '%Hewlett-Packard%' or b.Manufacturer0 like '%VMWare, Inc.%'

 

Collection of computers ending with odd numbers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Name0 like '%1'
or SMS_R_System.Name0 like '%3'
or SMS_R_System.Name0 like '%5'
or SMS_R_System.Name0 like '%7'
or SMS_R_System.Name0 like '%9'

 

Collection of computers ending with even numbers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Name0 like '%0'
or SMS_R_System.Name0 like '%2'
or SMS_R_System.Name0 like '%4'
or SMS_R_System.Name0 like '%6'
or SMS_R_System.Name0 like '%8'

 

Collection of computers ending with LAB

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Name0 like '%LAB'

 

Collection of computers beginning with LAB

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Name0 like 'LAB%'

 

Collection of computers without failing hard drive

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0,
V_GS_DISK.Status0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_DISK
ON
V_GS_DISK.ResourceId = SMS_R_System.ResourceId
WHERE
V_GS_DISK.Status0 != 'Pred Fail'
ORDER BY
SMS_R_System.Name0 DESC

 

Collection of computers with failing hard drive

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0,
V_GS_DISK.Status0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_DISK
ON
V_GS_DISK.ResourceId = SMS_R_System.ResourceId
WHERE
V_GS_DISK.Status0 = 'Pred Fail'
ORDER BY
SMS_R_System.Name0 DESC

 

Collection for all Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%server%'

 

Collection of all Windows 2016 Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%server% 10.0'

 

Collection of all Windows 2012 R2 Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%server% 6.3'

 

Collection of all Windows 2012 Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%server% 6.2'

 

Collection of all Windows 2008 R2 Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%server% 6.1'

 

Collection of all Windows 2008 Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%server% 6.0'

Collection of all Domain Controllers

SELECT
*
FROM
v_R_System SMS_R_System
INNER JOIN
v_GS_COMPUTER_SYSTEM
ON
v_GS_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
WHERE
v_GS_COMPUTER_SYSTEM.Roles0 like '%Domain_Controller%'

 

Query based collection for all Microsoft Exchange Servers

SELECT
*
FROM
v_R_System
INNER JOIN
v_GS_SERVICE
ON
v_GS_SERVICE.ResourceId = v_R_System.ResourceId
WHERE
v_GS_SERVICE.Name0 like '%Microsoft Exchange %'

 

SQL Server Collections

2016

SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.Name0,
b.DisplayName0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_ADD_REMOVE_PROGRAMS b ON b.ResourceID = SMS_R_System.ResourceID
WHERE
b.DisplayName0 like '%Microsoft SQL Server 2016%'

2014

SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.Name0,
b.DisplayName0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_ADD_REMOVE_PROGRAMS b ON b.ResourceID = SMS_R_System.ResourceID
WHERE
b.DisplayName0 like '%Microsoft SQL Server 2014%'

2012

SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.Name0,
b.DisplayName0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_ADD_REMOVE_PROGRAMS b ON b.ResourceID = SMS_R_System.ResourceID
WHERE
b.DisplayName0 like '%Microsoft SQL Server 2012%'

2008

SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.Name0,
b.DisplayName0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_ADD_REMOVE_PROGRAMS b ON b.ResourceID = SMS_R_System.ResourceID
WHERE
b.DisplayName0 like '%Microsoft SQL Server 2008%'

 

Query based collection based on IP subnet

SELECT
a.ResourceID,
b.Name0 AS[Name],
a.IP_Subnets0 AS[Subnet]
FROM
v_RA_System_IPSubnets a
INNER JOIN
v_R_System b ON b.ResourceID = a.ResourceID
WHERE
a.IP_Subnets0 LIKE '10.0.0.0'

 

Query based collection based on IP range

SELECT
a.ResourceID,
b.Name0 AS[Name],
a.IP_Addresses0 AS[IP]
FROM
v_RA_System_IPAddresses a
INNER JOIN
v_R_System b ON b.ResourceID = a.ResourceID
WHERE
a.IP_Addresses0 LIKE '10.1.0.1[1-9]'

All Dell Systems

SELECT
*
FROM
v_R_System a
INNER JOIN
v_GS_COMPUTER_SYSTEM b
ON
b.ResourceID = a.ResourceId
WHERE
b.Manufacturer0 like '%Dell%'

 

All Hewlett-Packard Systems

SELECT
*
FROM
v_R_System a
INNER JOIN
v_GS_COMPUTER_SYSTEM b
ON
b.ResourceID = a.ResourceId
WHERE
b.Manufacturer0 like '%HP%' or b.Manufacturer0 like '%Hewlett-Packard%'

 

All Lenovo Systems

SELECT
*
FROM
v_R_System a
INNER JOIN
v_GS_COMPUTER_SYSTEM b
ON
b.ResourceID = a.ResourceId
WHERE
b.Manufacturer0 like '%Lenovo%'

 

All Physical Systems

SELECT
a.ResourceID,
a.ResourceType,
a.Name0,
a.SMS_Unique_Identifier0,
a.Resource_Domain_OR_Workgr0,
a.Client0
FROM
v_R_System a
INNER JOIN
v_GS_SYSTEM_ENCLOSURE b
ON
b.ResourceID = a.ResourceId
WHERE
b.ChassisTypes0 = '23' or b.ChassisTypes0 = '17'

 

Pull Distribution Points with Source Distribution Points SQL Query

SELECT DISTINCT
dbo.v_DistributionPoints.ServerName AS [DP for Pull],
dbo.v_DistributionPoints.IsPeerDP,
dbo.v_DistributionPoints.IsPullDP,
dbo.vPullDPFullMap.PullDPNALPath AS [DP List],
dbo.v_DistributionPoints.IsPXE,
dbo.v_DistributionPoints.Description
FROM
dbo.vPullDPFullMap
INNER JOIN
dbo.v_DistributionPoints
ON dbo.vPullDPFullMap.SourceDPNALPath = dbo.v_DistributionPoints.NALPath

 

Report to list of all users laptops

SELECT DISTINCT
dbo.v_R_System.Name0 AS [Computer Name],
dbo.v_R_System.User_Name0 AS [User Name], dbo.v_R_System.User_Domain0 AS [Domain Name],
dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number], dbo.v_GS_SYSTEM.SystemRole0 AS [System OS Type],
dbo.v_GS_SYSTEM.SystemType0 AS [System Type]
FROM
dbo.v_GS_SYSTEM_ENCLOSURE
INNER JOIN
dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID
INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM
ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '8') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '9') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '10') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '11') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '12') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '14') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '18') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '21')

 

All application list

SELECT
*
FROM fn_ListLatestApplicationCIs(1033)

SELECT
DateCreated,
DateLastModified,
DisplayName,
Manufacturer,
SoftwareVersion,
CreatedBy,
LastModifiedBy
FROM
fn_ListLatestApplicationCIs(1033)
GROUP BY
DateCreated,
DateLastModified,
DisplayName,
Manufacturer,
SoftwareVersion,
CreatedBy,
LastModifiedBy
ORDER BY
DateCreated DESC

 

All OS with Versions

SELECT DISTINCT
dbo.v_R_System.Netbios_Name0 as [Machine Name],
dbo.v_R_System.User_Name0 as [User Name],
dbo.v_R_System.AD_Site_Name0 as [AD Site],
dbo.v_R_System.User_Domain0 as [Domain],
dbo.v_GS_OPERATING_SYSTEM.Caption0 as [OS Name],
dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 as [SP Name],
dbo.v_R_System.Operating_System_Name_and0 as [OS NT Version],
dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 as [Build Number]
FROM
dbo.v_R_System
INNER JOIN
dbo.v_GS_OPERATING_SYSTEM
ON
dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID

 

Software Updates Installed using SCCM or Manually

SELECT sys.Name0,ui.BulletinID, ui.ArticleID,ui.Title,
CASE
WHEN (ucs.Status=2 and ui.IsDeployed=0 ) then 'Required_General'
WHEN (ucs.Status=2 and ui.IsDeployed=1 ) then 'Required_ITICSDeploy'
WHEN (UCS.Status=3 and ui.IsDeployed=1 ) then 'Installed_SCCM'
WHEN (UCS.Status=3 and ui.IsDeployed=0 ) then 'Installed_Manual'
WHEN UCS.Status=0 then 'Unknown' end as 'Status',
CASE
WHEN ui.severity=10 THEN 'Critical'
WHEN ui.severity=8 THEN 'Important'
WHEN ui.severity=6 THEN 'Moderate'
WHEN ui.severity=2 THEN 'Low' WHEN ui.severity=0 THEN 'AddOn' end as 'Severity'
FROM
v_R_System sys
INNER JOIN
v_UpdateComplianceStatus UCS ON   sys.ResourceID = ucs.ResourceID
INNER JOIN
v_UpdateInfo UI ON   UCS.CI_ID = UI.CI_ID
WHERE
-UI.IsDeployed=1 and sys.Netbios_Name0='computer1'
ORDER BY Status

 

Notes

PowerShell – Add Schema, Add Table into a SQL Test Database
PowerShell – Create SQL Database
PowerShell – Add Entries into SQL Database

 

For my reference

Packages in SCCM DB; changing a program name

dbo.DeploymentSummary
dbo.SMSPackages_G
dbo.PkgPrograms_G
dbo.PkgPrograms_L