SCCM SQL Query - Part1

Here we are giving some SQL queries which can help to get common management required reports for ongoing jobs to be done, also it helps to get quick information on the report which is been looking for. These queries should work straight away by copying and pasting in SQL management studio and give output as mentioned for each query. There could be chances it may not work because changes in either SQL table or SQL Views. These queries also can be used as reference queries to build other queries for desired data reports.

SCCM SQL query Add Remove Programs? Or SCCM query Add Remove Programs display name and version? Or SCCM query for Add Remove Programs? Or SCCM query all software installed on all computers?

This query is going to give detailed information on software installed on client computers from Add Remove Program List. It would show Computer Name, Software Name, Username, Software Publisher, Software Name, Software Version.

 select sys.Netbios_Name0 , sys.User_Name0, arp.publisher0, arp.displayname0, arp.version0
 from v_R_System_Valid sys
 join v_gs_Add_Remove_programs arp on arp.ResourceID=sys.ResourceID

SCCM SQL query for specific software installed?

This query is going to give detailed information on specific software’s installed on client computers from Add Remove Program List. It would show Computer Name, Software Name, Username, Software Publisher, Software Name, Software Version.

 select sys.Netbios_Name0 , sys.User_Name0, arp.publisher0, arp.displayname0, arp.version0
 from v_R_System_Valid sys
 join v_gs_Add_Remove_programs arp on arp.ResourceID=sys.ResourceID
 where (ARP.DisplayName0 like '%(App Name A)%' and ARP.DisplayName0 like '% App Name B%')

SCCM SQL query software not installed?

This query is going to give detailed information on specific software not installed on client computers from Add Remove Program List. It would show Computer Name, Username, Software Publisher, Software Name, Software Version.

 select sys.Netbios_Name0 , sys.User_Name0, arp.publisher0, arp.displayname0, arp.version0
 from v_R_System_Valid sys
 join v_gs_Add_Remove_programs arp on arp.ResourceID=sys.ResourceID
 where (ARP.DisplayName0 Not like '%(App Name A)%' and ARP.DisplayName0 Not like '% App Name B%')

SQL query to get serial number SCCM?

This query is going to get the serial number of computers with column Computer Name, Username, Serial Number.

 select sys.Netbios_Name0 , sys.User_Name0, bios.SerialNumber0
 from v_R_System_Valid sys
 join v_GS_PC_BIOS bios on bios.ResourceID=sys.ResourceID

SCCM SQL query to find OS version?

This query is going to get the OS version of computers with column Computer Name, OS Name, OS Version, OS Install Date.

 select sys.Netbios_Name0, os.Caption0 as OS_Name, os.Version0 as OS_Version ,os.InstallDate0
 from v_GS_OPERATING_SYSTEM os
 join v_R_System sys on sys.ResourceID=os.ResourceID
 join v_R_System_Valid sysv on sysv.ResourceID=sys.ResourceID

SCCM SQL query to find Chassis Type?

This query is going to get the Chassis Type of computers with column Computer Name, Chassis Type.

 select distinct sys.Netbios_Name0, CASE en.ChassisTypes0 WHEN 8  THEN'Laptop' WHEN 9  THEN'Laptop'  
 WHEN 10 THEN'Laptop' WhEN 11 THEN'Laptop' WHEN 12 THEN 'Laptop' WHEN 14 THEN 'Laptop' WHEN 
 18 THEN 'Laptop' WHEN 21 THEN 'Laptop' WHEN 3 THEN 'Desktop' WHEN 4 THEN 'Desktop' WHEN 5 
 THEN 'Desktop' WHEN 6 THEN 'Desktop' WHEN 7 THEN 'Desktop' WHEN 15 THEN 'Desktop' end as  
 'Types' 
 from v_R_System_Valid as sys
 join v_GS_SYSTEM_ENCLOSURE as en on en.ResourceID=sys.ResourceID

SCCM SQL query collection members?

This query is going to get all members from specified collection ID with column Collection Name, Resource ID.

 select fscol.name, fscol.resourceid
 from v_cm_res_coll_(Collection ID) fscol

Note: Replace “(Collection ID)” with collection ID

SCCM SQL query installed patches?

This query is going to get MS Update status with column Computer Name, Deployment Name, Collection ID, Assignment Name, Collection Name, Reboot Status, Username.

 select distinct sys.netbios_name0, cl.name, cm.collectionid, cs.AssignmentName, cs.CollectionName,  
 cst.LastEnforcementIsRebootSupressed, cst.UserID
 From v_r_system_valid sys
 join v_fullcollectionmembership cm on cm.resourceid=sys.resourceid
 join v_collection cl on cl.collectionid=cm.collectionid
 join v_CIAssignment cs on cs.CollectionID=cm.CollectionID
 join v_CIAssignmentStatus cst on cst.ResourceID=sys.ResourceID
 where cs.AssignmentName like '%Microsoft_Updates_%'

SCCM SQL query for installed updates? And SCCM SQL query to find KB installed?

Here is another query to get the software installed details with column Computer Name, Bulletin ID, Article ID, Title Name, Patch Month, Status, Assignment Name.

 SELECT distinct SYS.Netbios_name0 , UI.BulletinID, UI.ArticleID, UI.Title,datename(year, UI.datecreated)+'-'+ datename (MONTH, UI.datecreated) as 'PatchMonth'  ,CASE when UCS.Status=2 then 'Required' WHEN UCS.Status=3 then 'Installed' when UCS.Status=0 then 'Unknown'  end as 'Status' ,UDS.AssignmentName
 FROM v_R_System_valid AS SYS
 LEFT OUTER JOIN v_Update_ComplianceStatusAll UCS ON SYS.ResourceID = UCS.ResourceID 
 Left outer JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
 left outer join v_FullCollectionMembership CM on cm.ResourceID=sys.ResourceID 
 left outer join v_UpdateDeploymentSummary UDS on uds.CI_ID=ui.CI_ID
 left outer join v_Collection cl on cl.CollectionID=cm.CollectionID

SCCM SQL query to find active clients

This query help to get information of client is active or Obsolete with column name Name, Obsolete, Active, Domain

 select name , IsObsolete, IsActive, Domain 
 from v_CM_RES_COLL_(Collection ID)

Note: Replace “(Collection ID)” with collection ID

SCCM query all systems with specific software product name?

This query is going to give detailed information on particular software’s installed on client computers from Add Remove Program List. It would show Computer Name, Software Name, Username, Software Publisher, Software Name, Software Version.

 select sys.Netbios_Name0 , sys.User_Name0, arp.publisher0, arp.displayname0, arp.version0
 from v_R_System_Valid sys
 join v_gs_Add_Remove_programs arp on arp.ResourceID=sys.ResourceID
 where arp.displayname0 like ‘%Inte%’

Note: Replace “Inte” with wild characters of the product name looking for.

Find here the link for the SQL table and views.

Please share your inputs or if any help required in comment box.

SCCMinfo

SCCM SQL Query – Part1