Config Manager Reporting & The Ultimate Computer Inventory Report
Have you ever wanted to make your own reports in Config Manager but just not had the time to dive in? Me too, but finally I forced myself to sit down and dive in. I'm working on a project right now where we are doing an available application deployment by device, but we want to be able to nag our users by email if they haven't done it yet. So how do you turn a device into an email address?
I decided to make a custom report that shows me a list of all machines in a collection and then gives me some key info, but most importantly it gives me the top console user and their email address. Our user discovery brings in the "mail" attribute for users so this information is already stored in our Config Manager database.
Part 1: The SQL Foundation
If you haven't figured it out yet, you're building a SQL query (from this query designer GUI) that you will later use to build a new report. Now that you have all your views on the screen, it's time to add some Joins so they can all work together. All the views I selected are Device based, with the exception of one that is User based. I'm going to join all of the Device based views based on the value "ResourceID" which is what Config Manager uses to identify a Device across views. The view that I want to use as my "anchor" is going to be dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP. Drag the "ResourceID" from each of the other Device based views and drop it on the "ResourceID" in the dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP view. When you do this, you'll notice an "anchor line" now connects them. I have one User based view (dbo.v_R_User) that I want to use in this query to get the users' display name and email address. I'm going to drag the Unique_User_Name0 from the dbo.v_R_User view over to TopConsoleUsers0 on the dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP view... these two values match and can be used for this join.
You can now run the query to start seeing your hard work pay off. Right click anywhere on the query and click on Execute SQL. You'll now see a table at the bottom of the screen populate with data from your query. Note the columns are in the order you specified and the alias names make it friendlier to view.
In the Report Data pane of Microsoft Report Builder, you'll see Data Sources. Under Data Sources, you'll now see one named AutoGen_xxxxxx which is a Data Source unique to your environment. You now need to add a new Dataset to your report which is where the SQL query will come into play. Right click on Dataset and then click Add Dataset. Select Use a dataset embedded in my report, make sure the AutoGen_xxxx is selected as the Data source, Query type is set to Text, paste your SQL query from the previous steps into the Query box, and click on Refresh Fields. You may get a prompt to enter your reporting credentials, so have those at the ready. Assuming you're error free, click OK to save the new Dataset. I've pasted a text copy of my exact SQL query below the screenshot.
Now that you have your Dataset, it's time to put it into a table. Click on Table or Matrix and you'll get a wizard to create the table.
In the New Table or Matrix wizard, choose the dataset that you created from the SQL query and click Next.
Drag everything from Available Fields to Values and click Next. Click Next one more time and then click Finish.
You'll now see your table with data. Hit Run to see the results. You'll notice a lot of wrapping going on which makes your rows oversized. Drag your columns to a larger size in order to eliminate the row wrapping.
I like to add alternating colors for every other row of data. To do this, highlight the bottom row in the designer. On the Properties pane to the right, click on the Fill to expand it. Then click the dropdown next to BackgroundColor and then click on Expression.
Next we want to make sure the headers stay visible as you scroll and that they stay on each page. Highlight the header row, click the dropdown on Column Groups, and check the box for Advanced Mode. Then click on (Static) under the Row Groups side.
When you highlight (Static) under the Row Groups section, you'll get a Tablix Member properties pane on the right side. Set FixedData to True and RepeatOnNewPage to True.
If you save and Run the report now, you should note that your rows are alternating in color and your header stays at the top both as you scroll or change pages. Now we're going to add the ability to filter this report with a Device Collection parameter. You'll need to add a new Dataset to your report. You'll use the same method as the previous Dataset you added. The query and a screenshot of it are both below.
Once you've added the second Dataset, you'll need to add two lines onto the query on the first Dataset you made with all the info. For this example report you will need to join the ResourceID from v_FullCollectionMembership (fcm) to the ResourceID on v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP which is the first line below and then you'll need to set the variable which is the second line below.
Name the Parameter Collection and put in a description of Select a collection. Make sure the parameter is set to visible. Under the Available Values section, select Get values from a query. Select the Dataset that you made second for the collections, select the CollectionID for the Value field and Name for the Label field.
Save your report and give it a run. If you did all these steps, it should look like the screenshot at the very top of this post. I've also uploaded the RDL to my GitHub, so feel free to download it if you aren't down for making it from scratch.