SQL Query for Avaialbility

Peter Svensson 211 Reputation points
2020-10-05T14:14:09.577+00:00

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.

Operations Manager
Operations Manager
A family of System Center products that provide infrastructure monitoring, help ensure the predictable performance and availability of vital applications, and offer comprehensive monitoring for datacenters and cloud, both private and public.
1,394 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. AlexZhu-MSFT 5,236 Reputation points Microsoft Vendor
    2020-10-07T07:07:12.34+00:00

    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

    30603-scom-query-01.png

    Hope the above information helps.

    Alex Zhu


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

    1 person found this answer helpful.
    0 comments No comments

  2. AlexZhu-MSFT 5,236 Reputation points Microsoft Vendor
    2020-10-06T05:59:15.18+00:00

    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.

    0 comments No comments

  3. Peter Svensson 211 Reputation points
    2020-10-06T07:56:06.507+00:00

    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. :-)

    0 comments No comments