Is it possible to show servers that have changed to a critical state within a given timeframe?

Dashboard Server
, , ,
1

I’m finalizing a dashboard for our Network Operations Center team and want to show a dashboard that lists servers that have gone into a Critical health state within a given time frame (e.g. last 1 hour, last 12 hours). Is this possible first of all in SCOM and if so also in SquaredUp? The use case is being able to easily new issues.

6 Likes
2

Natively in SCOM and Squared Up I don’t believe this is possible, or at least not with a Status section.

You should however be able to use the SQL Plugin to out put a list of servers in a critical health state where the state changed in a designated time period. This query could even be mapped to a SRS report to give the health state colouring we’re so fond of.

Unfortunately my SQL knowledge isn’t up to scratch to get something together for this, hopefully another community member will be able to assist you with this.

2 Likes
3

To point you closer into the right direction. Use the SQL Plugin and query the State.vStateDaily or State.vStateHourly views.
These contain the following attributes per ManagedEntity:

InRedStateMilliseconds
InYellowStateMilliseconds
InDisabledStateMilliseconds
InPlannedMaintenanceMilliseconds
InUnplannedMaintenanceMilliseconds
HealthServiceUnavailable

Then it’s just joining, filtering and ordering the query for the information you need.
If I’m able to put a working query together, I’ll post it here.

4 Likes
4

This should somewhat bring the Overview you are searching for:

SELECT substring(ManagedEntity.FullName,(charindex(‘:’,ManagedEntity.FullName)+1),120) AS FullName,
Max(Convert(varchar(20),State.vStateRaw.DateTime,120)) AS LastDateTurnedCritical
FROM ManagedEntity
INNER JOIN ManagedEntityMonitor ON ManagedEntityMonitor.ManagedEntityRowId = ManagedEntity.ManagedEntityRowId
INNER JOIN State.vStateRaw ON State.vStateRaw.ManagedEntityMonitorRowId = ManagedEntityMonitor.ManagedEntityMonitorRowId
WHERE ManagedEntity.FullName like ‘Microsoft.Windows.Computer:%’ AND DateTime >=dateadd(day,datediff(day,0,GetDate())- 1,0) AND State.vStateRaw.NewHealthState = ‘3’
GROUP BY ManagedEntity.FullName
ORDER BY Max(Convert(varchar(20),State.vStateRaw.DateTime,120)) DESC

This shows the last Date on which an Windows Computer has been in a Critical State for the last day. You can play around with this query, to get it to work as you please.

1 Like
5

Thanks for this, it’ll be great to get a query so look forward to hearing from you when you have time.

6

This should somewhat bring the Overview you are searching for:

SELECT substring(ManagedEntity.FullName,(charindex(’:’,ManagedEntity.FullName)+1),120) AS FullName,
Max(Convert(varchar(20),State.vStateRaw.DateTime,120)) AS LastDateTurnedCritical
FROM ManagedEntity
INNER JOIN ManagedEntityMonitor ON ManagedEntityMonitor.ManagedEntityRowId = ManagedEntity.ManagedEntityRowId
INNER JOIN State.vStateRaw ON State.vStateRaw.ManagedEntityMonitorRowId = ManagedEntityMonitor.ManagedEntityMonitorRowId
WHERE ManagedEntity.FullName like ‘Microsoft.Windows.Computer:%’ AND DateTime >=dateadd(day,datediff(day,0,GetDate())- 1,0) AND State.vStateRaw.NewHealthState = ‘3’
GROUP BY ManagedEntity.FullName
ORDER BY Max(Convert(varchar(20),State.vStateRaw.DateTime,120)) DESC

This shows the last Date on which an Windows Computer has been in a Critical State for the last day. You can play around with this query, to get it to work as you please.

7

Thanks for taking the time to get this on the Community site!