Advanced reporting against Add/Remove Programs (ARP), filtering and appending data
I’m not a SQL expert by any means, so if you see something that isn’t quite right, well i already said I'm not a SQL expert :) This process involves custom tables and modifying your SQL database, proceed at your own risk. Don’t say i didn’t warn you…
Whenever we do a refresh/ZTI of a machine or build a replacement computer, we always have to run reports to identify what software was/is installed on that machine, identify what applications are supported or unsupported and identify what Active Directory groups are needed for the correct software distribution. This process always took a bit of time and wasn’t too bad if you had to run it on one or two computers, but when we needed that data on 100 pc’s it took some time to compile. So we needed a solution.
One thing we really wanted to do was be able to filter out data from ARP, things that show up like drivers or windows updates or other things we know that we can filter out to help get the list down on size. We also wanted to be able to add data to matching entries, so we could identify the correct supported version of an application (if the user had an older one) and also identify any notes, and the Active Directory group associated with SMS/SCCM for software distribution. The one thing that was important to me was that it would always spit out the ARP data even if there wasn’t a match, so if we hadn’t gotten to that application yet and identified it as supported or not supported, that it would still show up in the report so nothing got missed.
The end result for us was 2 web reports:
Supported VS Non-Supported Applications by Collection (with collection prompt)
Supported VS Non-Supported Applications by Computer (with computer prompt)
Now the first thing to understand is how to control the output data or the filtered data, however you want to look at it. We have 2 tables that we used behind the scenes to do the filtering and append the output data.
We named the tables “AddRemoveProgram” and “AddRemoveProgramIgnore”
AddRemoveProgramIgnore has 3 columns:
AddRemoveProgramsIgnoreID, ProgramName, Notes
We use variables to help filter out the data we know we don’t want to see, you need to be careful with what you put in here as you could accidentally remove useful data. So i would recommend you test the results and make sure you are isolating what you want removed. But it’s very handy for us to remove several thousand entries of useless ARP data. For example a common one is removing the hundreds of Windows Update entries that show up in ARP for a single computer. Now imagine running that on 100 or 200 computers, that's a lot of lines of data you probably don’t need to see.
The second table is AddRemoveProgram:
This table has 5 columns:
AddRemoveProgramID, ProgramName, SupportedProgramName, ADgroup, Notes
Again we allowed the use of variables so you could blanket statement a program, an example is Adminstudio, i don’t care if they have 8.5, 8.6, i just want to know that they have it and output the AD group and the supported version:
And the resultant web report entry:
Another reason for the variables is so that you can only have one line entry in the table instead of having to put every possible version of that application, for example Cisco VPN Client, if you have 4.8 and 5.0 in your environment you would need to have 2 entries in your table to be able to append that data, so instead we make use of a variable so you can you append all entries of Cisco VPN client with the correct data.
Here is an sample example output against my laptop:
You can see the various entries and the supported vs non-supported output. A common notes field entry for us to “verify license spreadsheet”. Another entry we have is that we have a supported application, but it’s a manual install. In other words its a support IT application but we have been unable to successfully package the application, so it will have to be installed after the refresh by a helpdesk technician. Here you can see the last column says “manual install”.
Now as a whole, you could easily add more columns if you needed more data or remove some of them if you didn’t need the same data as we do. The concept is the fact that you can filter our append the data to your needs.
The only major drawback to this method of data filtering is that you have to maintain the AddRemoveProgram table with your supported applications, however doing the work here for us outweighs the time spent elsewhere. We just have it setup at a bi-weekly task that someone goes in and updates the table with the newest entries.
We currently filter out 21 items with our Ignore table. If i run a standard ARP report against my laptop, i show 268 entries, if i run the same report with our filtered data, i show 162 entries. So now multiply that by 10, 20, 100 computers… :) So we’ve managed to filter out data i don’t need to see as well as make it easier to identify what needs to be done to refresh that computer, I know what groups my laptop has to be in to get the correct software (if i’m not already in those distribution groups).
Query DATA:
Here is the query for the by computer report:
select distinct
sd.Name0 as ComputerName
, arpd.DisplayName0 as Program
,CASE
WHEN arp.ProgramName is null then 'Not Supported'
ELSE 'Supported'
END as ProgramType
, arp.SupportedProgramName
, arp.ADGroup
, arp.Notes
from dbo.v_GS_ADD_REMOVE_PROGRAMS arpd
INNER JOIN dbo.v_GS_SYSTEM sd on arpd.ResourceID = sd.ResourceID
LEFT OUTER JOIN dbo.AddRemoveProgram arp on arpd.DisplayName0 LIKE arp.ProgramName
LEFT OUTER JOIN dbo.AddRemoveProgramIgnore arpi on arpd.DisplayName0 LIKE arpi.ProgramName
WHERE sd.Name0 = @compname
AND arpi.AddRemoveProgramIgnoreID IS NULL
AND COALESCE(arp.SupportedProgramName, arpd.DisplayName0) IS NOT NULL
order by 1,3,2
Computer Prompt:
begin
if (@__filterwildcard = '')
SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS ORDER By SYS.Netbios_Name0
else
SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS
WHERE SYS.Netbios_Name0 like @__filterwildcard
ORDER By SYS.Netbios_Name0
end
Here is the query for by collection report:
select distinct
sd.Name0 as ComputerName
, arpd.DisplayName0 as Program
,CASE
WHEN arp.ProgramName is null then 'Not Supported'
ELSE 'Supported'
END as ProgramType
, arp.SupportedProgramName
, arp.ADGroup
, arp.Notes
from dbo.v_GS_ADD_REMOVE_PROGRAMS arpd
INNER JOIN dbo.v_GS_SYSTEM sd on arpd.ResourceID = sd.ResourceID
LEFT OUTER JOIN dbo.AddRemoveProgram arp on arpd.DisplayName0 LIKE arp.ProgramName
LEFT OUTER JOIN dbo.AddRemoveProgramIgnore arpi on arpd.DisplayName0 LIKE arpi.ProgramName
INNER JOIN dbo.v_FullCollectionMembership cm on arpd.ResourceID = cm.ResourceID
INNER JOIN dbo.v_Collection c on cm.CollectionID = c.CollectionID
WHERE c.CollectionID = @collection
AND arpi.AddRemoveProgramIgnoreID IS NULL
AND COALESCE(arp.SupportedProgramName, arpd.DisplayName0) IS NOT NULL
order by 1,3,2
Collection Prompt:
begin
if (@__filterwildcard = '')
select CollectionID, Name from v_Collection order by Name
else
select CollectionID, Name from v_Collection
WHERE CollectionID like @__filterwildcard
order by Name
end