SCCM Inventory Reporting for Outlook Add-ins Pt.1

So last article we modified the Configuration.MOF to start collecting inventory on Outlook Add-ins.  Now we are going to create some reports out of this data.  With this information we'll be able to determine the most popular Outlook add-ins and where it is installed so we can take swift action if it will be a hindrance to our migrations.

So first thing we can do is open up SQL Server Management Studio and run a simple "Select *" to get an idea of what data we are looking at.  We are going to run a New Query and enter in "Select * from v_GS_OutlookAddins0"

Ok, so here we can see a quick listing showing the add-ins with the detailed information we listed in our Configuration.MOF.  Now lets see a quick count of the add-ins.  For that we are going to enter the following code:

-------------------------------------
Select FriendlyName0, Count(FriendlyName0) as 'Count'
FROM v_GS_OutlookAddins0
Group by FriendlyName0
-------------------------------------

Thats looking like something that we can use to get a count of all the various add-ins in our environment.  Now what if we want to find which computers have a specific plugin, such as "Fubar" installed?  Well let us try the following code to identify that bit of information:


-------------------------------------
Select v_r_System.Netbios_Name0, Description0, FriendlyName0, KeyName0, LoadBehavior0, Manifest0, Version0
From v_GS_OutlookAddins0
Join v_r_System ON v_R_System.ResourceID = v_GS_OutlookAddins0.ResourceID
Where v_GS_OutlookAddins0.FriendlyName0 like '%fubar%'
ORDER BY v_R_System.Netbios_Name0

-------------------------------------

Even more data that we can use.  This will help us identify everyone who has a specific add-in installed so we know where to focus our attention.  I think its time to mix things up and start throwing this information into SSRS so we can have proper reports for PMs, Senior Management, and other individuals that this data might be helpful for.

Lets start with the count of Outlook add-ins first.  Go inside the SCCM Admin Console and go to "Monitoring" and then "Reporting".  Right-click on "Reports" and select "Create Report".  Follow the wizard and you will be brought to SSRS Report Builder to start creating our report.

So from the Report Builder screen above we will double-click on "Table or Matrix" icon in the center.  This will bring us to a new wizard to start creating our report.  On the first screen we will choose "Create a dataset".

We will then hopefully be presented with choosing a data source.  It might already be populated for you but if not simply choose "Browse..." and browse to the root of your reporting site.  Once selected don't forget to "Test Connection" to ensure its working properly.

If prompted for credentials just enter the same ones you used during the initial setup of the Reporting Services Point role.  On the "Design a query" screen we will select "Edit as Text" and then paste in the SQL code we used earlier to gather a count.  You can then hit the red exclamation point to check the results.

On the next screen select the "Available fields" and drag them to the section labeled "Values".  From here you can just Next, Next, Finish through the wizard accepting the defaults.

You can save this report and then browse to your reporting website to double check the report works as expected.

In Pt.2 we will create a report on the SQL query we did earlier showing which computers have a specific add-in.  Then we will combine the two reports so you can see the count, and then click through into the specifics report.  Hopefully you'll check in with Pt.2 to learn report linking within reports.