As always, Kevin Holman to the rescue!
Outages and Maintenance Report
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âŚ)
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
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:
{
"_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!
some one else missing this?