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 permettant de lister l’historique de maintenance appliqué à une machine et ses objets associés durant un intervalle donné.
Vous pourrez ainsi retrouver des informations comme le Management Pack, la classe l’objet, la date de démarrage, la date de fin, la date de fin planifiée, la raison spécifiée, le commentaire et l’identifiant de l’utilisateur ayant appliqué le mode maintenance.
N’oubliez pas de vous créer une datasource pointant sur la base de données opérationnelle avec les droits nécessaires pour que le rapport puisse être exécuté.
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 the maintenance history applied to a machine and its associated objects during a given interval. You can then retrieve information such as the Management Pack, the class, the object, the start date, the end date, the end date planned, the reason specified, the comment and the user id who applied the maintenance mode.
Do not forget to create a datasource targeting to the operational database with security rights necessary to generate the report.
Here is the query :
SELECT ManagedEntity.FullName, ManagedEntity.Path, ManagedEntity.Name, ManagedEntity.DisplayName, ManagedEntity.ManagedEntityDefaultName,
MaintenanceMode.StartDateTime, MaintenanceMode.EndDateTime, ManagedEntityType.ManagedEntityTypeSystemName,
ManagedEntityType.ManagedEntityTypeDefaultName, ManagedEntityType.ManagedEntityTypeDefaultDescription, TypedManagedEntity.FromDateTime,
TypedManagedEntity.ToDateTime, MaintenanceModeHistory.ScheduledEndDateTime,
CASE MaintenanceModeHistory.PlannedMaintenanceInd WHEN '1' THEN 'Scheduled' WHEN '0' THEN 'Unscheduled' END AS 'Outage Type',
CASE MaintenanceModeHistory.ReasonCode WHEN '0' THEN 'Other (Planned)' WHEN '1' THEN 'Other (Unplanned)' WHEN '2' THEN 'Hardware: Maintenance (Planned)'
WHEN '3' THEN 'Hardware: Maintenance (Unplanned)' WHEN '4' THEN 'Hardware: Installation (Planned)' WHEN '5' THEN 'Hardware: Installation (Unplanned)' WHEN '6'
THEN 'Operating System: Reconfiguration (Planned)' WHEN '7' THEN 'Operating System: Reconfiguration (Unplanned)' WHEN '8' THEN 'Application: Maintenance (Planned)'
WHEN '9' THEN 'Application: Maintenance (Unplanned)' WHEN '10' THEN 'Application: Installation (Planned)' WHEN '11' THEN 'Application: Unresponsive' WHEN '12'
THEN 'Application: Unstable' WHEN '13' THEN 'Security Issue' WHEN '14' THEN 'Loss of network connectivity (Unplanned)' END AS 'Reason Cause',
MaintenanceModeHistory.Comment, MaintenanceModeHistory.UserId, ManagementPack.ManagementPackSystemName,
ManagementPack.ManagementPackDefaultName
FROM ManagedEntityType WITH (NOLOCK) INNER JOIN
ManagementPack WITH (NOLOCK) ON ManagedEntityType.ManagementPackRowId = ManagementPack.ManagementPackRowId INNER JOIN
TypedManagedEntity WITH (NOLOCK) ON ManagedEntityType.ManagedEntityTypeRowId = TypedManagedEntity.ManagedEntityTypeRowId INNER JOIN
ManagedEntity WITH (NOLOCK) ON ManagedEntityType.ManagedEntityTypeRowId = ManagedEntity.ManagedEntityTypeRowId AND
TypedManagedEntity.ManagedEntityRowId = ManagedEntity.ManagedEntityRowId LEFT OUTER JOIN
MaintenanceMode WITH (NOLOCK) ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId LEFT OUTER JOIN
MaintenanceModeHistory WITH (NOLOCK) ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowId
WHERE (TypedManagedEntity.ToDateTime IS NULL) AND (ManagedEntity.Path LIKE '%' + @ComputerName + '%') AND (MaintenanceMode.StartDateTime BETWEEN
@MMStartDate AND @MMEndDate) AND (MaintenanceMode.EndDateTime BETWEEN @MMStartDate AND @MMEndDate) OR
(TypedManagedEntity.ToDateTime IS NULL) AND (MaintenanceMode.StartDateTime BETWEEN @MMStartDate AND @MMEndDate) AND
(MaintenanceMode.EndDateTime BETWEEN @MMStartDate AND @MMEndDate) AND (ManagedEntity.Name LIKE @ComputerName) OR
(TypedManagedEntity.ToDateTime IS NULL) AND (ManagedEntity.Path LIKE N'%' + @ComputerName + N'%') AND (MaintenanceMode.StartDateTime IS NULL) AND
(MaintenanceMode.EndDateTime IS NULL) OR
(TypedManagedEntity.ToDateTime IS NULL) AND (MaintenanceMode.StartDateTime IS NULL) AND (MaintenanceMode.EndDateTime IS NULL) AND
(ManagedEntity.Name LIKE @ComputerName)
GROUP BY ManagedEntity.FullName, ManagedEntity.Path, ManagedEntity.Name, ManagedEntity.DisplayName, ManagedEntity.ManagedEntityDefaultName,
MaintenanceMode.StartDateTime, MaintenanceMode.EndDateTime, MaintenanceMode.PlannedMaintenanceInd, ManagedEntityType.ManagedEntityTypeSystemName,
ManagedEntityType.ManagedEntityTypeDefaultName, ManagedEntityType.ManagedEntityTypeDefaultDescription, TypedManagedEntity.FromDateTime,
TypedManagedEntity.ToDateTime, MaintenanceModeHistory.ScheduledEndDateTime, MaintenanceModeHistory.PlannedMaintenanceInd,
MaintenanceModeHistory.ReasonCode, MaintenanceModeHistory.Comment, MaintenanceModeHistory.UserId, ManagementPack.ManagementPackSystemName,
ManagementPack.ManagementPackDefaultName
ORDER BY ManagedEntity.Path
Download Maintenance History report
This report is provided "AS IS" without express or implied warranty of any kind.
