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.