Wednesday, August 17, 2011

My First Little Report – Part I: Let’s make a ‘stupid’ report

----------------------------------------------------------------------------------
Postings in the same series:
Part II – Let’s add some brains…
----------------------------------------------------------------------------------

As stated before I would post an article about a Report created in Ad Hoc Report Builder, the web based Report Builder which starts when one clicks the option Design a new report in the SCOM R2 Console).
image

Soon I noticed I have too much to tell for just one posting. Instead I’ll use two postings. Just to get the message across in a good manner. The first posting (this article) will be about the basics. At the end you have a report which just runs. So it lacks ‘intelligence’ like input from the user. Hence a ‘stupid’ Report. The second posting in this series will be about adding filters and so on.

Let’s start!

Prereqs
Before one starts there are some prereqs to be met: two Report Models, present on the installation media of SCOM, need to uploaded to your Report Server. Fellow MVP Pete Zerger blogged about it, to be found here. When those Report Models are in place (actually they’re nothing more than simplified presentations of the Data Warehouse database) AND the Data Source is corrected, one is ready to start.

Why do I need this Report?
Let’s say you have made a Rule which collects certain Events. Rules like these are also known as Event Collection Rules. Rules like these don’t fire Alerts but collect the Windows Events they are meant for and put them into the Data Warehouse database. So in order to see on what servers those collected Events took place, one needs to run a Report, showing those Events per server, together with a proper description. On top of it all, when the event took place multiple times on a certain server, you want to see ALL those events and not an aggregation of it. They can be counted yes, but every single event must be shown in that Report.

Typically one can use the Reports available in the Microsoft Generic Report Library and use the Event Analysis or Custom Event Reports. But both Reports are not fitting the bill here. One Report shows the events but not the description. Further drilling is required for those details. But the Report must show the relevant details in a single glance.

The other generic report aggregates the collected events per server and shows only the details of the last event of that server. So now it’s time for another approach.

Let’s start
As stated before, Ad Hoc Report Builder isn’t a very strong tool but can help out sometimes in situation like these.

  1. Start the SCOM Console > Reporting pane > Select a Report (don’t double click it) and click on the link Design a new report in the Actions Pane;

  2. Ad Hoc Report Builder is loaded and started. Both Report Models are shown: Event and Performance. Select Event > Table and click OK;
    image

  3. Maximize the screen in order to have a good view. On the left side, under Entities, select Event (don’t double click);
    image

  4. On the left side, under Fields, drag & drop 2x Event Logging Computer Name , 1x Date Time and 1x Event Display Number to the Report Design area;
     image

  5. The Report Design area looks like this now:
    image

  6. Under Entities, select Event Detail. Under Fields, select Rendered Description and drag it into the Report next to Event Display Number;
    image image

  7. The Report Design area looks like this now:
    image

  8. Click on the button Run Report, just to see what it looks like. The Report needs some serious attention but at least you can see what you’re building:
    image

  9. Click on the button Design Report so we can make the Report look better. First of all the Field Event Logging Computer Name is found twice in the Report. That’s with a purpose. The first time it will display the Server Name and the second time it will be used as a counter in order to see how many times the event took place on a certain server.

    Also the Computer Name should be displayed in upper case, the Date notation is way too long, and the header titles require a serious overhaul. So let’s start!

  10. Setting the Computer Name in upper case:
    A - Right click under the blue column name in the field and select Edit Formula;
    image  
    B - The Define Formula screen is shown. Go to the tab Functions > Text and double click on UPPER > the Formula screen shows this now:
    image
    C - Remove the entry Name (click it once and delete it). Repeat the same step for the entry string. The cursor must now reside between both brackets. Go to the tab Fields. Under the header Fields double click on Name;
    image 
    D -The Formula screen looks like this now:
    image
    E -Click OK and run the report again in order to see all works as it should (the computer name is in UPPERCASE now):
    image
    F - Don’t mind the name of the columns. They will be changed later on. Click on the button Design Report.

  11. Configuring the second Computer Name to a counter:
    Follow Steps A and B as mentioned in item 10. Only, in Step B, don’t select Text but Aggregate > Count.
    Follow Step C and the Formula screen looks like this now:
    image
    Click OK and run the report in order to see what progress we made:
    image  Click on the button Design Report.


  12. Changing the Date Time format:
    As mentioned before, it’s too long. Click once on the field under the header Date Time.
    image  In the toolbar (above in the screen) click on Format and select Number. Select a Format which is short but shows enough information.
    image
    Click OK. Run Report to see how it looks:
    image
    Click on the button Design Report.


  13. Renaming the headers:
    The names of the headers can be changed. Works like Word or Excel for that matter so I won’t go in to details here. These are names I have chosen:
    image
    Run the Report again. Looks better already:
    image
    As you can see the columns Count and EventID have too much space. And the FQDN of the server name lacks some space. Also the Count and EventID look better when they’re centered. Let’s adjust it as well. Click on the button Design Report.

    Just hover your cursor between two columns. The shape of the cursor will change into two arrows with a stripe between it. Hold the left button of the mouse and drag your mouse. The width of the column will change now. Be careful not to make the columns too wide. When the report is printed on pdf for instance it won’t fit on the width of a single page anymore and make the reports look awful.

    A rule of thumb here is not to make the report wider than the body of the title:
    image
    In order to center the columns (and their fields as well) Count and EventID, select the column Count by clicking once on it. Now it’s selected. Click right and select Format. Go to the tab Alignment and select under the option Horizontal Center. Click OK. Repeat this for the field under the column Count as well. Do the same for the column and field EventID.
    image 
    Run the Report in order to see all is OK now:
    image
    Looking better already! Nice! Click on the button Design Report.

Even though the Report looks fine now, it’s a ‘stupid’ Report.

Simply because the user of the Report isn’t allowed to change anything. Like start/end date, the EventID number etc etc. The report just runs and shows ALL collected events present in the Data Warehouse, no matter what.  So more actions are required. Some filters need to be applied and the user must be prompted for input.

And when the Report is OK it needs to be uploaded to the Report Server. And perhaps even in a special folder so the users can differentiate between the usual SCOM Reports coming from the imported MPs and your Reports!

The next posting will be all about that. See you all next time!

12 comments:

Dominique said...

Hello,

At step 7 I have small differences... the Date & Time, Event Display Number, Rendered Description qll appear under Event they don't have their own banner as in your example...
Is it normal?

Thanks,
Dom

Dominique said...

Hello,

and then after 20 minutes I am getting an error...
Report execution error
The report might not be valid or the server could not process the data

What is the issue?
Thanks,
Dom

Dominique said...

Semantic query execution failed. A severe error occurred on the current command. The results, if any, should be discarded.
Operation cancelled by user.
----------------------------
Cannot read the next data row for the dataset dataSet.
----------------------------
An error has occurred during report processing.

Marnix Wolf said...

Ho Dominique.

Looks like you have chosen the wrong items here. From a distance it is hard to say anything solid about the other errors you mentioned. Can you give me more details?

Cheers,
Marnix

Dominique said...

Quick Update it works if I select Event DIsplay Number and Event Logging Computer Name only apparently it could be the date & time .. http://skamie.wordpress.com/2011/06/15/ssrs-error-cannot-read-the-next-data-row-for-dataset-dataset1/
still in progress...
Thanks,
Dom

Dominique said...

Hello Marnix,

the last failures seems to be an issue with the OperationsManagerDW database and/or tempdb on the DW server as the Primary Full Group is full !!!

Is it really creating a lot of transactions?

Thanks,
Dom

Marnix Wolf said...

Hi Dominique

Good to hear things start to work for you. For what I have seen is that different date and time settings because of regional settings can cause issues.

Cheers,
Marnix

Marnix Wolf said...

Hi Dominique.

On itself it isn't a very exiting query at all. So my guess is the DW database was already pretty full.

Cheers,
Marnix

Dominique said...

Hello,
I am now "expert" :) ... I need to split the Alert description... as it contains teh Parameters [1] - [5] I need to isolate the Ip, Servername, etc... one column for each ..
any idea?
Thanks,
Dom

epinion said...

Do you know why Microsoft removed the Design a New Report option from the Reporting Console in SCOM 2012 SP1?

Marnix Wolf said...

Hi Epinion.

No, that eludes me as well. See here how to open Report Designer through an alternative way.

Cheers,
Marnix

Marnix Wolf said...

Hi Epinion.

No, that eludes me as well. See here how to open Report Designer through an alternative way.

Cheers,
Marnix