SCOM Maintenance Mode - Current count

Hi Team,

I’m working on reporting for that i need to show number of servers currently in maintenance mode using sql query.
Any one have sql query to get number of servers in maintenance mode.

Looks like this has already been discussed here:

Not looking for maintenance history , looking for only (i.e) number of servers currently in maintenance mode.

Is this for a SCOM report or a SquaredUp dashboard?

From the other post, you just need to add a where clause to filter down based on class and date.

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

WHERE vmet.ManagedEntityTypeDefaultName = 'Windows Computer' 
AND (vmm.EndDateTime >=  GETDATE() OR vmm.EndDateTime IS NULL)

order by vmm.StartDateTime desc

This provides you with:

You just need to count the results, if needed.

To do this in SquaredUp is easy enough:

Use an advanced scope to select your class and the criteria:

InMaintenanceMode = 1

You could then share this dashboard with Open Access.

You can take it a step further and create a perspective on Windows Computer class, to show information on each server (though SquaredUp already shows this):

The sublabel contains the maintenance mode properties:

User: {{maintenanceMode.user}}<br> Comment: {{maintenanceMode.comments}}<br> Reason: {{maintenanceMode.reason}}<br> Start: {{timeago(maintenanceMode.startTime)}} <br> End: {{timeago(maintenanceMode.scheduledEndTime)}}

No able to fetch the count of number of servers in maintenance mode

The SQL query pulls back the objects in maintenance mode, you only need count the results.

You can do this by replacing the selection of columns with a count:

Select COUNT(*)
	
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

-- Add your class here
WHERE vmet.ManagedEntityTypeDefaultName = 'Windows Computer' 


AND (vmm.EndDateTime >=  GETDATE() OR vmm.EndDateTime IS NULL)
1 Like