DX NetOps Manager

Expand all | Collapse all

Availabilty events in SRM reporting database

  • 1.  Availabilty events in SRM reporting database

    Posted 06-24-2019 04:02 PM
    Edited by Jon Velazquez 06-24-2019 04:33 PM
    A Jasper availability report for a group of devices in a CA Spectrum global collection is showing 100% availability for all devices however for many weeks,however,  the event tab for a few of the devices show 0x10d35 down events during the report period i looked.  The outage editor showing no outages though.  Suspect the events are not showing up in the SRM reporting db.  The reporting db is running.  How do I check the connection where the events from DDMdb go to the SRM reporting DB if I understand correctly?


  • 2.  RE: Availabilty events in SRM reporting database

    Posted 06-25-2019 07:54 AM
    Edited by Silvio Okamoto 06-25-2019 01:50 PM

    Hi Jon,

    First check if the reporting database is synchronized by running this MySQL query:
    SELECT * FROM landscape\G

    Then run this MySQL query to check if there is any unprocessed Model Availability bucket file:
    SELECT COUNT(1) FROM bucketactivitylog WHERE destroy_time IS NULL AND  bucket_table_name LIKE "modelavailbucket%";

    And review the Tomcat log file ($SPECROOT/tomcat/logs/stdout.log - Windows or catalina.out - Linux)



    ------------------------------
    Technical Support Engineer IV
    Broadcom Inc
    ------------------------------



  • 3.  RE: Availabilty events in SRM reporting database

    Posted 06-25-2019 02:05 PM
    Here's the output from the reporting db when I ran it earlier.  Reviewing the stdout.log which starts from June 9th.  The report changed between May 7th and May 12th. 

    mysql> SELECT * FROM landscape\G;
    *************************** 1. row ***************************
    landscape_h: 5242880
    domain_name: itgc2w000145
    avail_sync_time: 2019-06-25 12:56:19
    dev_sync_time: NULL
    int_sync_time: NULL
    process_if_app_events: 0
    spm_sync_time: NULL
    alarm_sync_time: 2019-06-25 12:56:19
    event_sync_time: 2019-06-25 12:56:25
    event_polling: 1
    device_polling: 1
    accountable_sync_time: 2019-06-25 13:12:58
    SS_connected: true
    ArcMgr_connected: true
    ss_version: 10.3.2.000
    ip_address:
    *************************** 2. row ***************************
    landscape_h: 6291456
    domain_name: wdae2acaspectrum1
    avail_sync_time: 2019-06-25 12:59:27
    dev_sync_time: NULL
    int_sync_time: NULL
    process_if_app_events: 0
    spm_sync_time: NULL
    alarm_sync_time: 2019-06-25 12:20:20
    event_sync_time: 2019-06-25 13:07:24
    event_polling: 1
    device_polling: 1
    accountable_sync_time: 2019-06-25 13:12:52
    SS_connected: true
    ArcMgr_connected: true
    ss_version: 10.3.2.000
    ip_address:
    2 rows in set (0.00 sec)

    ERROR:
    No query specified



    mysql> SELECT COUNT(1) FROM bucketactivitylog WHERE destroy_time IS NULL AND bucket_table_name LIKE "modelavailbucket%";
    +----------+
    | COUNT(1) |
    +----------+
    | 0 |
    +----------+
    1 row in set (0.42 sec)


  • 4.  RE: Availabilty events in SRM reporting database

    Posted 06-25-2019 02:33 PM
    Hey Jon,

    If you run an event report for that time period, so you see the events that would have started an outage for that device/devices?
    This will tell us if SRM even got the down events at all.

    Thanks,
    Matt

    ------------------------------
    [Designation]
    [City]
    ------------------------------



  • 5.  RE: Availabilty events in SRM reporting database

    Posted 06-28-2019 04:23 PM
    I ran an event report for the device in question.  Actually ran event report for a global collection group with the device in question.  It turns out the occurenced of the events like 0x10d35 in the Event tab in Spectrum for same date/time range match the Jasper event report for the same date/time range.  It seems that reporting db has the same events that I'm seeing in Spectrum with same timestamps.  Now I just don't understand why the device would show 100% availability for the whole month when the events indicate down conditions multiple times during that same month.


  • 6.  RE: Availabilty events in SRM reporting database

    Posted 06-25-2019 02:42 PM

    Hi Jon,

    The following MySQL queries may display sensitive data, so please attach the output in your current open case 20013432.

    1) Navigate to the $SPECROT/mysql/bin directory
    > cd mysql/bin

    2) Launch the MySQL command prompt
    > ./mysql -uroot -proot reporting (Windows)
    or
    > ./mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting (Linux/Solaris)

    3) Redirect the MySQL output to text file (in the MySQL command prompt):
    mysql> \T device_outage.txt

    4) MySQL command to list the first and the last event in the event table:
    SELECT (MIN(time)) AS FIRST, (MAX(time)) AS LAST FROM event;

    5) MySQL command to display the model_key of the problematic model based on the model_handle: (Replace the 0xfe00891b value with your device's model_handle)
    SELECT * FROM model where WHERE model_h=0xfe00891b\G

    Take note of model_key value to use on the next MySQL command

    6) MySQL command to display the last 5 outages from the modeloutage table of model_key=4768: (Replace the 4768 value with your device's model_key)
    SELECT * FROM modeloutage WHERE model_key=4768 ORDER BY start_time DESC LIMIT 5;

    7) MySQL command to display all outages from the modeloutage table of model_key=4768: (Replace the 4768 value with your device's model_key)
    SELECT * FROM modeloutage WHERE model_key=4768;

    8) MySQL command to display all entries with outage_type=0 from the modeloutage table:
    SELECT * FROM modeloutage WHERE outage_type=0;

    9) MySQL command to display all events of model_key=4768: (Replace the 4768 value with your device's model_key)
    SELECT * FROM event where model_key=4768;

    10) MySQL command - This is the MySQL query that the JasperReports run to generate the Availability report: (Replace the 4768 value with your device's model_key and set the appropriate time range)

    SELECT DISTINCT outagetype.outage_desc AS outage_desc,
    outagetype.outage_type AS Outage_outage_type,
    modeloutage.end_time,
    modeloutage.start_time,
    modeloutage_notes.notes,
    modeloutage.outage_type AS ModelOutage_outage_type,
    model.model_key,
    model.model_name,
    modeltype.mtype_name,
    model.network_address,
    modelclass.mclass_name,
    model.model_h,
    model.destroy_time
    FROM reporting.modelclass modelclass
    INNER JOIN (reporting.modeltype modeltype
    INNER JOIN reporting.model model ON modeltype.mtype_h=model.mtype_h) ON modelclass.model_class=model.model_class AND model.model_key IN ('-1', '4768') and ((model.destroy_time IS NULL)
    OR (model.destroy_time>='2019-01-01 00:00:00'))
    LEFT OUTER JOIN reporting.modeloutage modeloutage ON model.model_key=modeloutage.model_key AND ((modeloutage.start_time IS NULL)
    OR (modeloutage.outage_type=0
    AND modeloutage.start_time<'2019-04-26 15:51:16')
    OR (modeloutage.start_time<'2019-01-01 00:00:00'
    AND ((modeloutage.end_time IS NULL)
    OR (modeloutage.end_time>='2019-04-26 15:51:16')))
    OR ((modeloutage.start_time>='2019-01-01 00:00:00'
    AND modeloutage.start_time<'2019-04-26 15:51:16'))
    OR ((modeloutage.end_time>='2019-01-01 00:00:00'
    AND modeloutage.end_time<'2019-04-26 15:51:16')))
    LEFT OUTER JOIN reporting.outagetype outagetype ON modeloutage.outage_type=outagetype.outage_type
    LEFT OUTER JOIN reporting.modeloutage_notes modeloutage_notes ON modeloutage.model_outage_ID=modeloutage_notes.model_outage_ID
    ORDER BY model.model_key,
    modeloutage.start_time;

    11) MySQL command - This is the MySQL query that the JasperReports run to generate the Alarm report: (Replace the 4768 value with your device's model_key and set the appropriate time range)

    select distinct
    abc.set_time,
    abc.clear_time,
    abc.seconds_to_clear,
    abc.model_name,
    abc.title,
    abc.domain_name,
    abc.condition_id,
    abc.alarm_key,
    abc.landscape_h,
    abc.alerttime,
    abc.`model_key`
    FROM
    (SELECT
    `alarminfo`.`condition_id`,
    `alarminfo`.`landscape_h`,
    `landscape`.`domain_name`,
    `model`.`model_name`,
    `alarminfo`.`set_time`,
    `alarminfo`.`clear_time`,
    `alarminfo`.`alarm_key`,
    `alarmtitle`.`title`,
    `model`.`model_key`,
    TIMESTAMPDIFF(SECOND,alarminfo.set_time ,alarminfo.clear_time) seconds_to_clear,
    TIMESTAMPDIFF(SECOND,alarminfo.set_time ,'2019-01-01 00:00:00') alerttime
    FROM
    `alarminfo`
    INNER JOIN `landscape` ON (`alarminfo`.`landscape_h`=`landscape`.`landscape_h`)
    INNER JOIN `alarmtitle` ON (`alarminfo`.`alarm_title_id`=`alarmtitle`.`alarm_title_id`)
    INNER JOIN `model` ON (`alarminfo`.`model_key`=`model`.`model_key`)
    INNER JOIN alarmcondition on (alarminfo.condition_id=alarmcondition.condition_id)
    where
    (((alarminfo.set_time>='2019-01-01 00:00:00' AND alarminfo.set_time<'2019-04-26 00:00:00') AND
    (alarminfo.clear_time>='2019-01-01 00:00:00' AND alarminfo.clear_time<'2019-04-26 00:00:00'))
    OR
    (alarminfo.set_time<'2019-01-01 00:00:00' and (alarminfo.clear_time>='2019-01-01 00:00:00' AND alarminfo.clear_time<'2019-04-26 00:00:00'))
    OR
    ((alarminfo.set_time>='2019-01-01 00:00:00' AND alarminfo.set_time<'2019-04-26 00:00:00') AND
    (alarminfo.clear_time IS NULL OR alarminfo.clear_time>'2019-04-26 00:00:00'))) AND
    (model.model_key IN ('-1', '4768')) AND
    (alarmcondition.condition_name IN ('Critical')) AND
    ( alarminfo.clear_time is null or TIMESTAMPDIFF(SECOND,alarminfo.set_time ,alarminfo.clear_time)>60*0 )
    order by `alarminfo`.`landscape_h`, `alarminfo`.`condition_id`,`alarminfo`.`set_time`)abc;


    12) End MySQL output redirection in the MySQL command prompt:
    mysql> \t



    ------------------------------
    Technical Support Engineer IV
    Broadcom Inc
    ------------------------------



  • 7.  RE: Availabilty events in SRM reporting database

    Posted 06-25-2019 05:15 PM

    Silvio,

     

    Loading that output onto the case.

     

    Thanks

    Jon V.

     

     

     

     






  • 8.  RE: Availabilty events in SRM reporting database

    Posted 06-26-2019 10:14 AM
    Silvio, the mysql query results have been uploaded to the case for review.


  • 9.  RE: Availabilty events in SRM reporting database

    Posted 06-26-2019 10:37 AM
    Let me review the attached file.

    ------------------------------
    Technical Support Engineer IV
    Broadcom Inc
    ------------------------------



  • 10.  RE: Availabilty events in SRM reporting database

    Posted 06-26-2019 11:02 AM
    I don't see the output of this query in the attached file:
    8) MySQL command to display all entries with outage_type=0 from the modeloutage table:
    SELECT * FROM modeloutage WHERE outage_type=0;

    ------------------------------
    Technical Support Engineer IV
    Broadcom Inc
    ------------------------------



  • 11.  RE: Availabilty events in SRM reporting database

    Posted 06-26-2019 11:29 AM

    Uploaded query output in Type0.zip.

     

    Jon V.

     

     






  • 12.  RE: Availabilty events in SRM reporting database

    Posted 07-09-2019 09:46 AM
    Updated case.  I noticed that the SRM and DDMdb have the same events however the outage editor is not showing any outages for June/July 2019 dates for the preselected month range.  It seems the outage editor is not seeing the outage events.


  • 13.  RE: Availabilty events in SRM reporting database

    Posted 07-09-2019 09:46 AM
    The SRM and DDMdb have the same outage events when I do a comparison of 10d35 events from both databases for June/July for our device VEAU7ASA1.  However, the outage editor does not display any outages for the device VEAU7ASA1.  It seems the outage editor is not seeing what it needs to see.  How do I debug further.  I've updated the case.


  • 14.  RE: Availabilty events in SRM reporting database

    Posted 07-16-2019 10:12 AM
    Discovered actually that after our Spectrum upgrade on April 29 there seems to be no outage data for device availability in the modeloutage SRM table.   Although the table shows entries for July for Interface mostly.  I ran several device availability reports on large Cisco switch and router global collections for past 2 weeks and all devices show 100% availability across the board which is highly unlikely.  It seems whatever the mechanism to recognize outages or put outage info in the modeloutage SRM table is not functioning or impaired.


  • 15.  RE: Availabilty events in SRM reporting database

    Posted 07-16-2019 11:10 AM
    When running a Interface (not Device) availability report on an interface global collection group for last 7 days or 2 weeks it actually shows availability less than 100%, along with downtime and number of outages.  It seems the SRM modeloutage table has valid interface outage entries but is not getting Device outage entries.