Share This Post

SCCM Report Comparing Add/Remove Programs on two Computers

Yes, I know that the builtin Report 97 lists differences between the software inventory reported for two selected computers. BUT! That is not what I want, as this report lists only the discrepancies, and more importantly it is based on software file collection, not for Add/Remove programs. I want a list that shows me what is different (on each computer), and what is the same between two computers in regards to Add/Remove Programs. This SQL query I created utilizes multiple subselect queries and requires 2 prompts. I have found this report to be a great help when troubleshooting, especially when looking at 2 servers that should be the same and am asking myself, what is different?

First, when creating the report, paste in the SQL statement found at the very bottom of this post. Then, click on the Prompts button to open the next dialog.

Click on the New button, which will open the new prompt dialog. We need to create 2 prompts so that we can enter 2 computer names. The first variable we want to enter is Computer1, with Prompt text as shown. Next, we also want to Provide a SQL statement so that we can use wildcards to find our computer names a little easier. Use the following SQL statement for the "Prompt SQL statement":

begin
if (@__filterwildcard = ”)
SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1 ORDER By SYS.Netbios_Name0
else
SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1
and SYS.Netbios_Name0 like @__filterwildcard
ORDER By SYS.Netbios_Name0
end

Afterwards, make another prompt just like above and name it Computer2. When finished, your prompts should look like this.

Hit OK, finish the wizard, and take it for a spin!

************* SQL STATEMENT BELOW HERE ************

SELECT one.Computer1Name AS [Computer 1 Name], one.Computer1Display AS [Display Name], one.Version0, two.Computer2Name AS [Computer 2 Name],
two.Computer2Display AS [Display Name], two.Version0
FROM (SELECT v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 AS Computer1Display, v_R_System.Netbios_Name0 AS Computer1Name,
v_GS_ADD_REMOVE_PROGRAMS.Version0, v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
FROM v_GS_ADD_REMOVE_PROGRAMS INNER JOIN
v_R_System ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID
WHERE (v_R_System.Netbios_Name0 = @Computer1) AND (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 IS NOT NULL))
AS one FULL OUTER JOIN
(SELECT v_GS_ADD_REMOVE_PROGRAMS_1.DisplayName0 AS Computer2Display, v_R_System_1.Netbios_Name0 AS Computer2Name,
v_GS_ADD_REMOVE_PROGRAMS_1.Version0, v_GS_ADD_REMOVE_PROGRAMS_1.InstallDate0
FROM v_GS_ADD_REMOVE_PROGRAMS AS v_GS_ADD_REMOVE_PROGRAMS_1 INNER JOIN
v_R_System AS v_R_System_1 ON v_GS_ADD_REMOVE_PROGRAMS_1.ResourceID = v_R_System_1.ResourceID
WHERE (v_R_System_1.Netbios_Name0 = @Computer2) AND (v_GS_ADD_REMOVE_PROGRAMS_1.DisplayName0 IS NOT NULL)) AS two ON
one.Computer1Display = two.Computer2Display
ORDER BY [Computer 1 Name], [Computer 2 Name]

Share This Post

Leave a Reply