SQL Query for Avaialbility

Question

Has someone got a SQL Query to get the Availability for any object or group?

I'm looking to mimic the Avaialbility report found in native SCOM Reporting.

Answer 1

Hi,

We may try the following query:

Select
S.ManagedEntityMonitorRowId,
sum(S.InRedStateMilliseconds/100000) as InRedState,
sum(S.InYellowStateMilliseconds/100000) as InYellowState,
sum(S.InGreenStateMilliseconds/100000) as InGreenState,
sum(S.InWhiteStateMilliseconds/100000) as InWhiteState,
sum(S.InDisabledStateMilliseconds/100000) as InDisabledState,
sum(S.InPlannedMaintenanceMilliseconds/100000) as InPlannedMaintenance,
sum(S.InUnplannedMaintenanceMilliseconds/100000) as InUnplannedMaintenance,
sum(S.HealthServiceUnavailableMilliseconds/100000) as HealthServiceUnavailable
FROM [OperationsManagerDW].[dbo].[vStateDailyFull] S group by S.ManagedEntityMonitorRowId

Hope the above information helps.

Alex Zhu

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi,

We may try the following query to see if it works.

Select
S.ManagedEntityMonitorRowId,
S.DateTime,
S.Date,
S.InRedStateMilliseconds,
S.InYellowStateMilliseconds,
S.InGreenStateMilliseconds,
S.InWhiteStateMilliseconds,
S.InDisabledStateMilliseconds,
S.InPlannedMaintenanceMilliseconds,
S.InUnplannedMaintenanceMilliseconds,
S.HealthServiceUnavailableMilliseconds
FROM [OperationsManagerDW].[dbo].[vStateDailyFull] S

Hope the above information helps.

Alex Zhu

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi

Yes, this query I know of. The issue is to take it further to calculate downtime for red state, yellow state and so on. :-)