Solved SQL Query – Maintenance Mode History
I’m sure we’ve all seen it all over the internet, the query below (slightly modified) can pull the MMode history from the OpsMgr DW. What I’m looking for is someone who’s already done this, or can help refine it to provide better results in the SquaredUp SQL tile. Specifically, I’m looking for a way to translate the numerical values into actual date/time.
SELECT ManagedEntity.DisplayName, MaintenanceModeHistory.*
FROM ManagedEntity WITH (NOLOCK)
INNER JOIN
MaintenanceMode ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId
INNER JOIN
MaintenanceModeHistory ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowId
Best answer
As always, Kevin Holman to the rescue!
The query below is slightly modified (it includes the Path and Class which can be helpful when you’re looking at a whole bunch of databases..)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Select vme.DisplayName, vme.Path, vmet.ManagedEntityTypeDefaultName as 'Class', vmm.StartDateTime as 'Start', vmm.EndDateTime as 'End', CASE vmm.PlannedMaintenanceInd WHEN '1' THEN 'Scheduled' WHEN '0' THEN 'Unscheduled' END AS 'Outage', CASE vmmh.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 'Type', vmmh.Comment as Reason, vmmh.UserId as 'User' FROM vMaintenanceMode as vmm INNER JOIN vManagedEntity as vme on vmm.ManagedEntityRowId = vme.ManagedEntityRowId INNER JOIN vMaintenanceModeHistory as vmmh on vmm.MaintenanceModeRowId = vmmh.MaintenanceModeRowId INNER JOIN vManagedEntityType as vmet on vmet.ManagedEntityTypeRowId = vme.ManagedEntityTypeRowId order by vmm.StartDateTime desc |
In terms of getting the Start and End times into something useful, you’ve got two choices – you can either try and convert this in SQL or do so in the SQL tile config. Converting in SQL is pretty easy as long as you are happy with the value shown in UTC, you can do something like
|
1 |
CONCAT(CONVERT(VARCHAR(24),vmm.StartDateTime, 113), ' UTC') As 'Start UTC' |
Which will display the value in European format (113 in the above snippet, see the “Date and Time Styles” portion of https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql for other formats). Converting out of UTC is hard however as even if you get the offset correct, you’ll still have daylight savings to deal with which is always much, much harder than you’d think.
The other option is to have the SQL tile convert the date for you (which should handle timezone and daylight savings conversion), but this requires getting your hands dirty with a bit of JSON. Assuming you are using the query above as is, you can override the display of columns and use a Mustache to specify how you want the column contents displayed (see some of the Squared Up coffee breaks on this if you aren’t familiar with them).
Once you’ve created the SQL tile, open up the JSON editor and add the display section below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
{ "_type": "tile/sql-as-table", "config": { "context": {}, "source": { "_security": "signing", "connectionstring": "global:dw", "querystring": "..." }, "display": { "columnOverrides": { "start": { "template": "{{timeago(value,true)}}" }, "end": { "template": "{{timeago(value,true)}}" } } } }, "description": "", "title": "Maintenance Mode History" } |
Note that “start” and “end” refer to the name of the column, so if you change this in the query you’ll need to update it here. This mustache uses the timeago function to display the column as a datetime (if the second parameter were false, it displays as a friendly value such as “6 minutes ago” as seen on the Alert tile).
As a final note, you’ll probably want to attach some WHERE clauses to the above SQL query, since right now it (and your original) are pulling MM history for everything in SCOM…
Hope that helps!
Answer this question
To reply or comment, use the 'Comment' link on the relevant answer or question.