Knowledge Store: The Performance Report

The Performance Report

The Performance report is similar to the Availability report in that it retrieves data for a specified time period on specific entities and retrieves the information from various views. The purpose of the Performance report is to report on the change of a value over time.

The query retrieves some basic information from the vPerfDaily view, which, in this query, has the alias of vPerf:

SELECT

vPerf.DateTime,

vPerf.SampleCount,

vPerf.AverageValue,

vPerf.MinValue,

vPerf.MaxValue,

vPerf.StandardDeviation,

The query also retrieves the performance rules located in the vPerformanceRuleInstance view:

vPerformanceRuleInstance.RuleRowId,

vPerformanceRuleInstance.InstanceName,

As in the other queries, data is retrieved from the vManagedEntity view, in particular the ManagedEntityRowId, although the GUID, default name, and path are also retrieved:

vManagedEntity.ManagedEntityRowId,

vManagedEntity.ManagedEntityGuid,

vManagedEntity.ManagedEntityDefaultName,

vManagedEntity.Path,

As in the Availability query, the query retrieves a display name if it exists:

ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)

AS DisplayName,

To display management group information, the query accesses the vManagementGroup view:

vManagementGroup.ManagementGroupGuid,

vManagementGroup.ManagementGroupDefaultName,

The query also accesses the vRule and vPerformanceRule views:

vRule.RuleGuid,

ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName,

vPerformanceRule.MultiInstanceInd,

The query also retrieves group information from the temporary table #OptionList:

OptionList.[Group],

OptionList.GroupTitle,

OptionList.Position,

OptionList.ChartScale,

OptionList.ChartType,

OptionList.ChartColor,

OptionList.OptionXml,

vManagedEntityTypeImage.Image

The FROM clause in the Performance query is more complex than in the Alert or Availability queries. It begins with the information retrieved from vPerfDaily:

FROM Perf.vPerfDaily as vPerf

The query joins that to the performance rule view, using PerformanceRuleInstanceRowId as the foreign key:

INNER JOIN vPerformanceRuleInstance

ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId

The query then joins that to the vManagedEntity view, again using ManagedEntityRowId as the key:

INNER JOIN vManagedEntity

ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRow

Once joined to the vManagedEntity view, the query joins to the vManagedEntityType view to retrieve the type for the managed entity:

INNER JOIN vManagedEntityType

ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId

The query also joins to the vManagementGroup view using the ManagementGroupRowId as the key:

INNER JOIN vManagementGroup

ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId

To retrieve performance information, the query needs to join the vRule and vPerformanceRule views to the vPerformanceRuleInstance view using the RuleRowId as the key in each case.

INNER JOIN vRule

ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId

INNER JOIN vPerformanceRule

ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId

The query needs to join to the #InstanceList:

INNER JOIN #InstanceList AS InstanceList

ON (vPerformanceRuleInstance.PerformanceRuleInstanceRowId = InstanceList.PerformanceRuleInstanceRowId)

The query needs to join to the #OptionList:

INNER JOIN #OptionList As OptionList

ON (InstanceList.Position = OptionList.Position)

Finally, the query joins the ObjectList to the InstanceList and to the vManagedEntity view using the ManagedEntityRowId as the key:

INNER JOIN #ObjectList AS ObjectList

ON (InstanceList.Position = ObjectList.Position)

AND (vManagedEntity.ManagedEntityRowId = ObjectList.ManagedEntityRowId)

The query then retrieves the image to use for the entity type:

LEFT OUTER JOIN vManagedEntityTypeImage

ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId

AND vManagedEntityTypeImage.ImageCategory = N'u16x16Icon'

Finally, the query joins the vDisplayString and vDisplayStringRule views to retrieve the necessary display strings for the report. The @LanguageCode is a parameter specified by the report designer.

LEFT OUTER JOIN vDisplayString

ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid

AND vDisplayString.LanguageCode = @LanguageCode

LEFT OUTER JOIN vDisplayString vDisplayStringRule

ON vRule.RuleGuid = vDisplayStringRule.ElementGuid

AND vDisplayStringRule.LanguageCode = @LanguageCode

The WHERE clause is similar to the one in the Availability report. It retrieves all the data that falls between the two dates specified in the date picker:

WHERE (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112)))

AND (vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112)))

The important points to note in this query are that the ManagedEntityRowId is a useful foreign key, and the format of the WHERE clause is similar to that used in the Availability report.

Here’s what the complete query looks like