In my previous post about how to create a custom report in ConfigMgr 2012, I showed you a nice trick where you can create an in-console Query, execute it and then grab the SQL query statement from the SMSProv.log file. Now that we’re familiar with this process, let’s create a custom report where we want to enter some input to search for.

In this post we’ll be using the method of creating a custom report for ConfigMgr 2012 with Report Builder, so please read my other post about how to get your hands on the SQL query statement if you havn’t already. I’ll assume that you know the basics of how to create a custom report from the ConfigMgr console.

Overview

  • Scenario
  • SQL query statement
  • Create a custom report with parameters
  • Running the report

Scenario

In this example, I’ll create a custom report that accepts an input parameter for a MAC address to identify a computer. What I want to do is to list the Computer Name, Last Logged on User and Active Directory Site based upon the results.

SQL query statement

Since we’ll be creating a custom report where we’re able to enter an input parameter, I’ve created an in-console Query that prompts for the MAC address value.

79_1

When executing this in-console Query I’m able to get the SQL query statement from the SMSProv.log file. The SQL query statement I’ll be using is the following:

select distinct 
SMS_R_System.ItemKey,
SMS_R_System.Name0,
SMS_R_System.User_Name0,SMS_R_System.AD_Site_Name0
from vSMS_R_System AS 
SMS_R_System LEFT OUTER JOIN 
System_MAC_Addres_ARR AS 
__ystemSystem_MAC_Addres_ARR0 ON 
SMS_R_System.ItemKey = __ystemSystem_MAC_Addres_ARR0.ItemKey where 
__ystemSystem_MAC_Addres_ARR0.MAC_Addresses0 = (@MACAddress)

I’ve made a small change to the statement based on that we want to able to input a value for a MAC address in the report that we will create in a bit. So I’ve changed the last part from:

__ystemSystem_MAC_Addres_ARR0.MAC_Addresses0 = N'00:00:15:3D:44:01'

to this:

__ystemSystem_MAC_Addres_ARR0.MAC_Addresses0 = (@MACAddress)

By changing this, we’ll get the value of a parameter called @MACAddress.

Create a custom report with parameters

We’re now going to create the custom report, and for this example I’ve chosen to create the report in the Hardware – Network Adapter folder under Monitoring – Reporting – Reports.

1. Select the Reports node, right click and select Create Report.
2. Select SQL-based Report, give it a name and choose the Path to store the report in. Click Next.

79_2

3. Click Next on the Summary page and complete the wizard.
4. Report Builder will now open up. Expand Data Sources and make sure that you have a dataset called AutoGet__xxxx_xxxx_xxxx_ (what replaced here by X is different for every setup).
5. Right click on Parameters and select Add Parameter.
6. On the General page, give the parameter a name. I’ve chosen to go with MACAddress. Configure like the picture below:

79_3

7. Leave the default values on the rest of the settings on the Available Values, Default Values and Advanced pages.
8. Click on Table or Matrix in the middle of the main window.
9.  Select Create a dataset and click Next.
10. Wait for the wizard to find available Data Source Connections. Once found, click on Test Connection and if the connection was successful, click Next.

79_4

11. Enter the password for the Reporting Services Point Account (you can read more about that account in my previous post) and click OK.
12. On the Design a query page, click on Edit as Text.
13. In the text field, enter the SQL query statement. If you’d like to execute the query, you can verify that it works by click on the exclamation mark. You’ll then be prompted to enter a value for the @MACAddress parameter. Enter a MAC address of one of your clients in the Parameter Value column and click OK. If the query returned any value, it will be shown in the box below the text area. Click Next.

79_5

14. Drag all of the the available fields to the Values box.

79_6

15. Click Next on the Choose the layout page.
16. On the Choose a style page, select a style and click Finish.
17. You’ve now created the dataset. You’ll find the information if you expand Datasets. I’ve choosen to rename the table column headers and to extend some of the columns to give them some more space. This can easily be done the same way that you’d edit a table in Word.

79_7_1

18. Click on the Report Builder menu and then Save.

Running the report

If we now try to run the report, we’ll be asked to enter a value for the parameter MAC Address as shown in the picture below.

79_8

When we enter a MAC address in the text field and click on View Report, the report will run and if any matches was found the results will be presented.

79_9

That’s it!

Nickolaj Andersen
Principal Consultant and Enterprise Mobility MVP since 2016. Nickolaj has been in the IT industry for the past 10 years specializing in Enterprise Mobility and Security, Windows devices and deployments including automation. Currently working for TrueSec as a Principal Consultant. Awarded as PowerShell Hero in 2015 by the community for his script and tools contributions. Creator of ConfigMgr Prerequisites Tool, ConfigMgr OSD FrontEnd, ConfigMgr WebService to name a few. Frequent speaker at conferences and user groups.

(793)

There are no comments.

Leave a Reply