Pavleck.NET

I’ve added a suggestion on Connect, #436472, that I think would really assist with the creation of custom reports. In HP’s System Insight Manager, when you generate a report for something, like number of CPUs per server, at the very bottom of the report is a link that says “Show SQL query used to generate this report”.

A feature like that added to OpsMgr reporting would go a long long way to allowing us to generate custom reports.
Why?
Well, the SQL schema for OpsMgr is not, and never will be, given out. It’s like that because once they’ve shared a schema, then they are more or less stuck with it and it makes adding, removing, or making more efficient sql much more difficult. So they just don’t share it. They didn’t in MOM2005 either, but it was significantly easier to figure things out there, since we had the one MOM database, and all of the warehousing data was pulled from that one via DTS packages, so the whole thing functioned easier. With OpsMgr 2007, data is written to the OpsMgr database and the reporting database concurrently, and they are more less independent of each other. Because of that a lot of the tables and such are a lot more confusing and a lot more happens behind the scene. So adding something simple like “Show me the SQL used to generate this query” would open up a whole new world to us.

Let’s face it. For the vast majority of us, we’re either System Administrators that handle day to day duties as well as the OpsMgr infrastructure, or we’re full time OpsMgr Admins. We’re IT renaissance men, Jacks (and Jills) of all trades. We know a little bit about all of the applications, operating systems, hardware, etc of our company, but we’re not an expert in one thing. Since OpsMgr uses SQL Reporting Services to issue up reports, that means we need either:

• High level knowledge of SQL Reporting Services
• Full access to someone that is an expert of SRS
  or
• An easier, more guided method of generating reports

What I normally run across is:

1. I know SQL, I can bang together most of the things I need with the help of the SQL Books and Google. But SRS? No idea!
2. Every SQL DBA I know is either swamped with work, or they themselves don’t use SRS and have no idea how to help me
3. To say reporting is not very intuitive would be an under-statement

But, if we had a way of looking at the SQL queries a particular report uses to pull up it’s information, we’d be able to bang something together. Jonathon Hambrook showed us how to create the report model and get started. Kevin Holman shows us a different way, and also shows us a real world query to use.

While those are great starting points, I want more information. I want to create a report through the reporting tab – let’s say we want to look at the CPU usage over a 4 week period for all of the exchange servers. I then want to see exactly what SQL voodoo was run, and then re-create the report myself in the SQL BI suite, that way I can prepare something for a presentation, and make sure this report adheres to my companies style and branding guides. Just that one simple change would be the bridge that lets us go from “No idea” to “Hey, I have a great idea!”.

Hopefully I’ve got you fired up enough to rate my suggestion – if we get it high enough there’s a good chance it’ll be included! So open up suggestion #436472, and rate it! If you’ve never been to MS Connect before, login to the site and subscribe to the OpsMgr connection, #446. Then you’ll be able to click on the suggestion above to go directly to it. In the mean time, I’m trying to figure out how to pull up this info via ‘other means’.

Until next time!