SCOM 2012: T-SQL to return alerts for a particular group.

Hi,

Does anyone know how I can return current open alerts for a particular group using t-sql?

I’m trying to setup a dashboard using this information.

I have various t-sql code segments to return Alerts, however I’m finding it impossible to tie this up to groups.

For Example;

Within the Operations Manager Console, I’ve setup a few groups. e.g. GroupA which contains 20 servers.

I’ve then created an alert view, with “show data related too” scoped to ‘GroupA’

This Alert view, now only display alerts for ‘GroupA’

How do I get the same data using t-sql from the Operations Manager database?

Thanks in advance,

Hugo.

6 thoughts on “SCOM 2012: T-SQL to return alerts for a particular group.

  1. krysred

    Run the following query and store into a temp table. It will take in a group name or part of a group name and returns the servers in that group. You can also pass it part of a group name and return servers in multiple groups that match:
    SELECT
    DISTINCT Name
    INTO #ServersInAGroup
    FROM
    Relationship RS with (noLock)
    JOIN ManagedEntityGenericView MEG with (noLock)
    on RS.TargetEntityId = MEG.BaseManagedEntityId
    WHERE
    RS.SourceEntityId in
    (
    SELECT
    ME1.BaseManagedEntityId
    FROM
    ManagedEntityGenericView ME1 with (noLock)
    WHERE
    ME1.DisplayName like ‘%DomainController%’ –put your group name here or part of a group name
    )
    and Name like ‘%.CORVEL.com’ –filter out extra objects by putting your domain here to get only the server names

    Then join this table into your query matching the names column. Typically this is the ManagedEntityGenericView table

  2. Uretzky

    I have quick and dirty soultion for you, try this query

    –first step is to union objects (since we want to relate healthservicewatcher to computer )
    SELECT
    R.TargetEntityId AS ContainerEntityId,–healthService
    R.SourceEntityId AS ContainedEntityId,–healthServiceWathcer
    1 DEPTH INTO #tmp1
    FROM OperationsManager..Relationship R WITH (NOLOCK)
    WHERE R.RelationshipTypeId = OperationsManager.dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceWatcherMonitorsHealthService()
    SELECT
    RM.ContainerEntityId,
    RM.ContainedEntityId,
    RM.Depth INTO #tmp2
    FROM Operationsmanager..RecursiveMembership RM WITH (NOLOCK) EXCEPT SELECT
    ContainedEntityId,
    ContainerEntityId,
    Depth
    FROM #tmp1

     
    ;

    WITH A
    AS (
    SELECT ContainerEntityId,ContainedEntityId FROM
    (
    SELECT
    ContainerEntityId,
    ContainedEntityId,
    Depth
    FROM #tmp2 UNION ALL SELECT
    ContainerEntityId,
    ContainedEntityId,
    Depth
    FROM #tmp1) RES
    JOIN Operationsmanager..BaseManagedEntity BME1 WITH (NOLOCK) ON RES.ContainerEntityId = BME1.BaseManagedEntityId
    JOIN OperationsManager..BaseManagedEntity BME2 WITH (NOLOCK) ON RES.ContainedEntityId = BME2.BaseManagedEntityId
    WHERE BME1.DisplayName=’replace me :)’ — group name
    )
    SELECT DISTINCT r.ContainerEntityId,BME2.BaseManagedEntityId,bme2.FullName into #t FROM A
    JOIN (SELECT
    ContainerEntityId,
    ContainedEntityId,
    Depth
    FROM #tmp2 UNION ALL SELECT
    ContainerEntityId,
    ContainedEntityId,
    Depth
    FROM #tmp1) r ON r.ContainerEntityId=a.ContainedEntityId
    JOIN OperationsManager..BaseManagedEntity BME2 WITH (NOLOCK) ON r.ContainedEntityId = BME2.BaseManagedEntityId

    — second step is to join alerts
    SELECT DISTINCT av.*, OperationsManager.dbo.fn_GetAlertDescription(av.AlertStringDescription,cast(av.AlertParams as xml)) alertDescription FROM #t t
    JOIN OperationsManager.dbo.AlertView av on av.MonitoringObjectId=t.BaseManagedEntityId OR av.MonitoringObjectId=t.ContainerEntityId
    WHERE av.ResolutionState=0 — open alerts

    DROP TABLE #tmp1
    DROP TABLE #tmp2
    DROP TABLE #t

    enjoy 🙂

  3. Uretzky

    I have quick and dirty soultion for you, try this query

    –first step is to union objects (since we want to relate healthservicewatcher to computer )

    DECLARE @id uniqueidentifier
    SELECT
    R.TargetEntityId AS ContainerEntityId,–healthService
    R.SourceEntityId AS ContainedEntityId,–healthServiceWathcer
    1 DEPTH INTO #tmp1
    FROM OperationsManager..Relationship R WITH (NOLOCK)
    WHERE R.RelationshipTypeId = OperationsManager.dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceWatcherMonitorsHealthService()
    SELECT
    RM.ContainerEntityId,
    RM.ContainedEntityId,
    RM.Depth INTO #tmp2
    FROM Operationsmanager..RecursiveMembership RM WITH (NOLOCK) EXCEPT SELECT
    ContainedEntityId,
    ContainerEntityId,
    Depth
    FROM #tmp1
    UNION ALL SELECT
    ContainerEntityId,
    ContainedEntityId,
    Depth
    FROM #tmp1

    ;

    ;
    WITH res
    AS (SELECT
    CAST(‘/’ + CAST(a.ContainedEntityId AS NVARCHAR(max)) + ‘/’ AS NVARCHAR(max)) AS Path,
    a.ContainerEntityId,
    a.ContainedEntityId,
    a.Depth
    FROM #tmp2 a
    WHERE a.Depth = 0 UNION ALL SELECT
    CAST(c.Path + CAST(b.ContainedEntityId AS NVARCHAR(max)) + ‘/’ AS NVARCHAR(max)),
    b.ContainerEntityId,
    b.ContainedEntityId,
    c.Depth+1
    FROM #tmp2 b
    INNER JOIN res c ON b.ContainerEntityId = c.ContainedEntityId
    AND b.Depth = 1
    )

    SELECT * into #t FROM Res
    SELECT @ID=
    R.ContainerEntityId
    FROM #t R
    JOIN OperationsManager..BaseManagedEntity BME1 WITH (NOLOCK) ON R.ContainedEntityId = BME1.BaseManagedEntityId
    WHERE bme1.DisplayName='{Replace me}’
    SELECT bme2.* into #objs FROM #t R
    JOIN OperationsManager..BaseManagedEntity BME2 WITH (NOLOCK) ON R.ContainedEntityId = BME2.BaseManagedEntityId
    WHERE r.path like ‘%’ + cast( @id AS NVARCHAR(max)) +’%’

    go

     

    –second step is to join alerts

    SELECT DISTINCT av.*, OperationsManager.dbo.fn_GetAlertDescription(av.AlertStringDescription,cast(av.AlertParams as xml)) alertDescription FROM #objs t
    JOIN OperationsManager.dbo.AlertView av on av.MonitoringObjectId=t.BaseManagedEntityId OR av.MonitoringObjectId=t.BaseManagedEntityId
    WHERE av.ResolutionState=0 — open alerts

    DROP TABLE #tmp1
    DROP TABLE #tmp2
    DROP TABLE #t
    DROP TABLE #objs

    enjoy

  4. Uretzky

    Even better query

    DECLARE @grpID uniqueidentifier
    DECLARE @relationHosting uniqueidentifier = ‘AE80F883-4409-9E35-03DA-90ECC19A8B2C’
    DECLARE @relationWatch uniqueidentifier = ’26AE25AF-4307-5414-B07F-EDA2BF845F9B’
    DECLARE @relationContain uniqueidentifier = ‘189F4500-7A70-DB53-9566-FEEA4695DA29’

    –all groups
    SELECT
    @grpID = bme.BaseManagedEntityId
    FROM OperationsManager..fn_DerivedManagedTypes(‘D0B32736-5344-2FCC-74B3-F72DC64EF572’) dm
    JOIN OperationsManager..ManagedType mt
    ON mt.ManagedTypeId = dm.DerivedManagedTypeId
    JOIN OperationsManager..BaseManagedEntity bme
    ON bme.BaseManagedEntityId = mt.ManagedTypeId
    WHERE bme.DisplayName = ‘Replace ME’
    SELECT av.* FROM (
    SELECT
    *
    FROM OperationsManager.dbo.fn_RelatedBaseEntitiesFromList(@grpID, @relationHosting, 3) UNION ALL SELECT
    *
    FROM OperationsManager.dbo.fn_RelatedBaseEntitiesFromList(@grpID, @relationWatch, 3)
    UNION ALL SELECT
    *
    FROM OperationsManager.dbo.fn_RelatedBaseEntitiesFromList(@grpID, @relationContain, 3)

    ) objs
    JOIN OperationsManager..AlertView av on av.MonitoringObjectId=objs.BaseManagedEntityId
    WHERE av.ResolutionState=0

  5. hugostyles Post author

    This is the final query which seems to work. Thanks for all the input.

    select *
    from OperationsManager.dbo.fn_AlertView(‘ENU’, null) BS
    where toplevelhostentityId
    in
    (
    select TopLevelHostEntityId
    from OperationsManager.dbo.BaseManagedEntity bme
    join OperationsManager.dbo.RelationshipGenericView rgv with(nolock)
    on bme.basemanagedentityid = rgv.TargetObjectid
    where bme.IsDeleted = 0
    and ResolutionState not in (255, 254) — change this
    and rgv.SourceObjectDisplayName in
    — Insert Groups Here —
    (
    ‘My Group’
    )
    )

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.