TOP 5 Queries on SCCM SQL database per category

Check out the most usefull SQL queries for SCCM


Open SQL Server Management Studio, press New Query, paste the desired query, select your SCCM database and hit RUN (F5)


You can export the results by Select All and copy with headers command or by chosing Save As to export as a CSV file

SCCM Client

image17

*


*

Latest SCCM client installation retries with error codes

Check the sccm client installation error codes HERE


SELECT           m.[Name] AS [Computer Name]           ,m.[LatestProcessingAttempt]          ,m.[LastErrorCode]          ,m.[Description]          , AD.AD_Site_Name0 AS [AD Site Name]          ,m.[NumProcessAttempts]  FROM v_R_System AD JOIN v_CP_Machine m         ON AD.Name0 = m.Name WHERE m.Description = 'Retry' order by m.LatestProcessingAttempt desc

SCCM 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’

*


*

*


*

List all devices without an SCCM Client installed


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

Device Status

image18

Count and list devices with the same MAC address

OBS: Very usefull when searching for Virtual Machine servers deployed with the same MAC address


SELECT dbo.v_RA_System_MACAddresses.MAC_Addresses0, Count(dbo.v_R_System.Name0) AS SystemCount  FROM dbo.v_R_System RIGHT OUTER JOIN dbo.v_RA_System_MACAddresses  ON dbo.v_R_System.ResourceID = dbo.v_RA_System_MACAddresses.ResourceID  GROUP BY dbo.v_RA_System_MACAddresses.MAC_Addresses0 ORDER BY SystemCount DESC  SELECT Name0, Hardware_ID0, Count(Hardware_ID0) AS SystemCount FROM dbo.v_R_System GROUP BY Hardware_ID0, Name0 ORDER BY SystemCount DESC

All devices 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())

List devices with same GUID


SELECT * from v_GS_System inner join v_HS_System on v_HS_System.ResourceID = v_GS_System.ResourceID where v_GS_System.Name0 <> v_HS_System.Name0

List all devices that requires a reboot


SELECT SYS.ResourceID,  SYS.ResourceType,  SYS.Name0,  SYS.SMS_Unique_Identifier0,  SYS.Resource_Domain_OR_Workgr0,  SYS.Client0  FROM  v_R_System AS SYS  INNER JOIN  vSMS_CombinedDeviceResources CDR  ON  cDR.MachineID=SYS.ResourceID  WHERE  CDR.CLIENTSTATE <> 0

List all devices where C drive free space is less then 4 GB


SELECT  a.ResourceID,  a.ResourceType AS[ResourceType],  a.Name0 AS[Name],  a.User_Name0 as [User 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)< 4000)

List the last boot up for devices


SELECT    SD.Name0 AS [Machine Name],  SD.User_Name0 AS [Last Logged on User Name],   Convert(VarChar(10), OS.LastBootUpTime0, 101)  AS [Last Boot Date]  From v_R_System SD  Join v_Gs_Operating_System OS on SD.ResourceID = OS.ResourceID  Order By [Last Boot Date] DESC

Blank

image19

---

---

__

__

&&


&&

**


**

--


--

--


--

Device info

image20

List the membership collections for a specific device

Type your device neme in 'your machine name'


SELECT v_FullCollectionMembership.CollectionID As 'Collection ID', v_Collection.Name As 'Collection Name', v_R_System.Name0 As 'Machine Name' from v_FullCollectionMembership  JOIN v_R_System on v_FullCollectionMembership.ResourceID = v_R_System.ResourceID  JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionID  Where v_R_System.Name0='your machine name'

__

__

&&


&&

**


**

--


--

--


--

Software

image21

All installed software for a specific device

Enter the device name under 'your machine name'


SELECT b.Name0,a.DisplayName0,b.User_Name0,  a.Version0 from v_Add_Remove_Programs a join v_R_System b on a.ResourceID=b.ResourceID  where b.Name0 like 'your machine name'

__

__

&&


&&

**


**

--


--

Count all OS from a specific collection


SELECT  SD.Operating_System_Name_and0 NOS,  Count (SD.Operating_System_Name_and0) AS [Total],  COL.CollectionId,  COL.Name  From v_R_System SD  Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID   Join v_Collection COL on FCM.CollectionID = COL.CollectionID  Where COL.COLLECTIONID = 'SMS00001'  Group By SD.Operating_System_Name_and0, COL.CollectionId, COL.Name  Order By SD.Operating_System_Name_and0, COL.CollectionId, COL.Name

Windows Updates

image22

List all WUA versions per collection

Very usefull query when trying to identify old WUA versions. Devices with old WUA versions will not receive regular patches. Identify them and apply the proper fix


SELECT distinct ras.Name0 as ServerName, ras.AD_Site_Name0 as Site_Location, ras.Client_Version0 as ClientVersion, os.caption0 as OperatingSystem, ras.Last_Logon_Timestamp0 as LastLogon, wua.Version0 as WUA from dbo.v_R_System ras inner join v_GS_WINDOWSUPDATEAGENTVERSION as wua on wua.ResourceID = ras.ResourceID inner join dbo.v_FullCollectionMembership as col on col.ResourceID = ras.ResourceID left join v_GS_OPERATING_SYSTEM os on ras.resourceid=os.resourceid inner join v_FullCollectionMembership fcm on ras.resourceid=fcm.resourceid inner join v_collection coll on coll.collectionid=fcm.collectionid  where coll.Name like '%%' order by wua

List all the patches downloaded but not included in any SUG


SELECT ui.title, ui.articleID, ui.bulletinID, case when ui.IsSuperseded = 0 then 'No' else 'Yes' end as [IsSuperseded], case when ui.IsExpired = 0 then 'No' else 'Yes' end as [IsExpired] from dbo.v_UpdateInfo ui inner join dbo.v_UpdateContents uc ON uc.CI_ID=ui.ci_ID where ui.CI_ID not in (Select upd.ci_id from vSMS_CIRelation as cr  INNER Join fn_ListUpdateCIs(1033) upd ON Upd.CI_ID = cr.ToCIID inner join v_UpdateContents CC on cc.CI_ID = upd.CI_ID inner join v_AuthListInfo AL on al.CI_ID=cr.FromCIID where cc.ContentProvisioned='1') and uc.ContentProvisioned='1' and (ui.CIType_ID=1 or ui.CIType_ID=8) group by  ui.Title, ui.ArticleID, ui.BulletinID, ui.IsSuperseded, ui.IsExpired order by 2

&&


&&

**


**

--


--

--


--

SCCM Site Status

image23

Backup & Restore live status

Run this query when running a backup or restore operation


SELECT command ,CAST(total_elapsed_time/1000.0/60.0 AS NUMERIC(8,2)) AS [Elapsed Min]   ,CAST(estimated_completion_time/1000.0/60.0 AS NUMERIC(8,2)) AS [ETA Min]   ,CAST(estimated_completion_time/1000.0/60.0/60.0 AS NUMERIC(8,2)) AS [ETA Hours]  ,CAST(percent_complete AS NUMERIC(8,2)) AS [Percent Complete]  from sys.dm_exec_requests  where session_id = (select max(spid) from master.dbo.sysprocesses with (nolock) where cmd in ('RESTORE DATABASE','BACKUP DATABASE'))

Live check the I/O total and percentage of your database

Run this query on the server where SCCM database is installed


WITH Aggregate_IO_Statistics AS (Select DB_NAME(database_id) as [Database Name], CAST (SUM(num_of_bytes_read + num_of_bytes_written)/1048576 as decimal(12, 2)) as io_in_mb from sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS] GROUP BY database_id) SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb desc) as [i/o Rank], [Database Name], io_in_mb as [Total I/O (MB)],     CAST(io_in_mb/ SUM (IO_In_MB) OVER () * 100.0 as decimal(5,2)) as [I/O Percent] FROM Aggregate_IO_Statistics ORDER BY [I/O Rank] OPTION (recompile);

Live check the SCCM database averange index percentage

Run this query on the server where SCCM database is installed to see the indexation on the SCCM DB. Very ussefull when checking the database health. Well written here


Select DB_NAME(database_id) as [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], i.name AS [Index Name], ps.index_id, index_type_desc, avg_fragmentation_in_percent, fragment_count, page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') AS ps inner join sys.indexes as i with (NOLOCK) ON ps.[object_id] = i.[object_id] and ps.index_id = i.index_id where database_id = DB_ID() AND page_count > 1500 ORDER BY avg_fragmentation_in_percent desc option (RECOMPILE);

**


**

--


--

--


--

Applications & Packages

image24

All available packages in SCCM with installation command line


SELECT  Program.PackageID,  Package.Name  as Package, Program.ProgramName as 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 != '*' ORDER BY  Package.Name

All available applications in SCCM details


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

--

--

--


--

--


--

--


--