File Usage Monitoring: Custom Report

Before I start this post I would like to thank Kevin Holman, I used his blog post about creating custom reports as my roadmap for creating these reports.  Kevin’s Post is available at http://blogs.technet.com/kevinholman/archive/2008/09/03/using-opsmgr-to-see-which-servers-have-not-been-logged-on-to-via-rdp.aspx

The first step I took is to think about what I wanted the report to look like.  I really wanted it to closely resemble what I created in my alert view in the console.

image 

The next step is to create a SQL query that will retrieve the all of the information we need – all Custom Field 1 and DateAccess for each of the alert rules.  The first step is to reference Kevin’s Blog post about retrieving information from the Data Warehouse with SQL queries http://blogs.technet.com/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx.  The section related to what we are doing is the Data Warehouse Database Section: Alerts Section.  I started off by running the following query against the Data Warehouse

To get all raw alert data from the data warehouse to build reports from:

select top 1 * from Alert.vAlertResolutionState ars
inner join Alert.vAlertDetail adt on ars.alertguid = adt.alertguid
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid

image

image

image

The Actual query is below.  The idea is to get the 3 fields we want and only from the databases (based on alert name which we created before).  So now after I get the report setup, if I ever need to add more files to the report all I have to do is add their alert name to the where cause.

select AlertName, adt.DWCreatedDateTime, CustomField1 from Alert.vAlertResolutionState ars
inner join Alert.vAlertDetail adt on ars.alertguid = adt.alertguid
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid
where (AlertName like ‘market_q1f10%’ or AlertName like ‘sales_Q1F10%’) and StateSetByUserId like ‘System’

Now that the query is setup we want to create a new report using the Visual Studio report builder.

image

image

image

image

image

image 

image

image

image

image

image

image

image

The next step is to set up the report so that we can specify date ranges

image

image

We can now take the .RDL file from this project and import it into the Report Server for SCOM

image

image

Connect to your SCOM reporting server and go to http://localhost/reports (alternatively you can go http://reportingservername/reports from any computer)

image

image

image

image

image

image

image

image

image

image

Now the Report shows up in the SCOM Console!

image

This entry was posted in Management Pack Authoring, Reporting How-To and tagged , . Bookmark the permalink.

One Response to File Usage Monitoring: Custom Report

  1. Glenn Cozine says:

    Ryan this looks great and I plan to try it out tomorrow.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s