SQL Queries for SCCM

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:

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

Collection of computers beginning with 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

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

2012

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

System Center

Hacks | Guides | Queries | Curated Articles – under one roof

SQL Queries for SCCM

Leave a Reply Cancel reply

Share this:

Like this:

Related

Leave a Reply Cancel reply