Jean-Sébastien DUCHENE Blog's

Actualité, Tips, Articles sur l'ensemble des Technologies Microsoft (SCCM/SMS, EMS, Microsoft Intune, Microsoft Azure, Windows 10, SCOM, MDOP...)

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 deux rapports :

  • Le premier permet de résumer (nombre, date d’apparition) les alertes d’un agent dans un intervalle donné

  • Le second permet de donner en détail les alertes levées et les différents états de résolution pour un agent dans un intervalle donné

 

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

 

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 two reports:

  • The first one summarizes (Number, raised date) agent’s alerts in a given interval
  • The second gives alerts raised with detailed information like resolution states in a given interval.

 

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 are the queries :

SELECT     Alert.vAlert.AlertName, Alert.vAlert.RaisedDateTime,

                      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', Count(AlertName) As 'NbOfAlerts'

FROM         Alert.vAlert INNER JOIN

                      vManagedEntity ON vManagedEntity.ManagedEntityRowId = Alert.vAlert.ManagedEntityRowId

WHERE     (Alert.vAlert.RaisedDateTime BETWEEN @StartDate AND @EndDate) AND (vManagedEntity.FullName LIKE N'%' + @ComputerName + N'%') AND

                      (Alert.vAlert.Severity >= 1)

GROUP BY Alert.vAlert.AlertName, Alert.vAlert.Severity, Alert.vAlert.Priority, Alert.vAlert.RaisedDateTime

ORDER BY Alert.vAlert.AlertName, Alert.vAlert.RaisedDateTime

 

And the other one :

 

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],

                      CASE Alert.vAlertResolutionState.ResolutionState WHEN '0' THEN 'New' WHEN '255' THEN 'Closed' WHEN '1' THEN 'Ready for Omnibus' WHEN '85' THEN 'ACK' END As 'Resolution State',

                      Alert.vAlertResolutionState.StateSetDateTime As 'State Date', Alert.vAlertResolutionState.StateSetByUserId As 'User', Alert.vAlertDetail.Owner, vManagedEntity.Name, vManagedEntity.DisplayName,

                      vManagedEntity.ManagedEntityDefaultName

FROM         Alert.vAlertDetail WITH (NOLOCK) INNER JOIN

                      Alert.vAlertResolutionState WITH (NOLOCK) ON Alert.vAlertDetail.AlertGuid = Alert.vAlertResolutionState.AlertGuid INNER JOIN

                      Alert.vAlert WITH (NOLOCK) ON Alert.vAlertDetail.AlertGuid = Alert.vAlert.AlertGuid INNER JOIN

                      vManagedEntity WITH (NOLOCK) ON Alert.vAlert.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId

WHERE     (Alert.vAlert.ManagedEntityRowId IN (@EntityId))

AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vAlert.RaisedDateTime)) between @startdate and @enddate

ORDER BY  Alert.vAlert.RaisedDateTime DESC

 

Download :

This report is provided "AS IS" without express or implied warranty of any kind.

Facebook Like
Anonymous