This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(307.2, 91%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
I have usecase in which I need to get the softwares which are not deployed by SCCM. I have explored some of the tables/views like v_Add_Remove_Programs and v_GS... but not able to distinguish between the softwares deployed by SCCM and installed by other sources.
How can I achive this by only using the SCCM database tables/views?
Thanks
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(291.2, 38%, 38%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMP%3C/text%3E%3C/svg%3E)
AFAIK, this would require analyzing data from multiple views.
1. Identify installed software
Use the v_Add_Remove_Programs or v_Add_Remove_Programs_64 views to get a list of installed applications:
SELECT DISTINCT MachineID, DisplayName, Version, Publisher
FROM v_Add_Remove_Programs
This provides a list of software installed on each machine.
2. Identify SCCM-managed deployments
Check the v_DeploymentSummary and v_Program tables to find applications deployed via SCCM:
SELECT DISTINCT dp.CollectionID, dp.PackageID, pr.ProgramName, ad.DisplayName
FROM v_DeploymentSummary dp
JOIN v_Advertisement ad ON dp.AdvertisementID = ad.AdvertisementID
JOIN v_Program pr ON dp.PackageID = pr.PackageID
This gives you a list of applications SCCM has deployed.
3. Find software not deployed by SCCM By performing a LEFT JOIN between these datasets, you can identify software installed on machines that were not deployed via SCCM:
SELECT arp.MachineID, arp.DisplayName, arp.Version, arp.Publisher
FROM v_Add_Remove_Programs AS arp
LEFT JOIN v_DeploymentSummary AS ds ON arp.DisplayName = ds.DisplayName
WHERE ds.DisplayName IS NULL
This will return a list of software that is installed but has no matching SCCM deployment record.
Alternatively, you can explore v_GS_SoftwareProduct and v_MeteredSoftware for additional insights into what software is being used and whether it was metered by SCCM.
If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.
hth
Marcin
We can't compare the DisplayName as DisplayName in arp and ds can be slightly different. For Example,
Below is the DisplayName from v_Add_Remove_Programs (arp).
Below is the SoftwareName from v_DeploymentSummary (ds).
Notice the arp DisplayName0 includes the version also which will not match with the SoftwareName of ds and due to that join will not work.
Let's say somehow I find a way which gives me the same name from both but there is one more case as described below.
There can be software lets say software named x is deployed by SCCM on device a and the same software is installed on device b but it is not deployed by SCCM. So, in this also name matching will not work.
Need something unique which segregates properly.
Try to match ProductID (if available), Publisher, and Version. If a software entry from v_Add_Remove_Programs does not have a corresponding entry in SCCM deployments (v_DeploymentSummary), it was likely installed outside of SCCM.
If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.
hth
Marcin
Hi, @Harsh
If the DisplayName from v_Add_Remove_Programs is different from the SoftwareName from v_DeploymentSummary, it's difficult to get the list of software not deployed by SCCM correctly.