Many of you asks if we can get a SQL query where you will just run a SQL query to pull the CPU and Memory data in a single query.

 

I have made a SQL Script which is available here:

 

https://github.com/souravmahato7/SQL/blob/SCOM/SCOM_CPU_Memory_Data.sql

 

This query will get the CPU and Memory result in a single page.

 


USE OperationsManagerDW
declare @startdate datetime
declare @enddate datetime

set @startdate = '20190816'
set @enddate = '20190917';

DECLARE @CPU TABLE(Path varchar(255),[DateTime] datetime, ObjectName varchar(255), CounterName varchar(255), Averagevalue int,MinValue int,MaxValue int)
DECLARE @Memory TABLE(Path varchar(255),[DateTime] datetime, ObjectName varchar(255), CounterName varchar(255), Averagevalue int,MinValue int,MaxValue int)
Declare @aggregationTypeid as int

Set @aggregationTypeid =30 --if you need for hourly, then change the value as 20

IF @AggregationTypeId = 30

BEGIN

INSERT INTO @CPU
select
vManagedEntity.Path
,Perf.vPerfDaily.DateTime
--,Perf.vPerfDaily.SampleCount
,vPerformanceRule.ObjectName
,vPerformanceRule.CounterName
,round(Perf.vPerfDaily.Averagevalue,3) As CPUAvgValue
,round(Perf.vPerfDaily.MinValue,3) as CPUMinValue
,round(Perf.vPerfDaily.MaxValue,3) as CPUMaxValue
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 @startdate and @enddate
and vPerformanceRule.ObjectName='Processor Information'
and vPerformanceRule.CounterName='% processor time'

INSERT INTO @Memory
select
vManagedEntity.Path
,Perf.vPerfDaily.DateTime
,vPerformanceRule.ObjectName
,vPerformanceRule.CounterName
,round(Perf.vPerfDaily.Averagevalue,3) As MemoryAvgValue
,round(Perf.vPerfDaily.MinValue,3) as MemoryMinValue
,round(Perf.vPerfDaily.MaxValue,3) as MemoryMaxValue
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 @startdate and @enddate
and vPerformanceRule.ObjectName='Memory'
and vPerformanceRule.CounterName='PercentMemoryUsed'

SELECT
C.Path [Server Name]
,Min(C.DateTime) [Start Date]
,Max(C.DateTime) [End Date]
,cast(Min(C.MinValue) as int) [Min_CPU]
,cast(max(C.MaxValue) as int) [Max_CPU]
,cast(avg(C.Averagevalue) as int) [Avg_CPU]
,cast(Min(M.MinValue) as int) [Min_Memory]
,cast(max(M.MaxValue) as int) [Max_Memory]
,cast(avg(M.Averagevalue) as int) [Avg_Memory]

FROM
@CPU C
INNER JOIN @Memory M
ON C.DateTime = M.DateTime
and C.Path = M.Path
--where C.Path in ('Servernamewithfqdn')
group by C.Path
order by C.Path

END

ELSE

BEGIN

INSERT INTO @CPU
select
vManagedEntity.Path
,Perf.vPerfHourly.DateTime
,vPerformanceRule.ObjectName
,vPerformanceRule.CounterName
,round(Perf.vPerfHourly.Averagevalue,3) As CPUAvgValue
,round(Perf.vPerfHourly.MinValue,3) as CPUMinValue
,round(Perf.vPerfHourly.MaxValue,3) as CPUMaxValue
from Perf.vPerfHourly
join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vPerfHourly.PerformanceRuleInstanceRowid
join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vPerfHourly.ManagedEntityRowId
join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
where Perf.vPerfHourly.Datetime between @startdate and @enddate
and vPerformanceRule.ObjectName='Processor Information'
and vPerformanceRule.CounterName='% processor time'

INSERT INTO @Memory
select
vManagedEntity.Path
,Perf.vPerfHourly.DateTime
,vPerformanceRule.ObjectName
,vPerformanceRule.CounterName
,round(Perf.vPerfHourly.Averagevalue,3) As MemoryAvgValue
,round(Perf.vPerfHourly.MinValue,3) as MemoryMinValue
,round(Perf.vPerfHourly.MaxValue,3) as MemoryMaxValue
from Perf.vPerfHourly
join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vPerfHourly.PerformanceRuleInstanceRowid
join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vPerfHourly.ManagedEntityRowId
join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
where Perf.vPerfHourly.Datetime between @startdate and @enddate
and vPerformanceRule.ObjectName='Memory'
and vPerformanceRule.CounterName='PercentMemoryUsed'

SELECT
C.Path [Server Name]
,Min(C.DateTime) [Start Date]
,Max(C.DateTime) [End Date]
,cast(Min(C.MinValue) as int) [Min_CPU]
,cast(max(C.MaxValue) as int) [Max_CPU]
,cast(avg(C.Averagevalue) as int) [Avg_CPU]
,cast(Min(M.MinValue) as int) [Min_Memory]
,cast(max(M.MaxValue) as int) [Max_Memory]
,cast(avg(M.Averagevalue) as int) [Avg_Memory]

FROM @CPU C

INNER JOIN @Memory M
ON C.DateTime = M.DateTime
and C.Path = M.Path
--where C.Path in ('Servernamewithfqdn')
group by C.Path
order by C.Path
END

 

Leave a Reply

Your email address will not be published. Required fields are marked *