SCOM CPU Memory Disk Util SQL Scripts

Below are the scripts are to find SCOM CPU Memory Disk Utilization. Scripts need to be run on the Data warehouse DB.

Adjust the date to suite your requirements. The rest of the script remains the same

CPU Utilization

select

vManagedEntity.Path

,Perf.vPerfdaily.DateTime

,Perf.vPerfdaily.SampleCount

,vPerformanceRule.ObjectName

,vPerformanceRule.CounterName

,vManagedEntity.Name

,Perf.vPerfdaily.Averagevalue

,Perf.vPerfdaily.MinValue

,Perf.vPerfdaily.MaxValue

,vRule.RuleDefaultName

from Perf.vPerfDaily

join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vPerfDaily.PerformanceRuleInstanceRowid

join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId

join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vPerfDaily.ManagedEntityRowId

join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId

where Perf.vPerfDaily.Datetime between '2012-02-08' and '2012-02-12'

and vPerformanceRule.ObjectName='Processor'

and vPerformanceRule.CounterName='% Processor Time'

and (vRule.RuleDefaultName='Processor % Processor Time Total 2003'

or vRule.RuleDefaultName='% Processor % Processor TIme Total 2008')

Order by Path,Name

Disk space in MB

select

vManagedEntity.Path

,Perf.vPerfdaily.DateTime

,Perf.vPerfdaily.SampleCount

,vPerformanceRule.ObjectName

,vPerformanceRule.CounterName

,vManagedEntity.Name

,Perf.vPerfdaily.Averagevalue

,Perf.vPerfdaily.MinValue

,Perf.vPerfdaily.MaxValue

,vRule.RuleDefaultName 

from Perf.vPerfDaily

join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vPerfDaily.PerformanceRuleInstanceRowid

join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vPerfDaily.ManagedEntityRowId
join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
where Perf.vPerfDaily.Datetime between '2012-03-06' and '2012-03-12'
and vPerformanceRule.ObjectName='LogicalDisk'
and vPerformanceRule.CounterName='Free Megabytes'
and (vRule.RuleDefaultName='Logical Disk Free Megabytes 2000'
or vRule.RuleDefaultName='Logical Disk Free Megabytes 2003'
or vRule.RuleDefaultName='Logical Disk Free Megabytes 2008')
Order by Path,Name
 

Free disk space in Percentage

select

vManagedEntity.Path
,Perf.vPerfdaily.DateTime
,Perf.vPerfdaily.SampleCount
,vPerformanceRule.ObjectName
,vPerformanceRule.CounterName
,vManagedEntity.Name
,Perf.vPerfdaily.Averagevalue
,Perf.vPerfdaily.MinValue
,Perf.vPerfdaily.MaxValue
,vRule.RuleDefaultName

from Perf.vPerfDaily

join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vPerfDaily.PerformanceRuleInstanceRowid

join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId

join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vPerfDaily.ManagedEntityRowId
join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
where Perf.vPerfDaily.Datetime between '2012-03-06' and '2012-03-12'
and vPerformanceRule.ObjectName='LogicalDisk'
and vPerformanceRule.CounterName='% Free Space'
and (vRule.RuleDefaultName='% Logical Disk Free space 2000'
or vRule.RuleDefaultName='% Logical Disk Free Space 2003'
or vRule.RuleDefaultName='% Logical Disk Free Space 2008')
Order by Path,Name

Memory Percentage used

select

vManagedEntity.Path
,Perf.vPerfdaily.DateTime
,Perf.vPerfdaily.SampleCount
,vPerformanceRule.ObjectName
,vPerformanceRule.CounterName
,vManagedEntity.Name
,Perf.vPerfdaily.Averagevalue
,Perf.vPerfdaily.MinValue
,Perf.vPerfdaily.MaxValue
,vRule.RuleDefaultName

from Perf.vPerfDaily

join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vPerfDaily.PerformanceRuleInstanceRowid
 
join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vPerfDaily.ManagedEntityRowId
join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
where Perf.vPerfDaily.Datetime between '2012-03-06' and '2012-03-12'
and vPerformanceRule.ObjectName='Memory'
and vPerformanceRule.CounterName='PercentMemoryUsed'
and (vRule.RuleDefaultName='Percent Memory Used')
Order by Path,Name