If you followed the community lately, you see some are writing blog posts about reporting in System Center. It is funny as I also have been playing around with Report Builder 3.0 lately and it seems that I am following this trend.
Building custom reports is not that difficult if you have some sort of reporting background using tools like Crystal Reports or Access reports :). The most important point is to have the appropriate SQL query which delivers the actual data you need. Luckily there are great posts in the community which help you getting over this. One post I highly recommend is Kevin Holman useful SQL query post. Although this post contains queries for SCOM 2007 I suppose most of them are also valid for SCOM 2012. In my case I wanted to build some reports for Agentless Exception Monitoring (AEM). In Kevin’s post there was exactly what I needed. AEM Queries to get Agentless Exception Monitoring information out of the SCOM data warehouse. I used this query here…
Because Agentless Exception Monitoring offers you “only” 4 basic reports, which is not a whole lot, I decided to write my own reports the way I needed it. If you also look around for some custom reports there are not many available and if they are they might not work with SCOM 2012 for some reason.
Some of the questions you have to ask your self before building custom reports are:
- What information do I want to have on my report?
- How do I pull this information together using SQL?
- How do I want to display the information on the report? Should I use tables, charts, pies etc. ?
- What parameter do I need to implement into the report?
- Who is the consumer of the report?
- Do I need some sort of limitation to build into the report, like selecting only the top 10 records, because the chart is getting too big for the data that could be returned from the query.
- How would the report look if I export it into Excel?
There are many more questions but finally just go ahead and try to build your own reports. If you need some good starting points there is a post from Marcel Zehner (MVP) Creating Custom Reports or a good tutorial on TechNet Tutorial (Report Builder 3.0) .
Report 1 – AEM.Top10.Computer.Error & AEM.Top10.Computer.Error.Detail
The AEM.Top10.Computer.Error report shows you the computer with most errors within a specified time span. If you click onto the computer name in the detail table the detail report AEM.Top10.Computer.Error.Detail will open.
Report 2 – AEM.Top10.User.Error & AEM.Top10.User.Error.Detail
Similar to Report 1 the AEM.Top10.User.Error report queries the users with most errors within a specified time span. If you click onto the user name in the detail table the detail report AEM.Top10.User.Error.Detail will open.
Report 3 – AEM.Application.Computer
The AEM.Application.Computer report shows all application which crashed. You can drill down to the computer where this program crashed and at which time the crash occurred.
How do I install the reports?
1) Browse to your report server URL http://[ReportServer]/Reports and choose a folder where you want to upload your reports. I select MyReports…
2) Click “Datei hochladen” or “Upload File”…
3) Click “Browse…” and select the report file (*.rdl) which you want to upload and click “OK”.
4) Go to your SCOM console and you should see the reports under Authored Reports
Make sure to upload AEM.Top10.Computer.Error & AEM.Top10.Computer.Error.Detail and AEM.Top10.User.Error & AEM.Top10.User.Error.Detail always together into the same folder otherwise you would not be able to run the detail report from the “parent” report.
Note: Be aware of the fact that the reports are based on RAW data. This means there is only data available for the last 30 days and this data is not aggregated. These reports are more for a short term analysis if you need immediately information about errors, computers or users. It is easier to run these reports and get the information in a more visible way than clicking through the Client Monitoring views in the SCOM console.
There are much fancier reports you could build but the reports should not look only great they also should present the data in an easy readable and printable way.