When you monitor a set of servers using MOM, the Operator Console focus you on doing: having the alerts solved and the cattle happy and healthy.

But one day comes when you want to know more that do.

Do not you try to extract knowledge from the Operator Console as it hopefully only keeps some days of data, normally too few to extract relevant conclusions.

Then there’s the Reporting Console. You’d better match your need to know with the predefined reports, because it does not have a report creation tool as some may expect.

You can, of course, create a Reporting Solution on Visual Studio 2005/2008 and then integrate it into the Reporting Console (see here for a good example).

But once you have to dive into the MOM database, don’t you want to take a deep breath and get to the bottom of it?

Don’t you want to query MOM?

This may help the hero in you.

I could not find any documentation on the databases structure, so I started to look around.

I used SQL Query Analyzer for the task.

First there’s the databases: “OnePoint” is the one that gets projected to the Operator Console. Data stays there for a while, then it’s groomed: it flies to “SystemCenterReporting” database.

Then for the tables, I’ve been playing with a couple of them: “SC_AlertFact_Table” and “SC_AlertHistoryFact_Table”, in there you’ll find these comments you enter when you resolve an issue.

This is, in fact, the reason why I had to walk this path.

You know that when you set the resolution state to “Resolved” you are prompt for “optional comments you would like to append to each Alert’s history”.

But, is this the right place to store your comments in order to make knowledge persistent? Apparently not.

So, now what? How do I access all this information I entered?

I created this query to get a first approach. And at least you will be happy that all your comments are still there.

select b.AlertDescription, a.Comments from SC_AlertHistoryFact_Table a join SC_AlertFact_Table b on a.AlertID = b.AlertID_PK where a.UserOwner_FK = 17 and b.DateTimeAdded > ‘9/1/2007’;

Notice the fields for the join: «a.AlertID» and «b.AlertID_PK«.

_PK means Primary Key.
And _FK Foreign Key, I guess…

Then you may want to move them all to the proposed field under the Company Knowledge section.

But this will be another tale.