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
SELECT
vPerf.DateTime,
vPerf.SampleCount,
vPerf.AverageValue,
vPerf.MinValue,
vPerf.MaxValue,
vPerf.StandardDeviation,
vPerformanceRuleInstance.RuleRowId,
vPerformanceRuleInstance.InstanceName,
vManagedEntity.ManagedEntityRowId,
vManagedEntity.ManagedEntityGuid,
vManagedEntity.ManagedEntityDefaultName,
vManagedEntity.Path,
ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)
AS DisplayName,
vManagementGroup.ManagementGroupGuid,
vManagementGroup.ManagementGroupDefaultName,
vRule.RuleGuid,
ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS
RuleDisplayName,
vPerformanceRule.MultiInstanceInd,
OptionList.[Group],
OptionList.GroupTitle,
OptionList.Position,
OptionList.ChartScale,
OptionList.ChartType,
OptionList.ChartColor,
OptionList.OptionXml,
vManagedEntityTypeImage.Image
FROM Perf.vPerfDaily as
vPerf
INNER JOIN
vPerformanceRuleInstance
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId =
vPerf.PerformanceRuleInstanceRowId
INNER JOIN vManagedEntity
ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN
vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId =
vManagedEntity.ManagedEntityTypeRowId
INNER JOIN
vManagementGroup
ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId
INNER JOIN vRule
ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
INNER JOIN
vPerformanceRule
ON vPerformanceRuleInstance.RuleRowId =
vPerformanceRule.RuleRowId
INNER JOIN #InstanceList
AS InstanceList
ON (vPerformanceRuleInstance.PerformanceRuleInstanceRowId =
InstanceList.PerformanceRuleInstanceRowId)
INNER JOIN #OptionList As
OptionList
ON (InstanceList.Position = OptionList.Position)
INNER JOIN #ObjectList AS
ObjectList
ON (InstanceList.Position = ObjectList.Position)
AND (vManagedEntity.ManagedEntityRowId =
ObjectList.ManagedEntityRowId)
LEFT OUTER JOIN
vManagedEntityTypeImage
ON vManagedEntity.ManagedEntityTypeRowId =
vManagedEntityTypeImage.ManagedEntityTypeRowId
AND vManagedEntityTypeImage.ImageCategory
= N'u16x16Icon'
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
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)))
To add an image to the report