Inside a Generic Report

Inside a Generic Report

Before you start building your own queries, it may be helpful to see how the queries for the built-in reports work. The sections below look at the queries used by three existing reports from the Microsoft Generic Report Library: The Availability report, the Alerts report, and the Performance report.  For each report, the query is located in a stored procedure. The stored procedures are reproduced here so you can see the Transact-SQL code.

The Alert Report

The purpose of the Alert report is to present a simple list of alerts raised by the managed objects specified in the parameters of the report. The report in the Generic Library can list events of all severities (Information, Warning, and Critical) and all Priorities (Low, Medium, and High). For each alert, it provides the Alert Name, Repeat Count, Priority, the Object that raised the alert, and the first and last dates where the alert appeared.

This query deals with Alert data, so it uses the vAlert view. First, it retrieves the AlertName, Severity, and Priority, which are all basic information. It also retrieves the ManagedEntityRowId, which will be used as a foreign key later in the query:

SELECT

Alert.vAlert.AlertName,

Alert.vAlert.Severity,

Alert.vAlert.Priority,

Alert.vAlert.ManagedEntityRowId,

Each time the alert is raised it creates a new record. To get the first and last dates where the alert was raised, this query uses the MAX and MIN functions:

MAX(Alert.vAlert.RaisedDateTime) AS LastRaisedTime,

MIN(Alert.vAlert.RaisedDateTime) AS FirstRaisedTime,

The query also uses the MIN function to retrieve the AlertDescription. The description is just text, but using MIN guarantees that the query will retrieve only one entry for the given alert. This is required because the descriptions should all be identical:

MIN(Alert.vAlert.AlertDescription) AS AlertDescription,

To get the number of times an alert was repeated, the query uses the COUNT function. This simply counts the number of rows retrieved and indicates how many times the event was raised:

COUNT(*) AS RepeatCount,

Finally, the query retrieves the SiteName and the AlertProblemGuid. It does not use the AlertGuid because that row is unique for each individual Alert. Instead, it uses the AlertProblemGuid, which identifies the specific type of alert:

Alert.vAlert.SiteName,

Alert.vAlert.AlertProblemGuid

To define the FROM clause, the query retrieves all of the rows above from the vAlert view. It then  joins that information to several temporary tables. The #ObjectList table holds the objects passed in to the query from the report. The vAlert.Severity and vAlert.Priority fields are simply integers, which can be used with the #SeverityList.Severity field and the #PriorityList.Priority tables to return text strings:

FROM Alert.vAlert

INNER JOIN #ObjectList ON #ObjectList.ManagedEntityRowId = Alert.vAlert.ManagedEntityRowId

INNER JOIN #SeverityList ON #SeverityList.Severity = Alert.vAlert.Severity

INNER JOIN #PriorityList ON #PriorityList.Priority = Alert.vAlert.Priority

The WHERE clause simply limits the query by the dates and the site name (if any) that was passed from the report form. The variables preceded by the @ symbol are parameters defined in the report form.

WHERE

Alert.vAlert.RaisedDateTime >= @StartDate AND

Alert.vAlert.RaisedDateTime < @EndDate AND

(@SiteName IS NULL OR Alert.vAlert.SiteName = @SiteName)

Finally, there’s a simple GROUP BY clause which groups records with identical results into summary rows, so that the report does not contain duplicate alerts:

GROUP BY

Alert.vAlert.AlertName,

Alert.vAlert.Severity,

Alert.vAlert.Priority,

Alert.vAlert.ManagedEntityRowId,

Alert.vAlert.SiteName,

Alert.vAlert.AlertProblemGuid

Here’s what the complete query looks like:

SELECT

Alert.vAlert.AlertName,

Alert.vAlert.Severity,

Alert.vAlert.Priority,

Alert.vAlert.ManagedEntityRowId,

MAX(Alert.vAlert.RaisedDateTime) AS LastRaisedTime,

MIN(Alert.vAlert.RaisedDateTime) AS FirstRaisedTime,

MIN(Alert.vAlert.AlertDescription) AS AlertDescription,

COUNT(*) AS RepeatCount,

Alert.vAlert.SiteName,

Alert.vAlert.AlertProblemGuid

FROM Alert.vAlert

INNER JOIN #ObjectList ON #ObjectList.ManagedEntityRowId = Alert.vAlert.ManagedEntityRowId

INNER JOIN #SeverityList ON #SeverityList.Severity = Alert.vAlert.Severity

INNER JOIN #PriorityList ON #PriorityList.Priority = Alert.vAlert.Priority

WHERE

Alert.vAlert.RaisedDateTime >= @StartDate AND

Alert.vAlert.RaisedDateTime < @EndDate AND

(@SiteName IS NULL OR Alert.vAlert.SiteName = @SiteName)

GROUP BY

Alert.vAlert.AlertName,

Alert.vAlert.Severity,

Alert.vAlert.Priority,

Alert.vAlert.ManagedEntityRowId,

Alert.vAlert.SiteName,

Alert.vAlert.AlertProblemGuid

Although this query may seem complicated, its function isn’t. It retrieves most of the values from the vAlert view, filtered by date, with additional access to some outside lists.