Wednesday, August 31, 2011

Great tool for exporting ALL RDLs and Data Sources

Based on a posting of mine about how to export RDLs I got feedback from one my readers, Sharon Abarbanel. He has made a tool which enables you to export ALL RDLs and Data Sources from you ReportServer database.

Since his comment made me curious, I downloaded the tool and tested it. Even though the interface is really basic, the tool does exactly what it promises to do. Which is AWESOME! Here are some screen dumps of the tool running in one of mine test environments:

Screen 01
Let’s enter all the required information (account and password for accessing SQL), SQL server name, Report database name and the path to where the exported files will be saved. Also one can select what to export: Reports and/or Data Sources.
image

Screen 02
For a while nothing seems to happen. Be patient. The tool is trying to connect to the SQL instance, ReportServer database, based on the credentials you entered.
image

Screen 03
Patience is needed here as well. All Reports and Data Sources are enumerated and exported.
image

Screen 04
The export ran fine. All RDLs and Data Sources are exported successfully.
image

image

This tool is really AWESOME. It makes it a whole lot easier to export RDLs and Data Sources. Another great tool is added to my toolbox.

All credits go to Sharon Abarbanel. Tool can be downloaded from his blog, to be found here.

Tuesday, August 30, 2011

Monday, August 29, 2011

How To: Export RDL files from SQL Server 2005 or SQL Server 2008 R2

Sometimes I get questions from customers about rdl-files. Even though I already posted an article about how to upload rdl-files , I also get questions about how to download them. Why would some one want do that? Well, suppose you’ve made some nice customized Reports with Ad-Hoc Report Builder, people like to save those Reports outside the SSRS instance as well.

Since exporting those rdl-files isn’t very straight forward process, I have decided to blog about it. So next time I get a question about it, all I have to do is to refer to this posting.

One must know there are differences in procedures how to download rdl-files between SQL Server 2005 and SQL Server 2008 R2. Therefore I’ll describe both of them. Let’s start.

SQL Server 2005
It starts with the Report Server, which is a web based interface. When running a straight forward installation/configuration – the default SQL instance – the url is /Reports">/Reports">/Reports">/Reports">http://<yourSSRSservername>/Reports.

  1. Open IE and browse to your Report Server and click on the button (left side of the screen) Show Details;
  2. Select the Report you created earlier with Ad-Hoc Report Builder and you want to export now;
  3. Click the Properties button;
  4. Now the Report is shown in Edit mode with the tab General selected by default. In that screen there is a header named Report Definition. Under this header the option Edit is shown. Click it;
  5. The dialogue box File Download is shown. Click the Save button and now one can save the file to any location.

SQL Server 2008 R2
It starts with the Report Server, which is a web based interface. When running a straight forward installation/configuration – the default SQL instance – the url is /Reports">/Reports">/Reports">/Reports">http://<yourSSRSservername>/Reports.

  1. Open IE and browse to your Report Server;
  2. Select the Report you created earlier with Ad-Hoc Report Builder and you want to export now;
  3. In the drop down menu: select the option Edit in Report Builder;
  4. Report Builder 3.0 (!) is started. Click on the SQL logo (top right of the screen);
  5. Select the option Save As and now one can save the file to any location.

As you can see it isn’t a difficult process, only it’s a bit hidden. None the less there are two things to reckon with:

  1. When you import the Report into another SSRS instance the Report might need some modifications (like the data source it connects to) and the default values (which will be different when the Report is targeted against another Management Group);
  2. When you have added a Description to the Report you export, that Description isn’t exported with the rdl-file.

Wednesday, August 24, 2011

AD Certificate Services MP throws EventID 1400: The permissions on this certification authority do not allow the current user to enroll for certificates. 0x80094011

Bumped into this issue. The AD Certificate Services MP was imported but the Views for the Certificate Authorities stayed empty.

So it was time to investigate. On the MS and RMS nothing strange was shown. The OpsMgr log of the CA showed this error however: CARoleDiscovery.vbs : Unable to determine the Common Name of the CA hosted by xxxxxxxxxxxxxx. Cause: CCertRequest: :GetCAProperty: The permissions on this certification authority do not allow the current user to enroll for certificates. 0x80094011
image

So the script couldn’t run.

After having adjusted the account (SYSTEM) to have enough permissions, all Views present in the MP were populated.
image

Tuesday, August 23, 2011

EventID 26319: An exception was thrown while processing GetStateByManagedEntitiesAndMonitorsAndLanguage

Funny. Thought had seen it all of EventID 26319. I already blogged about it in detail, to be found here.

Issue
But now the Description was totally different: ‘An exception was thrown while processing GetStateByManagedEntitiesAndMonitorsAndLanguage for session id uuid:b91bbcd2-1611-4404-a9b3-97437c03dfe0;id=3’.

And this event was logged three to six times in a row, per 90 seconds. So the OpsMgr event log of the RMS really looked alarming. Filled with errors.
image

So time to investigate.

How the West was won :)
Especially this part ‘GetStateByManagedEntitiesAndMonitorsAndLanguage’ of the description was new to me. Seemed almost like a localization issue to me. And yes, the RMS, MS and SQL servers are running on Dutch Format and Location settings. But I have seen many other customers running their SCOM environment with the same settings and WITHOUT these errors.

So that couldn’t be the issue, unless in this particular situation a MP is in place which isn’t used very often by other customers. A quick scan revealed to me that the KMS (Key Management Service) MP is in place. This MP isn’t used very often.

Besides that I am not very charmed of this MP either. It’s very basic so it can be replaced easily by a couple service and process Monitors as well. After having discussed it with my customer I removed the MP.

Soon the EventIDs 26319 were gone. So somehow the KMS MP doesn’t like Dutch localization settings :).

OM12 Infrastructure Topology Changes Overview

Rob Kuehfus, a Program Manager in the System Center Team, has posted an excellent article in which he describes the changes made to the Operations Manager 2012 infrastructure topology.

For any one interested in OM12 it’s an excellent write up. Very informative and easy to understand. Posting to be found here.

Thanks Rob for sharing this good information with us!

Sunday, August 21, 2011

My First Little Report – Part II: Let’s add some brains…

----------------------------------------------------------------------------------
Postings in the same series:
Part I – Let’s make a ‘stupid’ Report
----------------------------------------------------------------------------------

In the second and last posting of this series we’ll add some functionality to the Report, enabling the user to select a Start- and End date and what EventID number to report upon. So we’re adding some ‘brains’ to the Report.

Before we start there are two things to reckon with:

  1. It goes without saying that the Report can only contain information present in the Data Warehouse. So when you want to collect certain Events from your servers, make sure the required Event Collection Rules are in place AND running.

  2. You really need to have run through the steps described in the first Posting in this series before you can start with this article. So when you’re not on that level yet, go back see you later :).

Let’s start
In order to get some input from the user of the Report AND to have the Report take that input into account, we need to add a Filter to the Report. But before we do that, it’s better to create some new Fields.

Why? Suppose you run a Report and are requested for some input like a start- and end date and find fields like these:
image

I know, it does the job but it isn’t very user friendly either. From the start the user will notice that the Report isn’t very professional.

So when we create new Fields we’re allowed to make NEW names as well, like ‘Start Date’ and ‘End Date’. Looks better already, doesn’t it?

  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. Load the Report you made based on the first posting;

  3. When the Report is loaded go to Report > New Field. We’re going to create three new Fields: ‘Start Date’, ‘End Date’ and ‘EventID Number’.
    Let’s start with Start Date: enter as Field name Start Date, expand in the Fields column (lower left side of the screen) the Date Time node and double click on First Date Time. This Field is now added to the Formula for each Event: part of the screen. Click OK.
    image

  4. Repeat Step 3 for a new Field with the name End Date (select as Field Last Date Time) and for a Field with the name EventID Number (select as Field Event Display Number);

  5. In the Explorer pane under the header Fields the three new Fields are shown. Now we can start to create our Filter;
    image

  6. Click on the button Filter in the toolbar. Add the three new Fields in this order: Start Date, End Date and EventID Number. The Filter screen looks like this now:
    image 
    Still the user of the Report isn’t prompted for any input. Let’s change it.

  7. Right click on the Field Start Date and select the option Prompt. Now you’ll see a question mark in front of the field.
    image
    Repeat this step for the other two remaining Fields. Now the screen looks like this:
    image

  8. Lets make it the user of the Report a bit easier by defining a default Start Date (one week back on the day the Report is run) and a default End Date (the day the Report is run). Click on the arrow button next to Start Date. A calendar is shown. Click on Relative Date.
    image
    Select the option (n) days ago.
    image
    Replace the whole string (n) with the number 8. (I know. But SQL starts a date at 0:00 AM which is basically a new day. So when you want to see all data from the past 7 days, you must add an additional day. So it’s 8 instead of 7). Now the screen looks like this:
    image

    Repeat the same for the Field End Date. Instead of (n) days you select today. The screen looks like this now:
    image

  9. Still we need to do some more work. The conditions have to be changed for both dates. For the Field Start Date the condition must be set to after. For the Field End Date the condition must be set to before. Just click on equal and adjust it as required. The screen looks like this now:
    image

  10. There is one more thing to look at. As long we don’t fill out the Field EventID Number, a NULL value will be accepted as well. Which will grey out this Field in the Report. One can enable it by placing a checkmark in a small checkbox, but perhaps it’s better to add a basic value, like EventID 6022. This EventID is collected by default, so one will always get a filled Report and the user is allowed to change that value any time. Now the screen looks like this:
    image

  11. Click on OK to close the Filter screen. Now it’s time to add a title, like Custom EventID Report, counted. Run the Report and see what it does:
    image
    Nice! We have the Fields which prompt for input from the user, the Report itself counts the EventIDs per server AND per Event the details are shown!

  12. We can go back now to the Designer and add a picture (Insert > Picture) as a header. Like this:
    image
    Try it yourself. Colors can be added as well. Just play with it and be surprised!

  13. Now it’s time to save the Report. First it’s better to create a folder like ‘_Customized Reports’ in SSRS. In order to do this open the url to your Report Server (like http://localhost/reports) and create a new folder as you like. And yes, you can nest folders as well (don’t nest it too much though);
    image

  14. Now go back to Ad Hoc Report Builder and save the Report. The folder you created earlier is present as well;
    image
    Click Save. The Report is available now in the SCOM R2 Console;

  15. A refresh of the Reporting tree in the SCOM R2 Console is required in order to show the new folder (_Customized Reports).
    image 
    Run the Report by double clicking on it:
    image

  16. Click on the parameter area button and now you’ll see the filter you defined earlier on:
    image
    Nice!

Conclusion
Even though Ad Hoc Report Builder is limited in its capabilities, it still can do some nice things for you. Just start experimenting and be surprised! This small series of blog postings showed some possibilities. I am sure there are many more. Explore and have fun.

BUT never forget that a Report is only good when it serves a good purpose. So before you start with asking questions in your organization what they want, be sure you can deliver AND that Ad Hoc Report Builder is up to the job. Otherwise you’ll soon find the boundaries of it and then it’s time to start SQL Server Business Intelligence Development Studio, which is a total different kind of ballgame all together...

Friday, August 19, 2011

Storing Overrides: The Good, The Bad and The Ugly.

It might seem like flogging a dead horse, but sometimes I bump into situations which really puzzle me. Hence this posting in order to shed some light on this topic which seems to confuse many SCOM users.

What am I talking about?
I am talking about storing overrides. As we all know, every sealed MP requires some or more modifications. These modifications are also named Overrides. These Overrides need to be saved in a MP. Since the MP where the override is meant for, is sealed the Override can’t be saved in that MP. So an unsealed MP is required.

So far so good
Up to this point people agree with each other. But now the difference in views starts to kick in. Because now this question has to be answered: WHAT unsealed MP does one use for it? And actually there is only one GOOD answer possible here. But let’s take a look at the answers people can give and what the effects are on the SCOM R2 environment, or better the ‘modus operandi’.

The Bad
Seriously, this is BAD. Let’s assume we have three sealed MPs in place: Server OS, SQL and IIS. All related guides have been read (and understood!). Based on those guides and the requirements of the organization, the MPs have been modified. The resulting Overrides are saved into a single unsealed MP.

Since we’re good and law abiding citizens we DID’NT use the Default MP, but made a new unsealed MP named ‘Modifications Company X’. Let’s pour it into a picture so we’re all thinking the same here:
image

We really feel good about our selves. Why? Well we made a new unsealed MP AND we didn’t use the Default MP. Wow! We ROCK! Or don’t we?

Let’s take it a step further in order to see what will happen when the SQL MP gets an update. The guide tells it all and boy, we WANT that new version of the SQL MP. But the newest version of the SQL MP doesn’t allow an upgrade of any previous version of the SQL MP. No. The previous version must be removed first.

But now some alarm bells start to ring. When creating an override and storing it in an unsealed MP, a dependency is created as well. So in this case the SQL MP has become dependent of the unsealed MP  ‘Modifications Company X’. As a result the SQL MP can only be removed AFTER that unsealed MP, containing the overrides for SQL, is removed first.

And now we’re in trouble. Yes, we can remove the ‘Modifications Company X’ MP. But doing so will also remove the overrides made for the Server OS and IIS MP. And that’s not what we want. No way! Yes, we can remove the MP temporarily and import it back in after the old SQL is removed and the new SQL MP imported. But since the new SQL MP is totally different compared to the old version, the old overrides set for SQL aren’t valid anymore. This can cause some strange side effects in your SCOM environment.

So basically this situation is just as bad as putting ALL overrides in the Default MP. So simply don’t do that.

I see similar situations where the unsealed MPs containing the overrides have names like Server X or Server Y. So unsealed MP ‘Server X’ contains overrides for the Server OS MP, SQL MP and IIS MP targeted against Server X. This is equally bad or even worse since you’re creating TONS of dependencies! So simply don’t do that either.

Let’s take a look at another scenario I sometimes bump into. Isn’t good either.

The Ugly
OK. We learned our lesson well. We know about the bad situation and we don’t want to go there.

Let’s assume we have the same three sealed MPs in place: Server OS, SQL and IIS. All related guides have been read (and understood!). Based on those guides and the requirements of the organization, the MPs have been modified. The resulting Overrides are saved into a multiple unsealed MPs.

But while we were at it, we didn’t stop. So the SQL MP doesn’t have one unsealed MP containing all the SQL related overrides. No. Because we made many overrides: for SQL Server 2000 but also SQL Server 2005 and let’s not forget SQL Server 2008, SQL Server 2008 R2 and generic SQL overrides.

We really want to differentiate between those overrides so we created five unsealed MPs only for the SQL MP: ‘Overrides SQL 2000, ‘Overrides SQL 2005, ‘Overrides SQL 2008’, ‘Overrides SQL 2008 R2and ‘Overrides SQL’…

Boy! Are we smart or what?

Let’s pour it into a picture so we’re all thinking the same here:
image

Let’s take it a step further in order to see what will happen when the SQL MP gets an update. But the newest version of the SQL MP doesn’t allow an upgrade of any previous version of the SQL MP. The previous version must be removed first.

Most sealed MPs come in different components. So the SQL MP for monitoring SQL 2000, 2005 and 2008 consists out of 7 components. With the 5 unsealed MPs – for the overrides - we added our selves, there are 12 component MPs in place only for monitoring SQL. The more the merrier! Or not? Soon with all the references we created (per unsealed MP a reference is created) we find ourselves in a world of spaghetti.

Of course, so many MPs create a burden on the total amount of required administration of SCOM. And is prone to error as well. One could easily remove one MP to many with some unneeded results. So this is a bad situation and one to stay away from as well.

Let’s move on to the scenario which is best and should be followed to the letter.

The Good
Phew! We’ve come a long way. Covered many miles and learned our lessons.

Let’s assume we have the same three sealed MPs in place: Server OS, SQL and IIS. All related guides have been read (and understood!). Based on those guides and the requirements of the organization, the MPs have been modified. The resulting Overrides are saved into a per MP single dedicated unsealed MPs.

This  sounds good doesn’t it? Let’s visualize it so we’re on the same line:
image

Let’s take it a step further in order to see what will happen when the SQL MP gets an update. But the newest version of the SQL MP doesn’t allow an upgrade of any previous version of the SQL MP. The previous version must be removed first.

We stored all SQL MP related overrides into a single SQL dedicated unsealed MP, ‘Overrides SQL’. Whether it was an override for a Rule targeted against SQL 2000 or a Monitor targeted against SQL Server 2008 R2, we used the same single SQL dedicated unsealed MP.

So when we want to remove the SQL MP we must first remove the single unsealed MP containing the SQL related overrides. This is just one MP and only touches the SQL MP and nothing more.

Now we’re in the clear! Nice! No unwanted side effects or tons of dependencies.

Conclusion
When storing Overrides, store them in a single unsealed MP which is dedicated only to the MP where you’re making the override for. So overrides for the SQL MP go into the unsealed MP ‘Overrides SQL’ and overrides for the Server OS MP go in to the unsealed MP ‘Overrides Server OS’. This is the only viable and workable option. All other options cause issues, sooner or later.

All You Ever Wanted To Know About The Exchange 2010 MP But Were Afraid To Ask…

The Exchange MP is a good example about how the MPs evolved.

Exchange 2003 MP
I won’t go into much detail, but when this MP was imported, Alerts started pouring in very quickly. So it was all hands on deck in order to get the MP functional at a reasonable and acceptable level.

Exchange 2007 MP
This MP is a huge improvement since the Monitoring needs to be switched on, step by step. So here one can introduce this MP in a phased approach. Also the Discovery Helper is a really good aid in this MP. I am still positively impressed by this MP.

Exchange 2010 MP
This MP introduced the Correlation Engine (CE). This component takes care that only the relevant Alerts come in. When a certain component of Exchange 2010 stops working many many Alerts can be generated. The CE looks at all those Alerts and decides what’s the real cause is. Those Alerts will be send out and the others won’t.

However, the CE is a component which is new in SCOM and only used by the Exchange 2010 MP. Which makes this MP special and a bit rare as well. So the MP can be a challenge to configure. Therefore Microsoft has published KB2592561 all about Guidance, Tuning and Known Issues for the Exchange 2010 MP.

This KB contains really good information. So when you have this MP in place, read the KB. It really helps.

And of course, the MP guide of the Exchange 2010 MP itself contains good information as well. So RTFM is at play here :).

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!