Tuesday, December 27, 2016

SCCM Inventory Reporting Outlook Add-ins Pt.2

In Pt.1 we looked at some SQL queries and creating a report based on Outlook add-in counts.  Today we are going to create a report based on specific add-ins per machine.  We will also look at linking the two reports for quicker information gathering.

So first we will go into our SCCM Admin Console, "Monitoring", "Reporting", and right-click on "Reports" and select "Create new report".  Follow through with the wizard and you will be brought back to the Report Builder.

Similar to Pt.1, we will double click on "Table or Matrix" in the center of the screen.  Select "Create a dataset", select the appropriate data source, "Test Connection", enter credentials, and then "Edit as Text".  Here we will enter the SQL code below.  Take note that this is similar to the SQL code we used in Pt.1 except instead of looking for "%fubar%" we are going to use a variable.

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 = @variable
ORDER BY v_R_System.Netbios_Name0

We will highlight all the items in "Available fields" and drag them to "Values".  And finally Next, Next, Finish through the wizard.

Now on the left-hand pane right click on the folder "Datasets" and select "Add Dataset...".  On the "Dataset Properties" screen ensure you select "Use a dataset embedded in my report" and use the drop down to select a "Data source".  In the "Query" box you will enter the following SQL code.  Then click OK.

Select DISTINCT FriendlyName0 FROM v_GS_OutlookAddins0
ORDER by FriendlyName0

Ok, so DataSet2 will be used in our report for a list of choices for the "@variable" we used in the first DataSet.  Back on the left-hand pane expand the folder labeled "Parameters" and then right-click on "variable" and select "Parameter Properties".  On the "Report Parameter Properties" we will give the prompt a recognizable name that will appear in the report, modify the field labeled "Prompt:".

Still on the "Report Parameter Properties" for "variable" we will click on "Available Values" on the left-pane.  Here you will specify "Get values from a query", and select the appropriate DataSet that we created.....in my example it is DataSet2.  With the drop-down also select "FriendlyName0" for the "Value Field:" and "Label Field:".  Click OK to save changes.

Save the report and test it in the reporting website to ensure your drop down is correctly showing the Outlook add-ins in your environment and spits out the appropriate information when you run the report.

Ok, now we will link the report we created in Pt.1 with this report.  Basically, you will run the "Count of Outlook Add-ins" report and you can click on the "FriendlyName" to be brought to the "Computers with Specific Outlook Add-in" report with the variable now set to the "FriendlyName" you clicked.  A quick way to see which computers make up that count.

We are going to edit the "Count of Outlook Add-ins" report.  You can use Report Builder to just open the report.  From here we will simply right-click on "[FriendlyName0]" in the middle of the screen and select "Text Box Properties...".  On the "Text Box Properties" screen we will go to "Action" on the left-pane.  Ensure the option under "Enable as an action:" is "Go to report".  Then under "Specify a report:" browse to the report we created earlier labeled "Computers with Specific Outlook Add-in".  Under "Use these parameters to run the report" click on "Add".  Use the drop-down to select "Variable" for "Name" and then "[FriendlyName0]" for "Value".  Ok out of this and save the report.

Going back to the web reports and opening the "Count of Outlook Add-ins" report you will be able to click on the add-in name and be immediately brought to the next report "Computers with Specific Outlook Add-in".  In the screenshots below I clicked on "BlahBlahAddins" and it immediately brought me to the report showing which computers had that add-in installed.

You now have two very simple reports to get a grasp on your environment to help with Office migrations BUT you also learned a very simple technique to add some more pizzazz to your reports.  You can even take this further by linking the "Netbios Name" text box properties in the report "Computers with Specific Outlook Add-in" to another report that gives detailed system information about the computer that was clicked on.  Enjoy.

No comments:

Post a Comment