The most common question I get from my customers who are using Configuration Manager is without a doubt; “Can I get a report on this?”, “Is there a dashboard for this?” or other analytics.
Well, as most of you know, the reports straight out of System Center Configuration Manager (SCCM) isn’t always the best and there is no simple way of creating a dashboard either, at least not for the customer to use directly.
In this post I will be working with Analytics Plus from ManageEngine and show you how you can read data from the database directly and create a dashboard with three of the most common requests.
Analytics Plus integrates out of the box with other solutions from ManageEngine, ServiceDesk Plus and OpManager for example, but you also have the possibility to fetch data from other sources like MySQL, SQL and Oracle databases.
Get Analytics Plus
Analytics Plus is available in a 30-day trial and can be downloaded from the following link
I will be using my small environment for this which consists of:
- Int-DC01: Domain controller and hosts Analytics Plus
- Int-CM01: Primary Configuration Manager site
- Client01: Windows 10 client installed and managed with Int-CM01
The installation of Analytics Plus is as easy as clicking “Next” a few times and waiting for the installation to complete. One thing you need to take a note of is the port-numbers that will be used to access the webpage that hosts the interface. Standard ports are:
Once the installation is completed, verify that the services are running:
Since we will be working with the System Center Configuration Manager database we need to create a local SQL service account that will be used to read data from the SQL-server. This account should only have read access to the database, called CM_<SiteCode>. (In my case CM_P01)
Once this account has been created, connect to the server hosting Analytics Plus and open a web browser. Then go to the following link: “https://localhost:8443″
Note that its https and not http, also change the port if required.
You should see the following page:
Connect to a database
Once you have logged in to Analytics Plus you are presented with a few options on how to create a reporting database. As you can see, there are a few preconfigured options for other products from ManageEngine. But we will fetch data from our own database, so click on “Import from Local and Cloud Databases”
On the next page you have the option to either select a single table to import, or to write your own custom query. Here you can choose what you would like to do. Further down I will show you how to import new data with a query, so in this case I will select the first available table.
When selected, in this case table _394_CONTROLLER_DATA, you are able to give the table a new name that will be used in Analytics Plus and to select which columns to import. When done, click on “Create”
Once imported, you will see a summary of the import, and you will be able to set a scheduled on how often you would like to do this import. Depending on the data you want, the time is different, but I set the import to once every day.
When done, click on close.
Once you’ve clicked on close you will get a question if Analytics Plus should analyze and create reports for you. I’ve tried this on a few tables but unfortunately this doesn’t work all to well on the database for Configuration Manager, so I would recommend that you click on “No” at this time.
Then you will be presented with the table, in my case it will be empty since I don’t use this table in my environment.
Import data and create a new report
Okay, now that we have everything in place and know how to import data from our database, it’s time to create our first report that will actually show something useful.
Almost every head of IT or similar would like to know how many clients there are in the environment and what OS they are running, they don’t need to know the name of client or its location, just a number.
So the first report we will create will contain the above information in a nice chart view.
The first thing we need to do is to find out in which table in our database we can find the information we need. If we examine the database for our System Center Configuration Manager, we can select the top 1000 items from the table called “dbo.Operating_System_DATA”
As you can see, we have the name of the OS under “Caption00″, so we will use this table for this report.
Back in Analytics Plus we then click on “Import Data” in the upper right-hand corner.
We then select to import data from “Local and Cloud Databases”, which will bring up the same guide as before. Enter the details and click on “Next”.
For this scenario we will only need the data under “Caption00″, but for further use, I will select the following columns:
- Caption00 OR Name00
When done selecting your columns, give the table a name that will shown in Analytics Plus and then click “Create”. When done you should be redirected to the following view:
Click on “Schedule This Import” and choose how often new data should be imported, I choose to do this once every day. Then click on close to view the table:
The next step is to create our first chart view. To do this, click on the “Add-button”, just above “Edit Design” and select “New Chart View”.
One thing that I like about Analytics Plus is that creating reports and charts is really easy, all we need to do now, is to drag “Caption00″ from the left pane over to the box for “X-Axis” and change to a “Bar graph” instead of a “Pie chart”. The result should be similar to the following depending on the data in your table:
When done, save the chart and give it a name, Operating Systems for example.
Next, let us create another “Bar Chart”, so following the instructions above for Operating System, create one using the table called “EP_Malware” and when creating the chart, set ThreatName on the “X-Axis”.
Depending on your data, the chart will look similar to the following (I’ve infected my client with EICAR):
Import data with query and create a report
Well, the above is pretty nice, but still it’s just numbers. Another report that could be nice is to check the status of content that is being distributed to our distribution points.
Checking this in the console is possible, but it still requires several clicks and you need to check every package and application one by one, so in this example we will create a “Pie chart” that contains all of the information we need for a quick overview.
This however requires the we write our own query since we will be needing information from several different tables and views.
Below you see the beginning of the query. You can download the complete query here.
So, to import the data, do as before. But instead of selecting a single table, click on “Custom Query” and paste the query in the box and click on “Next”. You should be presented with a similar result depending on your data:
Give the table a name that will be used in Analytics Plus and then click on “Create”.
Then select how often new data should be fetched, in this case I would recommend every other hour.
With the table open, choose to create a new chart view and add “Content_Status” to the “X-Axis”. This will generate a pie chart based on the status and the result will be the following, depending on your data and statuses.
Save the chart by giving it a name and the go back to the tab called Explorer.
Creating a Dashboard
So, now we have three charts but handing this to the head of IT might not be the best idea, so we will create a dashboard that contains all of them.
To do this, simply click on “New” and select “New Dashboard”
In the new view, simply drag the charts we created over to the right and position them in a way that you like, the result should be similar to this:
Then save the dashboard and give it a suitable name, for example “Quick Overview”.
To see the new dashboard in action, click on “View Mode”. From here you can drill down to the data that the charts are based on for more information.
So, we have a nice dashboard with the information that updates every hour or day depending on what you’ve configured it to do. But how will we present it to the receiving person?
Well, you can either export it to a PDF or HTML file, send it directly in a mail, or even better, publish a permalink that will auto refresh.
To do this, simply click on “Publish” in the top-right corner and select “URL / Permalink for this view”. From here you can define auto refresh and what to include.
Creating reports and dashboards has never been as easy as with Analytics Plus. It still requires that you have basic knowledge in SQL and a good understanding of how the database for Configuration Manager is built, but a lot of the headache is removed when trying to create a dashboard in System Center Configuration Manager.
What happens now?
I will continue to develop guides for new reports and post them on my site for you to use, I have a few in mind, but if there is something specific that you would like to see, let me know and I will try to create it!