Search This Blog

Thursday, December 27, 2012

Setup a Disk Report in SCOM 2012 (Part-3)

Finally got around to getting all the screen shots for the updated post for creating a more visually useful report. Now I don't have to make a new years resolution to get it done.

Recall in Part-2 of Setting up a Logical Disk Report for SCOM 2012 that a new view was created to make life a little easier. It was named “vCustomHourlyLogicalDiskPerf”. We will continue to use this view in the design of our updated, color-coded report. I was going to switch over to Visual Studio for this update, but decided to stick with Report Builder 3.0, which is a tool that comes with SQL or can be downloaded for free.

I will be more to the point with this post since I have covered other topics in parts one and two. Please refer back to those if you get stuck here. I presume you have already created the SQL view, which this post will utilize and was created earlier in Part-2.

Open Microsoft Report Builder 3.0 and connect to your SQL server housing the Operations Manager data warehouse. Start with a blank report project.

In the blank report, go ahead and resize out to about 8 inches for width.

 
 

Let’s add a quick title. For this, we’ll call it “Less than 10% Free Logical Disk Report”







Now let’s add the datasource:



Select the “Use a connection embedded in my report” and enter the connection string to DataWarehouseMain







data source=<SQL DW Server>;initial catalog=OperationsManagerDW;Integrated Security=SSPI

For the time being, change the credentials to “Use Current Windows User”. This will be changed after the report is complete, but use this for now in order to connect to the database and finish the report.

Now let us add a dataset:


We are going to do a slightly different dataset here than the one used in Part-2 of the series. For the purposes of this report, I want it to give me the status of disk space as of the last day. Since we’re trying to keep this simple and hack the code, we can run into accuracy issues with the construction of this report if more than one day of data is used. Additionally, I use this report each Friday so I know the disks that are currently having space issues so I can remediate the problems prior to the weekend and hopefully, avoid an outtage or after-hours call.

Here is the select statement for this dataset. Notice, I am hard coding the dates here. No variables will have to be used when this report runs. This makes it fast to use and easy to schedule. Notice some of the exclusions in the last section of the query. I am specifically removing the “total” metric, which can add redundant data. I also remove volume links that may be reported by services such as clustering or exchange, but effectively have the same data as the logical disk link. I could also exclude servers here that I know I don’t care about but that might have monitoring data, such as a backup server, video server, etc.

DECLARE @Start_Date DATETIME
DECLARE @End_Date DATETIME
SET @Start_Date = DateAdd(d,-1,GETDATE())
SET @End_Date = GETDATE()


select
[Total Disk Space],
[Free Megabytes],
[% Free Space],
InstanceName,
Path,
DateTime


from
(select CounterName, AverageValue,InstanceName, Path,DateTime
 FROM vCustomHourlyLogicalDiskPerf) AS SourceTable
PIVOT
(
AVG (AverageValue) FOR CounterName IN ([Total Disk Space],[Free Megabytes],[% Free Space])
) AS PivotTable

WHERE
DateTime >= @Start_Date AND
DateTime <= @End_Date AND
NOT InstanceName = '_Total' AND
NOT InstanceName Like '\\?\Volume%%' AND
[% Free Space] <= 10.00






 

 

With the query complete, let’s get the report up and running. This report will include a status bar to visually show the disk space left as well as color coding to show really critical space issues in red and warnings in orange.

Start by inserting a matrix into the report fields. Just drag and drop the matrix onto the report itself and then reposition to the upper-left, just under the title







Grab the “Path” variable from the dataset and drag to the lower left corner of the matrix:



Next, add a child group to the “Row Groups”:


Add the “Instance Name to the row child group”




On the third, remaining column, select the top of the column and right-click to add another column within the group. This needs to be repeated four times.

 Should now have something that looks like this:


We’ll need to split the top header. Right click on the top header to bring up the context menu and select “Split Cells”




Now, in this order, drag and drop the total disk space, free disk space and % free metrics into the bottom of each column. Leave a space between “free disk space” Should look something like the following:


This isn’t very handy, since we don’t want the sum of these metrics. So we’ll want to adjust each field, starting with “Total Disk”, right click on the field and select the “expression” option.


Let’s change this to the actual value along with converting it into Gigabytes, from the default Megabytes.


Now do the same with free space.


For the next field, which is blank, we’ll call it used disk space Used (GB). Here, we’ll use a calculated field. We could have done this in the SQL query itself, but I chose to do this differently. The expression here should include the total disk space minus the free disk space.

=(Fields!Total_Disk_Space.Value-Fields!ID__Free_Space.Value)/1000



Then finally on to percent free space.


For this field, we are going to add a color coding mechanism.  Right click on the ID_Free_space and select the “Text Box Properties”


Navigate to Font, and select the expression applet next to color.


Add the following expression in the "Color" field


=IIF(Fields!ID__Free_Space.Value >= 10, "Green", IIF(Fields!ID__Free_Space.Value >= 5, "Orange", "Red"))

What this is saying is, any field greater than 10% free space will show up as green, otherwise, anything between 10 and 5 will turn orange and anything under 5 will turn red. If this report had not been limited already to items under 10%, it would have made the corresponding report much easier to figure out which items were critical.


Under the “Insert” menu item in the report builder, select “Gauge” and then select the lower-right, empty column, to insert the visual gauge for free space.




We’ll choose the “Bullet Graph” under linear gauges.


Double-click on the gauge and for the “LinearPointer1” values, select the “ID_Free_Space” field and then delete “LinearPointer2”. The gauge will also want to sum up the values. Go back to the expression and remove the SUM option.


For the gauge, we’ll want similar fill options to our numerical values. Select the small line in the gauge, then right-click and select the “Pointer Properties”.


Select the “Pointer Fill” option and adjust the expression for the “Color” and “Secondary Color” with the following expression.

=IIF(Fields!ID__Free_Space.Value >= 10, "Green", IIF(Fields!ID__Free_Space.Value >= 5, "Orange", "Red"))


Adjust the column widths and get things all squared up. Now your report should look something like this:


Now go back to the properties of DataWarehouseMain and change the credentials back to “Do not use Credentials”


Now save the report to the SCOM reporting server.


The report is almost ready to be completed and viewed. Login to the web interface of the report server.

Usually with the syntax of
http://<report server>/Reports.

Select the "Details View" and look for the DataWarehouseMain object and select it. Go into the object properties and copy the selection string. Now find the report you just created, and select the manage option in the drop down menu.


Go to the “Data Sources” tab for the report and delete the existing connection string and replace with the one copied from DataWarehouseMain. Also ensure that “Credentials are not required” is selected. Apply the changes.

You should now be able to run the report. Depending on the cleanup you did in the report format, you may need to go back and make some changes. In my example, I did not adjust the numerical formatting for each text field and ended up with too many decimals in the fields. So long as you are only changing text field properties, you should be able to save the report without altering any connection string settings.


To correct this, open a text box property, select the “Number” tab and change the format to “Number” with 2 decimal places.





Now the output looks like this, which is much better:



Good luck and happy report writing. There is a little bit of everything in this post and hopefully this helps get you started with additional techniques.

12 comments:

  1. Hi Dave,

    Would it be possible to change the sql code to work out the percentage increase if the value was taken at 12pm the day before and compare to the value 12pm today?

    ReplyDelete
  2. Dave - it would a temendous help to the community provide the report for downlaod.

    Thoughts?

    ReplyDelete
    Replies
    1. I have thought about it. My internal conflict is that if I publish the file, a lot of folks will think it works out of the box without understanding the background logic going on and that there are dependencies driving the report that have to be setup.

      Delete
    2. I agree with Dave. To teach to fish, is better than just giving away a fish.

      Delete
  3. Why is your Total (GB) and Used (GB) the same number? I have an issue where for some I'm seeing a Total (GB) of 0.00 and where it does work my Users is like yours the same as my total?

    ReplyDelete
    Replies
    1. Dave, I'm interested to hear if you think the way I made it work has any side impact. I changed the DataSet query from GETDATE() to GETUTCDATE() because I believe that is how Operations Manager stores the information. I also did the total - fee space in the SQL query instead of in the expression. Lastly, the final piece that made the largest difference is I added [Total Disk Space] IS NOT NULL. It seemed some of the values were NULL and that is why I would get 0.00 but there was always one filled out so this allowed Total Space to never be 0.00

      I'm really interested in why I ran into these issues if you didn't.

      Delete
    2. Correct, the date is stored in UTC time and is something I've played around with in other reports. I have encountered issues with the NULL values before, typically when there are issues with WMI or system performance at either the SCOM or individual host level. At a basic level, that NULL value can mean SCOM wasn't able to connect and extract the necessary value for that particular time slice, often due to performance issues, but could be WMI isn't doing well or the system is overloaded, leading to a blank or NULL field. That was one of the reasons I settled in initially on the pivot function, because it averages out several time slices in a short period to come up with the value, so if one or two are missing, it shouldn't affect the outcome too much. Consider that if you filter on IS NOT NULL, you may be passing up an opportunity to find machines that aren't reporting health state consistently and that may require additional tuning or are having performance problems. But yes, I have encountered issues with NULL values.

      Delete
    3. Hopefully that makes sense. The difference in experience you're having, versus what I appear to have is that I've had to perform a lot of wmi troubleshooting on the back end. It took a good week or so to iron out all those issues. That is where the wmi posts came from that I did earlier on. Many of the WMI classes get locked down by .NET or some other update and have to be opened back up and there are about half a dozen WMI memory leak hotfixes as well, which I just posted an article about in troubleshooting gray agents, which is another reason data points could be missing.

      Delete
  4. Dave,

    First of all thank you for all these three articles. It was great and easy to follow. I was just wondering if you had any problems with Win 2008 and Win 2012 counters reporting total disk space. Mine works for Win 2003 and reports disk size but when it comes to OS higher than 2003, they are all 0.00 GB.

    ReplyDelete
    Replies
    1. Haven't had issue yet, beyond one or two servers that throw a fit now and then. Seems a bit odd that all your 2008 and 2012 servers are missing. Double check the WMI query to ensure you're able to pull those stats from one or two of those servers. I typically use scriptomatic 2.0 to perform those queries and check on the health of WMI.

      Delete
    2. Also, I do consulting work. Would be more than happy to dial in and assist.

      Delete
  5. thanks a lot for the excellent report dave, could you please help us to configure memory/CPU utilization report. thanks a ton in advance

    Cheers

    ReplyDelete