SCCM 2012 – Create Report with SQL Report Builder

In this post I would like to Show, how to create a Basic Report with System Center 2012 Configuration Manger SP1. Besides your SCCM 2012 Installation you will need Access to the Report Service and have installed the SQL Report builder. Let’s say you will a simple Report, that Shows the Resource ID, NetBios Name and the Last Hardware Scan of your machines.

Create Report

Chose “Create Report” and the “Create Report Wizard” appears, where you have to add the Information about your specific report. In my case, I created a new Folder to store the report, I didn’t want to mix up custom and built-in reports.
Create Report WizardComplete this wizard, and with clicking on Close, it will ask you to install the SQL Report Builder if it is not already installed. Once the Report builder is started, click on “Table or Matrix:
report builderIn the “New Table or Matrix” wizard, Chose to “Create a dataset” and click on next. A similar Screen should appear:report builder
You will be asked for credentials to connect to the report Service. I just used the same Account as I use when I set up the report role:
report credentialAnd if you enter the Username and Password correct, it is possible, that you receive the following error:

The certificate chain was issued by an authority that is not trusted
—————————-
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.)

Picture: report sql connection errorThis relies on the used certificate in the SQL Configuration, usually it is a Self-Signed Certificate, which your Client does not trust. I just change the certificate to one of my PKI:
change sql cert

After this, just restart the SQL Server Service:
sql restartAfter this small hurdle, you can start with your report, but let us start with our easy report, add Name and Last Hardware Scan Date to the report. Open the “Views” Tree and navigate to “v_GS_SYSTEM”, select “ResourceID” and “Name0”:
add first tableTo get the Information about the Hardware Scan navigate to “v_GS_WORKSTATION_STATUS” and add “LastHWScan”:
second tableNow we have added Information from two different tables, and we have to create a Realtionship between the tables. You might already recognized that this could be done easily by using the ResourceID from both tables, click on “Autodetect First” and then on “Add Relationship”:
relationship 01Add both tables on one side of the join type and double-click on the right section:
relationship02In the “Edit Related Fields” wizard click on “Add Field” and chose the “ResourceID:
Relationship03Click on next, and arrange the fields for getting the report:
Arrange FieldsChoose your Settings for the report:
arrange fieldsThe printscreen for choosing a style i didn’t share with you, because it’s secret 😉
Click on Finish, and save the report. Now it is time to open your Report within a Configuration Manager Console:
run reportIf you have edited the Design just a Little bit (deleted a row and sized the rows), it could look like this:
Report FinalHope this helps.

One thought on “SCCM 2012 – Create Report with SQL Report Builder

  1. To figure out the certificate issue you can also do following steps:

    On the machine with the Report Services installed:

    Start>run>mmc>file>add/remove snapins>certificate>Computer Account>Local Computer>Certificates>Personal>Certificates>Issued to: Name of server

    Look for a Certificate with a friendly Name “ConfigMgr SQL Server Identification Certificate”. Export that cert.

    Then on the machine you running the report builder, import the certificate to the local computer trusted root.
    Reconnect to the Report Builder.

    Hope that helps.
    Al

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s