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]