SCCM SQL Query for Software not in Add Remove Programs

This SCCM SQL query finds every machine in your environment that does not have a specific piece of software listed in Add/Remove Programs — useful for identifying gaps in a required application rollout.

The Query

Select Distinct
sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
WHERE sys.ResourceID not in
(select sys.ResourceID
from v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
WHERE DisplayName0 = @displayname)

Populating the Prompt

If you’re building this as an SSRS report, use the following as the dataset for the @displayname parameter’s drop-down list, so users can pick from software actually seen in your environment rather than typing the name manually:

select DisplayName0 from v_Add_Remove_Programs

This makes the report self-service — anyone with access can select any application from the drop-down and immediately get a list of machines missing it, without needing to know the exact DisplayName string in advance.

A Caveat Worth Adding: Machines That Never Reported the Software At All

This query has one subtlety worth calling out explicitly: it returns machines whose Add/Remove Programs inventory simply doesn’t contain a matching row for the target software — which includes both machines that genuinely don’t have it installed and machines whose hardware inventory hasn’t run recently enough to reflect a recent install. Before treating the results as a definitive “needs deployment” list, it’s worth cross-checking a handful of the returned machines’ last hardware-inventory scan dates (v_R_System.AgentTime or the client’s Hardware Inventory Cycle history) — a machine that installed the software yesterday but hasn’t scanned since will show up as a false positive. This underlying schema (v_R_System, v_Add_Remove_Programs) is stable and unchanged across current Configuration Manager branches, so the query itself needs no modification — the caveat is purely about interpreting freshness of the underlying inventory data.

Resources

• query
• WQL
• our SQL & WQL query library