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).

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;
   
   
   
3. Maximize the screen in order to have a good view. On the left side, under Entities, select Event (don’t double click);
   
   
   
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;
    
   
   
5. The Report Design area looks like this now:
   
   
   
6. Under Entities, select Event Detail. Under Fields, select Rendered Description and drag it into the Report next to Event Display Number;
    
   
   
7. The Report Design area looks like this now:
   
   
   
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:
   
   
   
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;
      
    B - The Define Formula screen is shown. Go to the tab Functions > Text and double click on UPPER > the Formula screen shows this now:
    
    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;
     
    D -The Formula screen looks like this now:
    
    E -Click OK and run the report again in order to see all works as it should (the computer name is in UPPERCASE now):
    
    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:
    
    Click OK and run the report in order to see what progress we made:
      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.
      In the toolbar (above in the screen) click on Format and select Number. Select a Format which is short but shows enough information.
    
    Click OK. Run Report to see how it looks:
    
    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:
    
    Run the Report again. Looks better already:
    
    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:
    
    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.
     
    Run the Report in order to see all is OK now:
    
    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!