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.
- SQL query statement
- Create a custom report with parameters
- Running the report
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.
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'
__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.
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:
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.
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.
14. Drag all of the the available fields to the Values box.
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.
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.
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.