DX Unified Infrastructure Management

 View Only
  • 1.  Possible to extract alarms via API or SQL based on the OC group?

    Posted Oct 15, 2021 02:00 AM
    Edited by Steve B Oct 15, 2021 02:03 AM
    Hi guys,

    I've been trying to find a way to extract a list of alarms based on whether the alarming host is located under a particular OC group. There doesn't seem to be any way to do it via the API that I can find - but curious if anyone has any experience with the groups in the database?

    Our OC group structure is similar to this:

    Sites
     |- Location 1
       |- System 1
         |- Host 1
         |- Host 2
         |- Host 3
       |- System 2
         |- Host 1
         |- Host 2
         |- Host 3
       |- System 3
     |- Location 2
       |- System 1
         |- Host 1
         |- Host 2
         |- Host 3
       |- System 2
         |- Host 1
         |- Host 2
         |- Host 3
     |- Location 3  <--
       |- System 1
         |- Host 1
         |- Host 2
         |- Host 3
       |- System 2
         |- Host 1
         |- Host 2
         |- Host 3

    So for example - how do i extract the alarms for hosts that are only sitting under Location 3?

    Any advice or info would be greatly appreciated.

    Cheers


  • 2.  RE: Possible to extract alarms via API or SQL based on the OC group?

    Broadcom Employee
    Posted Oct 15, 2021 08:54 AM
    Hi Steve I did have to put something together for alarm on a dynamic group, example query below for hosts in a group. For a container(location3) you can play with using "OR"''s for group names, or look for the join field for dynamicGroup to Container to add to query. Someone will probably have a better query then mine :) 

    select alarms.hostname,alarms.message,alarms.severity from cm_group_member gmem
    join cm_computer_system system on gmem.cs_id = system.cs_id
    join nas_alarms alarms on alarms.hostname like '%'+ system.name +'%'
    join cm_group cmgroup on gmem.grp_id = cmgroup.grp_id
    where cmgroup.name = 'Windows'


  • 3.  RE: Possible to extract alarms via API or SQL based on the OC group?

    Posted Oct 17, 2021 07:10 AM
    Edited by Steve B Oct 17, 2021 07:15 AM
    Thanks Greg, much appreciated! 

    Unfortunately that doesn't seem to work recursivley on a group.

    The /uimapi/groups/xxxx api call seems to be able to list all the csid's of a group recursively. The groups API call sends the following sql query to get the list of sub-groups within a given group (in this case, group ID 1091):

    WITH nested_group AS
    ( SELECT cm_group.pgrp_id, cm_group.grp_id
    FROM cm_group
    WHERE cm_group.grp_id = 1091
    UNION ALL
    SELECT sub_nested_group.pgrp_id,sub_nested_group.grp_id
    FROM cm_group as sub_nested_group, nested_group
    WHERE nested_group.grp_id = sub_nested_group.pgrp_id )
    SELECT grp_id FROM nested_group

    A second call using the sub-group ID's is then used to obtain all the sub-group members:

    SELECT gme.grp_id, gme.cs_id AS member_id
    FROM cm_group_member gme
    WHERE ( gme.grp_id IN ( '1176','1175','1681','1174','1173','1172','1183','1171','1182','1490','1181','1170','1180','1169','1179','1178','1177','1907','1906','1836','1219','1218','1988' )
    )

    The alarms api call sends this query to sql:

    select a.*, d.cs_id, m.ci_metric_type, m.ci_id, p.cs_attr_value as primaryipv4address
    from nas_alarms a WITH(NOLOCK)
    LEFT JOIN cm_device d WITH(NOLOCK) on a.dev_id = d.dev_id
    LEFT JOIN cm_configuration_item_metric m WITH(NOLOCK) on a.met_id = m.ci_metric_id
    LEFT JOIN cm_computer_system_attr p WITH(NOLOCK) on d.cs_id = p.cs_id and p.cs_attr_key = 'PrimaryIPV4Address'
    where alarm_manager is null

    My issue is now trying to combine these together so it only obtains alarms from cs_ids that are in the second list...


  • 4.  RE: Possible to extract alarms via API or SQL based on the OC group?

    Broadcom Employee
    Posted Oct 18, 2021 08:04 AM
    Yes that will take some trial and error, in my case. Maybe some one more of an expert in SQL could come up with a query. I may visit with "a go" if time permits, good luck. and possibly share when you solve it :)
    I was suggesting using a possible "or" between dynamic group names but would only be static list query.


  • 5.  RE: Possible to extract alarms via API or SQL based on the OC group?

    Posted Oct 18, 2021 08:38 AM
    Edited by Ravishu Arora Oct 18, 2021 08:38 AM
    Thanks Greg, much appreciated! 

    Unfortunately that doesn't seem to work for recursive hosts. Interestingly - the /uimapi/groups/xxxx api call seems to be able to list the csids of everything in a group recursively but I can't seem to work out how the API is getting that list.

    I've managed to capture the query which the api sends sql to grab all alarms:

    select a.*, d.cs_id, m.ci_metric_type, m.ci_id, p.cs_attr_value as primaryipv4address
    from nas_alarms a WITH(NOLOCK)
    LEFT JOIN cm_device d WITH(NOLOCK) on a.dev_id = d.dev_id
    LEFT JOIN cm_configuration_item_metric m WITH(NOLOCK) on a.met_id = m.ci_metric_id
    LEFT JOIN cm_computer_system_attr p WITH(NOLOCK) on d.cs_id = p.cs_id and p.cs_attr_key = 'PrimaryIPV4Address'
    where alarm_manager is null

    Then the /groups/xxx api call sends this sql query to get a recursive list of cs_ids below that group (in this case, group ID 1091):

    WITH nested_group AS
    ( SELECT cm_group.pgrp_id, cm_group.grp_id
    FROM cm_group
    WHERE cm_group.grp_id = 1091
    UNION ALL
    SELECT sub_nested_group.pgrp_id,sub_nested_group.grp_id
    FROM cm_group as sub_nested_group, nested_group
    WHERE nested_group.grp_id = sub_nested_group.pgrp_id )
    SELECT grp_id FROM nested_group

    My issue is now trying to combine these together so it only obtains alarms from cs_ids that are in the second list...