SCCM Add Remove Programs: Understanding the Database Tables

The SCCM database exposes installed-software data through five different views, and picking the wrong one is a common source of confusing (or duplicated) results in custom reports and queries.

The Five Views

• v_Add_Remove_Programs — combined 32-bit and 64-bit program data (current/active).
• v_GS_Add_Remove_Programs — 32-bit program data only.
• v_GS_Add_Remove_Programs_64 — 64-bit program data only.
• v_HS_Add_Remove_Programs — historical 32-bit program data (previously installed, now removed).
• v_HS_Add_Remove_Programs_64 — historical 64-bit program data.

For most day-to-day reporting — “what’s currently installed across the estate” — v_Add_Remove_Programs is what you want, since it already combines both architectures. Reach for the architecture-specific views only when you need to isolate 32-bit-only or 64-bit-only results, and the v_HS_ historical views only when you’re specifically investigating software that used to be installed on a machine but has since been removed.

Why Duplicated Results Happen

The most common mistake is joining against both the combined view (v_Add_Remove_Programs) and one of the architecture-specific views (v_GS_Add_Remove_Programs or _64) in the same query — since the combined view already contains everything from both architecture-specific views, doing this returns each installed program twice. Pick one source view per query and stick to it; if you need to distinguish which architecture a program was installed as while still working from the combined view, use the Add_Remove_Programs.Is64Bit0 flag on v_Add_Remove_Programs instead of joining a second view.

A Sample Query Using the Combined View

select SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName0, SMS_G_System_ADD_REMOVE_PROGRAMS.Version0, SMS_R_System.Name0 from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName0 like "%Adobe%"

SMS_G_System_ADD_REMOVE_PROGRAMS is the hardware-inventory class backing the combined v_Add_Remove_Programs view, so most reporting queries built through the console will already be working from the right source without needing to touch the underlying SQL view names directly.

A Note on Reliability

All five views depend on the Add Remove Programs hardware inventory class being enabled and having actually run on the client — a machine with hardware inventory disabled, or one that hasn’t completed a scan cycle since a piece of software was installed, simply won’t show up in any of these views regardless of what’s genuinely installed on it. If a report looks incomplete, check the client’s last hardware scan date before assuming the software genuinely isn’t there.

Resources

• WQL
• our SQL & WQL query library