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

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s