SCCM – SQL Queries

Anda dapat mencoba queri SQL Anda di SQL Server Management Studio atau WQL di bawah node CM Queries.

Create SQL Query in SQL Server Management Studio

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

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

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:

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

All SCCM Clients Matching Version

Build 1702

Build 1610

Build 1606

Build 1511

Collection with all computers without a ConfigMgr client

Return machine name, serial, and AD container

Return computers in a specific domain

Collection for all Workstations

Collection of all Windows 10 clients

Collection of all Windows 8.1 clients

Collection of all Windows 8 clients

Collection of all Windows 7 clients

Use this query to create collections for your various computer models

Collection of computers ending with odd numbers

Collection of computers ending with even numbers

Collection of computers ending with LAB

Collection of computers beginning with LAB

Collection of computers without failing hard drive

Collection of computers with failing hard drive

Collection for all Servers

Collection of all Windows 2016 Servers

Collection of all Windows 2012 R2 Servers

Collection of all Windows 2012 Servers

Collection of all Windows 2008 R2 Servers

Collection of all Windows 2008 Servers

Collection of all Domain Controllers

Query based collection for all Microsoft Exchange Servers

SQL Server Collections

2016

2014

2012

2008

Query based collection based on IP subnet

Query based collection based on IP range

All Dell Systems

All Hewlett-Packard Systems

All Lenovo Systems

All Physical Systems

Pull Distribution Points with Source Distribution Points SQL Query

Report to list of all users laptops

All application list

All OS with Versions

Software Updates Installed using SCCM or Manually

sumber

Happy

0 0 %

Sad

0 0 %

Excited

0 0 %

Sleepy

0 0 %

Angry

0 0 %

Surprise

0 0 %