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)
MaintenanceMode ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId
MaintenanceModeHistory ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowId

1 Like

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…)

	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!


I’m missing the end date :frowning: some one else missing this?

1 Like

I’m missing the end date some one else missing this?

1 Like

Now that I’ve had a chance to look at this a bit further, it seems to not show me what I thought. This appears to show more of the “maintenance mode history of all objects”. While I can see the purpose for that, I was expecting something that showed the history of Windows Computer (Server) objects that are currently in MMode, with their history.

“the history of Windows Computer (Server) objects that are currently in MMode, with their history.” - I’m guessing you mean you want to see all computers that are in MM with the current MM session settings (duration, settings etc)? You’ll just need to add a where clause that filters the output - GETUTCDATE() between vmm.StartDateTime and vmm.EndDateTime will handle things currently in MM, and you can use the vmet.ManagedEntityTypeDefaultName column to handle only targeting windows computers

Thanks. Exactly what I was looking for!

Did anyone make a query that really worked? The one at the top is good as it shows the actual ScheduledEndTime - but for everything

The 2nd one from KH shows literally everything but if you scope it to a class like WindowsComputer then it does not give the valid future dates it gives ‘NULL’

I’m after something in between the two. I want to show all servers currently in MMode with the comment and the user ID and thats about it.