By default – our generic reports access *aggregated* data in the warehouse… either daily or hourly. These contain information like number of data points, avg, min, max, and std. deviation data. However, sometimes users will want access to the RAW, individual data points.
This is kept in the warehouse – and you can see it in the Perf.vPerfRaw view in the DB. By default – we keep this data for 10 days, and in general – we do NOT recommend that you extend this to any longer retention.
I will add these to my mega-query list… HERE
Here are some core query examples/samples for RAW perf data – which you can use to write reports against this output:
Raw data – core query:
select top 10 * from Perf.vPerfRaw pvpr inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
More selective of “interesting” output data:
select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime from Perf.vPerfRaw pvpr inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
Scoped to a ComputerName (FQDN)
select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime from Perf.vPerfRaw pvpr inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId WHERE Path = 'OMDB.opsmgr.net'
Scoped to a Counter:
select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime from Perf.vPerfRaw pvpr inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId WHERE CounterName = 'Private Bytes'
Scoped to a Computer and Counter:
select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime from Perf.vPerfRaw pvpr inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId WHERE CounterName = 'Private Bytes' AND Path = 'OMDB.OPSMGR.NET'
Ordered By DateTime:
select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime from Perf.vPerfRaw pvpr inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId WHERE CounterName = 'Private Bytes' AND Path = 'OMDB.OPSMGR.NET' Order By DateTime DESC
Modified DateTime relative to Central Time zone:
select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, dateadd(hh,-5,DateTime) as 'DateTime (GMT-5)' from Perf.vPerfRaw pvpr inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId WHERE CounterName = 'Private Bytes' AND Path = 'OMDB.OPSMGR.NET' Order By DateTime DESC
How to get all the possible optional data to modify these queries above, in a list:
Select Distinct Path from Perf.vPerfRaw pvpr inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId Select Distinct Fullname from Perf.vPerfRaw pvpr inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId Select Distinct ObjectName from Perf.vPerfRaw pvpr inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId Select Distinct CounterName from Perf.vPerfRaw pvpr inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId Select Distinct InstanceName from Perf.vPerfRaw pvpr inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId