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 Citrix servers that should be the same and ask 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]