OpsMgr (SCOM) - Visual Studio Report (System Drive Available Space)

Challanged by a local PFE friend to start using Visual Studio to create my reports, and keeping in mind i'm no SQL expert, and no Report-Builder greatest fan, i tried to make a costumers request on specific report they wanted (C: Drive available Space)!

So, since i'm kind new to Visual Studio, and to this scenario in general, i decided to make this how to, and obviously, share it here.

So! Let's open Visual Studio and click NEW!

And then :



You'll have a blank report, so we need to add a datasource (from where you'll get your data from!)
(Since we're using OpsMgrDW database...)



Then, add a dataset (it's a query!) :)

This one i used!

 SELECT DISTINCT VME.Path AS Agente, DISKs.SystemDiskUsed AS FreePercentage, DISKs.SystemDiskFreeMB AS FreeMB  
 FROM Perf.vPerfDaily AS PERF   
 INNER JOIN vManagedEntity AS VME ON PERF.ManagedEntityRowId = VME.ManagedEntityRowId   
 INNER JOIN vPerformanceRuleInstance AS PRI ON PRI.PerformanceRuleInstanceRowId = PERF.PerformanceRuleInstanceRowId   
 INNER JOIN vPerformanceRule AS PR ON PR.RuleRowId = PRI.RuleRowId   
 LEFT OUTER JOIN  
 (  
     SELECT ME.Path AS Computer,  
     AVG(CASE WHEN PR.CounterName = '% Free Space' THEN PERF.AverageValue END) AS SystemDiskUsed,   
     AVG(CASE WHEN PR.CounterName = 'Free Megabytes' THEN PERF.AverageValue END) AS SystemDiskFreeMB  
     FROM Perf.vPerfDaily AS PERF   
     INNER JOIN vPerformanceRuleInstance AS PRI ON PRI.PerformanceRuleInstanceRowId = PERF.PerformanceRuleInstanceRowId  
     INNER JOIN vPerformanceRule AS PR ON PR.RuleRowId = PRI.RuleRowId  
     INNER JOIN vManagedEntity AS ME ON PERF.ManagedEntityRowId = ME.ManagedEntityRowId  
     WHERE (PERF.DateTime > DATEADD(Day, - 7, GETDATE()))   
     AND (PR.CounterName = '% Free Space'   
     OR PR.CounterName = 'Free Megabytes') AND (PRI.InstanceName = 'C:')  
     GROUP BY ME.Path, PRI.InstanceName  
 ) AS DISKs ON VME.Path = DISKs.Computer  
 WHERE (PERF.DateTime > GETUTCDATE() - 2)   
 AND (PR.CounterName LIKE '% Free Space')  
 AND (PRI.InstanceName LIKE 'C:')  
 ORDER BY FreeMB  

Then, add a matrix, with that dataset.



Add a logo you may want (Company logo perhaps!):

Change the expression value for the FreeMB (Just a tweak to look better!)

So, now, let's add some colour to it with a new row with a 'progress bar'.

First, add the row:

Then, insert the 'Data Bar'

For the 'progress bar' you added, change the values of its expression like : 

And the values of the series fill propreties, just to make it RED when is bellow a threshold you define.

As you did for the FreeMB field, do the same for the Free% :

Run the preview and you'll get :

And that's it! :)

Save the project, copy the RDL file into your Reporting Services server and schedule it!

In the future i'll make other posts like this, now that i'm a Visual Studio rookie :)

Cheers :)