Creating Custom Queries

Applies To: Operations Manager 2007 R2

The core activity when creating a custom report is creating a custom Transact-SQL query to retrieve the information you need. When you design a custom report, if you are unfamiliar with Transact-SQL, it may be easier to obtain a Transact-SQL query from an outside source, such as Kevin Holman’s OpsMgr Blog (https://go.microsoft.com/fwlink/?LinkId=207524) or SQL Server Central (http://www.sqlservercentral.com/) rather than attempting to create an original query.

This section presents three examples of custom queries, including three different types of data: performance, alerts, and availability (or state). These examples should provide a foundation for creating custom queries of your own.

The first example creates a processor performance report for a particular server. A report from the generic library can provide the performance information but it may not be in the best grouping, format, or appearance.

This report includes performance data and the query should be run against the vPerfHourly view. vPerfDaily could also be used, depending on the desired level of detail of the data. In this example we have used vPerfHourly. Therefore, vPerfHourly is the view used in the FROM clause, with vPerf being the alias.

The next step is to determine the specific pieces of information from this dataset needed for the report. In this case, this is the DateTime, SampleCount, AverageValue, MinValue, MaxValue, and StandardDeviation columns from the vPerfHourly view. These columns are added to the SELECT clause.

As a best practice, when using the larger tables such as the vPerformance or vEvent tables, always specify date clauses in the WHERE clause. This ensures the use of the table index for fast querying.

The following is the first iteration of the query:

SELECT
vPerf.DateTime, 
vPerf.SampleCount, 
vPerf.AverageValue,
vPerf.MinValue,
vPerf.MaxValue,
vPerf.StandardDeviation 
FROM Perf.vPerfHourly AS vPerf 
WHERE 
vPerf.DateTime >= '2010-06-28 15:19:59.387' 
and vPerf.DateTime < '2010-07-10 15:19:59.387'
ORDER BY  vPerf.DateTime

This query returns the requested data from the vPerfHourly table: date and time, Sample Count, Average, minimum value, maximum value, and standard deviation, within the times specified. However, the data is not filtered with regard to performance rule or managed instance; all the rows in the database between those two dates are retrieved.

The next step is to filter the results of the previous query to just the % Processor Time counter. The processor time counter requires the use of the Processor object. Therefore, the next version of the query requires the CounterName and ObjectName columns from the vPerformanceRule view. There is no foreign key to join vPerfHourly directly to vPerformanceRule. However, vPerformanceRule can be joined to vPerformanceRuleInstance using RuleRowId as a foreign key. In turn, vPerformanceRuleInstance can be joined to vPerfHourly using PerformanceRuleInstanceRowId. The modified query looks like this:

SELECT     
vPerf.DateTime,
vPerf.SampleCount, 
vPerf.AverageValue,
vPerf.MinValue,
vPerf.MaxValue,
vPerf.StandardDeviation, 
vPerformanceRuleInstance.InstanceName, 
vPerformanceRule.ObjectName, 
 vPerformanceRule.CounterName
FROM Perf.vPerfHourly AS vPerf INNER JOIN
 vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN
 vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId 
WHERE 
vPerf.DateTime >= '2010-06-28 15:19:59.387' 
and vPerf.DateTime < '2010-07-10 15:19:59.387'
AND (vPerformanceRule.ObjectName IN ('Processor'))
AND (vPerformanceRule.CounterName IN ('% Processor Time'))
ORDER BY  vPerf.DateTime

This version of the query returns the data just for the % Processor Time counter. However, it is still reporting on every processor object in the database not just the specified entities.

The next step is to join the current query the objects to report on the managed servers. To do this, the query needs to join the vManagedEntity view in the FROM clause using ManagedEntityRowId as the foreign key. A specific ManagedEntityRowId is added to the WHERE clause for retrieval. In addition, vManagedEntity.Path is added to the SELECT clause. Here’s the next part of the query:

SELECT
vPerf.DateTime,
vPerf.SampleCount, 
vPerf.AverageValue,
vPerf.MinValue,
vPerf.MaxValue,
vPerf.StandardDeviation, 
vPerformanceRuleInstance.InstanceName, 
vManagedEntity.Path, 
vPerformanceRule.ObjectName, 
 vPerformanceRule.CounterName
FROM Perf.vPerfHourly AS vPerf INNER JOIN
 vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN
 vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
 vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId 
WHERE 
vPerf.DateTime >= '2010-06-28 15:19:59.387' 
and vPerf.DateTime < '2010-07-10 15:19:59.387'
AND (vPerformanceRule.ObjectName IN ('Processor'))
AND (vPerformanceRule.CounterName IN ('% Processor Time'))
AND (vManagedEntity.Path IN ('OM10DC.OM10.lab'))
ORDER BY  vPerf.DateTime

This query returns the results for the managed servers on the specified computer OM10DC.OM10.lab. This result is not particularly flexible because the dates, counters, and server are all coded into the query. Allowing the user to choose these parameters is described in Custom Report Parameters.

The following sample query retrieves information about the alerts generated by the SQL Service Windows Service monitor in the month of June 2010. This query returns the path and name of the managed object, the Alert name, the time the alert was raised, the value of Parameter 1 in the Alert, how long it took for the alert to be resolved, and the name of the user who resolved it.

The query begins by selecting the desired information from the vAlert view:

SELECT 
vAlert.AlertName,
vAlert.RaisedDateTime,
FROM
Alert.vAlert AS vAlert 
ORDER BY RaisedDateTime

This query returns the two specified columns for all the alerts in the vAlert table. The ParameterValue column isn’t found in the vAlert view as it’s in the vAlertParameter view, which can be joined using AlertGuid as the foreign key. Likewise, the StateSetByUserId and TimeFromRaisedSeconds columns are found in the vAlertResolutionState view, which can also be joined using the AlertGuid as the foreign key. The query should only return alerts where the resolution state is “resolved” which is a value of 255. Therefore, that condition is added to the WHERE clause.

SELECT 
vAlert.AlertName,
vAlert.RaisedDateTime,
vAlertParameter.ParameterValue,
vAlertResolutionState.TimeFromRaisedSeconds,
vAlertResolutionState.StateSetByUserId
FROM
Alert.vAlert AS vAlert 
INNER JOIN Alert.vAlertParameter AS vAlertParameter 
ON vAlertParameter.AlertGuid=vAlert.AlertGuid 
INNER JOIN Alert.vAlertResolutionState AS vAlertResolutionState 
ON vAlertResolutionState.AlertGuid=vAlert.AlertGuid
WHERE 
vAlertResolutionState.ResolutionState=255
ORDER BY RaisedDateTime

The next step is to add the desired dates to the WHERE clause as well as the requirement that the alert has a value in parameter 1:

SELECT 
vAlert.AlertName,
vAlert.RaisedDateTime,
vAlertParameter.ParameterValue,
vAlertResolutionState.TimeFromRaisedSeconds,
vAlertResolutionState.StateSetByUserId
FROM
Alert.vAlert AS vAlert 
INNER JOIN Alert.vAlertParameter AS vAlertParameter 
ON vAlertParameter.AlertGuid=vAlert.AlertGuid 
INNER JOIN Alert.vAlertResolutionState AS vAlertResolutionState 
ON vAlertResolutionState.AlertGuid=vAlert.AlertGuid
WHERE 
vAlertResolutionState.ResolutionState=255
AND vAlertParameter.ParameterIndex=1
AND vAlert.RaisedDateTime between '2010-06-01' and '2010-06-30'
ORDER BY RaisedDateTime

The next step is to add the requirement that the alert is raised by the SQL Server Windows Service monitor. This is done by adding a restriction on MonitorDefaultName to the WHERE clause. MonitorDefaultName is in the vMonitor view, which can be joined to the vAlert view by setting vmonitor.MonitorRowId equal to vAlert.WorkflowRowId:

SELECT 
vAlert.AlertName,
vAlert.RaisedDateTime,
vAlertParameter.ParameterValue,
vAlertResolutionState.TimeFromRaisedSeconds,
vAlertResolutionState.StateSetByUserId
FROM
Alert.vAlert AS vAlert 
INNER JOIN Alert.vAlertParameter AS vAlertParameter 
ON vAlertParameter.AlertGuid=vAlert.AlertGuid 
INNER JOIN Alert.vAlertResolutionState AS vAlertResolutionState 
ON vAlertResolutionState.AlertGuid=vAlert.AlertGuid
INNER JOIN vMonitor ON vMonitor.MonitorRowId=vAlert.WorkflowRowid
WHERE 
vAlertResolutionState.ResolutionState=255
AND vAlertParameter.ParameterIndex=1
AND vAlert.RaisedDateTime between '2010-06-01' and '2010-06-30'
AND vMonitor.MonitorDefaultName='SQL Server Windows Service'
ORDER BY RaisedDateTime

The final iteration of the query adds the name and path of the managed entity to the SELECT statement. This requires a join to the vManagedEntity view, which can be joined to the vAlert view using ManagedEntityRowId as the foreign key. There is also an addition to the WHERE clause to restrict results to rows where the type of the managed entity is SQL DB Engine. This requires a join to the ManagedEntityType view, which can be joined to the vManagedEntity view on vManagedEntityTypeRowId:

SELECT 
vManagedEntity.Path,
vManagedEntity.Name,
vAlert.AlertName,
vAlert.RaisedDateTime,
vAlertParameter.ParameterValue,
vAlertResolutionState.TimeFromRaisedSeconds,
vAlertResolutionState.StateSetByUserId
FROM
Alert.vAlert AS vAlert 
INNER JOIN vManagedEntity ON vManagedEntity.ManagedEntityRowId=vAlert.ManagedEntityRowId
INNER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId=vManagedEntity.ManagedEntityTypeRowId
INNER JOIN Alert.vAlertParameter AS vAlertParameter 
ON vAlertParameter.AlertGuid=vAlert.AlertGuid 
INNER JOIN Alert.vAlertResolutionState AS vAlertResolutionState 
ON vAlertResolutionState.AlertGuid=vAlert.AlertGuid
INNER JOIN vMonitor ON vMonitor.MonitorRowId=vAlert.WorkflowRowid
WHERE 
vAlertResolutionState.ResolutionState=255
AND vAlertParameter.ParameterIndex=1
AND vAlert.RaisedDateTime between '2010-06-01' and '2010-06-30'
AND vMonitor.MonitorDefaultName='SQL Server Windows Service'
AND vManagedEntityType.ManagedEntityTypeDefaultName ='SQL DB Engine'
ORDER BY RaisedDateTime

This final version of the query returns all the resolved alerts generated by the SQL Server Windows Service during the month of June 2010 as the requirements stated. As with the performance report, this report is not very flexible because the dates, resolution state, and managed entity cannot be modified without editing the Transact-SQL query directly. These elements can be changed with parameters as described in Custom Report Parameters

The following sample query will retrieve information about when the Computer Availability Health Rollup monitor was in the red or the yellow state for the month of June, 2010. The generic availability report will provide the information for all states and for specific objects, but this custom report has a narrower focus. Availability information is found in the state views, specifically vStateDaily and vStateHourly. This query uses vStateDaily.

The first iteration of the query retrieves the time in the red and yellow states together with the time stamp that the state was retrieved for the month of June 2010.

SELECT
vStateDaily.DateTime,
vStateDaily.InRedStateMilliseconds,
vStateDaily.InYellowStateMilliseconds
FROM 
State.vStateDaily AS vStateDaily 
WHERE 
DateTime BETWEEN '2010-06-01' and '2010-06-30'
ORDER BY DateTime

The next step is to specify the monitor in order to narrow the returned data. The monitors are found in the vMonitor view, which cannot be joined to vStateDaily, but can be joined to vManagedEntityMonitor using MonitorRowId as the foreign key. vManagedEntityMonitor can then be joined to vStateDaily using ManagedMonitorRowId as a foreign key.

SELECT
vStateDaily.DateTime,
vStateDaily.InRedStateMilliseconds,
vStateDaily.InYellowStateMilliseconds
FROM 
State.vStateDaily AS vStateDaily 
INNER JOIN vManagedEntityMonitor ON vManagedEntityMonitor.ManagedEntityMonitorRowId=vStateDaily.ManagedEntityMonitorRowId
INNER JOIN vMonitor ON vMonitor.MonitorRowId=vManagedEntityMonitor.MonitorRowId
WHERE 
vMonitor.MonitorDefaultName='Computer Availability Health Rollup'
AND DateTime BETWEEN '2010-06-01' and '2010-06-30'
ORDER BY DateTime

The next step is to retrieve the path and name for the managed entity. As in the Alert monitor section, these columns are found in the vManagedEntity view, which can be joined to vManagedEntityMonitor using MonitorRowId as the foreign key.

SELECT
vManagedEntity.Path,
vManagedEntity.Name,
vStateDaily.DateTime,
vStateDaily.InRedStateMilliseconds,
vStateDaily.InYellowStateMilliseconds
FROM 
State.vStateDaily AS vStateDaily 
INNER JOIN vManagedEntityMonitor ON vManagedEntityMonitor.ManagedEntityMonitorRowId=vStateDaily.ManagedEntityMonitorRowId
INNER JOIN vMonitor ON vMonitor.MonitorRowId=vManagedEntityMonitor.MonitorRowId
INNER JOIN vManagedEntity ON vManagedEntity.ManagedEntityRowId=vManagedEntityMonitor.ManagedEntityRowId
WHERE 
vMonitor.MonitorDefaultName='Computer Availability Health Rollup'
AND DateTime BETWEEN '2010-06-01' and '2010-06-30'
ORDER BY DateTime

As with the other two reports, the monitor and dates are coded directly into the query and are not flexible. This can be solved by using parameters as described in Custom Report Parameters