Creating Reports for SCOM and SCVMM - Microsoft System Center Reporting - page 207

How to do it...
In this recipe, we will create a table report that shows the alerts from last month, ordered by
the number of alerts per triggering rule/monitor. Perform the following steps:
1. Start Report Builder (use the Report Manager website or standalone option).
2. Under New Report , select Table or Matrix Wizard .
3. Select Create a dataset and click on Next .
4. Select the OperationsManagerDW data source connection. If it is not in the
list, click on Browse… to browse to the data source in SQL Server Reporting Ser-
vices. Click on Next .
5. If prompted for data source credentials, select Use the current Windows user and
click on OK .
6. Click on Edit as Text and type the following SQL query in the query window:
DECLARE @DateTime datetime = GETUTCDATE()
DECLARE @StartOfPreviousMonth datetime =
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @DateTime)
- 1, '19000101')
DECLARE @EndOfPreviousMonth datetime = DATEADD(MONTH,
DATEDIFF(MONTH, '19000101', @DateTime), '19000101')
SELECT
CASE A.MonitorAlertInd WHEN 1 THEN
M.MonitorDefaultName WHEN 0 THEN R.RuleDefaultName END
AS WorkflowName,
A.AlertName,
A.RaisedDateTime,
ET.ManagedEntityTypeDefaultName AS EntityType,
E.FullName AS Entity,
ETI.Image AS EntityImage
FROM
Alert.vAlert A
LEFT OUTER JOIN vMonitor M ON
A.WorkflowRowId = M.MonitorRowId
LEFT OUTER JOIN vRule R ON
A.WorkflowRowId = R.RuleRowId
LEFT OUTER JOIN vManagedEntity E ON
A.ManagedEntityRowId = E.ManagedEntityRowId