Les rapports fournis avec System Center Operations Manager (SCOM) peuvent paraître parfois pauvre notamment en matière d’administration quotidienne du produit. Aujourd’hui, je vous propose un rapport listant toutes les alertes de moniteurs ou règles levées par un Management Pack dans un intervalle spécifique.
Attention les données proposées dépendent de la rétention du Data WareHouse.
N’oubliez pas de vous créer une data source pointant sur le data warehouse avec les droits nécessaires pour que le rapport puisse être exécuté. Ce rapport marche pour System Center Operations Manager 2007 R2, System Center 2012 Operations Manager et SQL Server 2008 RTM/R2.
Télécharger le rapport Management Pack - Alerts Raised
The reports provided with System Center Operations Manager (SCOM) may sometimes seem particularly poor in terms of daily operations on the product. Today, I offer a report to list all alerts raised by a Management Pack in a given interval.
Beware: the proposed data depend on the retention time of the Data WareHouse.
Do not forget to create a datasource targeting to the data warehouse with security rights necessary to generate the report. This reports works with System Center Operations Manager 2007 R2, System Center 2012 Operations Manager and SQL Server 2008 RTM/R2.
Here is the query:
SELECT Distinct Alert.vAlert.AlertName AS 'Alert Name', Alert.vAlert.AlertDescription AS [Alert Description], Alert.vAlertDetail.TicketId AS 'Ticket ID',
CASE Alert.vAlert.Severity WHEN '0' THEN 'Information' WHEN '1' THEN 'Warning' WHEN '2' THEN 'Critical' END AS 'Severity',
CASE Alert.vAlert.Priority WHEN '0' THEN 'Low' WHEN '1' THEN 'Medium' WHEN '2' THEN 'High' END AS 'Priority', Alert.vAlert.RepeatCount AS 'Repeat Count',
Alert.vAlert.RaisedDateTime AS [Raised Date], COUNT(vAlert.AlertName)
FROM Alert.vAlertDetail WITH (NOLOCK) INNER JOIN
Alert.vAlert WITH (NOLOCK) ON Alert.vAlertDetail.AlertGuid = Alert.vAlert.AlertGuid INNER JOIN
vRule WITH(NOLOCK) ON (vRule.RuleRowId = vAlert.WorkflowRowId AND vAlert.MonitorAlertInd = 0) INNER JOIN
vManagementPack WITH (NOLOCK) ON (vRule.ManagementPackRowId = vManagementPack.ManagementPackRowId)
WHERE (vManagementPack.ManagementPackRowId = @MPiD)
AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vAlert.RaisedDateTime)) between @startdate and @enddate
GROUP BY Alert.vAlert.AlertName, Alert.vAlert.AlertDescription, Alert.vAlertDetail.TicketId, Alert.vAlert.Severity, Alert.vAlert.Priority, Alert.vAlert.RaisedDateTime, Alert.vAlert.RepeatCount
UNION
SELECT Distinct Alert.vAlert.AlertName AS 'Alert Name', Alert.vAlert.AlertDescription AS [Alert Description], Alert.vAlertDetail.TicketId AS 'Ticket ID',
CASE Alert.vAlert.Severity WHEN '0' THEN 'Information' WHEN '1' THEN 'Warning' WHEN '2' THEN 'Critical' END AS 'Severity',
CASE Alert.vAlert.Priority WHEN '0' THEN 'Low' WHEN '1' THEN 'Medium' WHEN '2' THEN 'High' END AS 'Priority', Alert.vAlert.RepeatCount AS 'Repeat Count',
Alert.vAlert.RaisedDateTime AS [Raised Date], COUNT(vAlert.AlertName)
FROM Alert.vAlertDetail WITH (NOLOCK) INNER JOIN
Alert.vAlert WITH (NOLOCK) ON Alert.vAlertDetail.AlertGuid = Alert.vAlert.AlertGuid INNER JOIN
vMonitor WITH(NOLOCK) ON (vMonitor.MonitorRowId = vAlert.WorkflowRowId AND vAlert.MonitorAlertInd = 1) INNER JOIN
vManagementPack WITH (NOLOCK) ON (vMonitor.ManagementPackRowId = vManagementPack.ManagementPackRowId)
WHERE (vManagementPack.ManagementPackRowId = @MPiD)
AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vAlert.RaisedDateTime)) between @startdate and @enddate
GROUP BY Alert.vAlert.AlertName, Alert.vAlert.AlertDescription, Alert.vAlertDetail.TicketId, Alert.vAlert.Severity, Alert.vAlert.Priority, Alert.vAlert.RaisedDateTime, Alert.vAlert.RepeatCount
ORDER BY Alert.vAlert.RaisedDateTime DESC
Download Management Pack - Alerts Raised Report
This report is provided "AS IS" without express or implied warranty of any kind.
