search cancel

CABI - Incorrect Availability report for some Global Collections

book

Article ID: 212386

calendar_today

Updated On:

Products

CA Spectrum DX NetOps

Issue/Introduction

When generating the Availability report for some global collections, the data is being displayed incorrectly.

 

Environment

Release : 20.2

Component : Jaspersoft for CA Spectrum

Cause

There are ongoing outages.

Enabled MySQL General Query Logging to capture the MySQL query that JasperReports is running to generate the Availability report.

https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=48011

This is the MySQL query the JasperReports runs to display the outages of indivitual devices:

SELECT outagetype.outage_desc,
       outagetype.outage_type AS Outage_outage_type,
       modeloutage.outage_type AS ModelOutage_outage_type,
       modeloutage.end_time,
       modeloutage.start_time,
       model.model_key,
       model.model_name,
       modeltype.mtype_name,
       model.network_address,
       modelclass.mclass_name,
       model.model_h,
       modeloutage_notes.notes
FROM reporting.modeltype modeltype
         INNER JOIN reporting.model model ON modeltype.mtype_h=model.mtype_h AND model.model_key=9372
        INNER JOIN reporting.modelclass modelclass ON model.model_class=modelclass.model_class
       LEFT OUTER JOIN reporting.modeloutage modeloutage ON model.model_key=modeloutage.model_key
    AND ((modeloutage.start_time IS NULL)
       OR (modeloutage.outage_type=0)
       OR (modeloutage.start_time<'2021-02-01 00:00:00'
           AND ((modeloutage.end_time IS NULL)
                OR (modeloutage.end_time>='2021-03-01 00:00:00')))
       OR ((modeloutage.start_time>='2021-02-01 00:00:00'
            AND modeloutage.start_time<'2021-03-01 00:00:00'))
       OR ((modeloutage.end_time>='2021-02-01 00:00:00'
            AND modeloutage.end_time<'2021-03-01 00:00:00')))
      INNER 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 modeloutage.start_time;


+-------------+--------------------+-------------------------+---------------------+---------------------+-----------+------------+-------------+----------------------------+-------------+---------+-------+
| outage_desc | Outage_outage_type | ModelOutage_outage_type | end_time            | start_time          | model_key | model_name | mtype_name  | network_address            | mclass_name | model_h | notes |
+-------------+--------------------+-------------------------+---------------------+---------------------+-----------+------------+-------------+----------------------------+-------------+---------+-------+
| Initial     |                  0 |                       0 | 2020-10-16 11:06:41 | 2020-10-16 11:06:41 |      9372 | XXXXXXXXX  | L+GyrRouter | fda0::160:1c:6400:8061:6b9 | SNMP        | 2106185 | NULL  |
| Unplanned   |                  1 |                       1 | 2021-02-01 03:26:26 | 2021-01-31 22:40:32 |      9372 | XXXXXXXXX  | L+GyrRouter | fda0::160:1c:6400:8061:6b9 | SNMP        | 2106185 | NULL  |
| Unplanned   |                  1 |                       1 | 2021-02-01 06:42:13 | 2021-02-01 04:58:18 |      9372 | XXXXXXXXX  | L+GyrRouter | fda0::160:1c:6400:8061:6b9 | SNMP        | 2106185 | NULL  |
| Unplanned   |                  1 |                       1 | NULL                | 2021-02-01 08:04:49 |      9372 | XXXXXXXXX  | L+GyrRouter | fda0::160:1c:6400:8061:6b9 | SNMP        | 2106185 | NULL  |
+-------------+--------------------+-------------------------+---------------------+---------------------+-----------+------------+-------------+----------------------------+-------------+---------+-------+
4 rows in set (0.00 sec)

 

1. Run the following MySQL query to confirm the model_name of a particular model_key value:

mysql> select * from model where model_key=9372\G
*************************** 1. row ***************************
       model_key: 9372
         model_h: 2106185
     model_name: XXXXXXXXX
         mtype_h: 4294901760
     model_class: 14
 network_address: fda0::160:1c:6400:8061:6b9
first_event_time: 2020-10-16 11:06:41
     landscape_h: 2097152
    destroy_time: 2021-01-25 14:51:23
 security_string: ADMIN|OPE_FAN
 isClusterEntity: 2
       tenant_id: -1
SDM_Host_Address: 0.0.0.0

1 row in set (0.00 sec)

 

2. Run the following MySQL query to confirm the ongoing outage (it is not ended yet):

mysql> SELECT * FROM modeloutage WHERE model_key=9372 and end_time is null\G
*************************** 1. row ***************************
     model_outage_ID: 1835911
           model_key: 9372
         landscape_h: 2097152
          start_time: 2021-02-01 08:04:49
            end_time: NULL
         outage_type: 1
  zero_length_outage: 0
     start_event_key: 68104420
       end_event_key: NULL
    legacy_outage_id: NULL
legacy_outage_source: NULL
   last_updated_time: 2021-02-01 08:04:49

1 row in set (0.00 sec)

Resolution

Run the following MySQL command to end the ongoing outage:

mysql> UPDATE modeloutage SET end_time='2021-02-01 08:05:18' where model_outage_ID=1835911;

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

Run the following MySQL to list the devices that belong to a particular Global Collection:

SELECT entity.entity_name, model.model_key, entity.create_time, entity.destroy_time, model.network_address FROM entity, entitygroup, entitygroupentity, model WHERE entity.entity_ID=entitygroupentity.entity_ID AND entitygroupentity.entity_group_ID=entitygroup.entity_group_ID AND entity.current_model_key=model.model_key AND entitygroup.entity_group_name='<Global Collection name>';

 

For each model_key from the above query, run the following MySQL query to see if there any ongoing outage that needs to be manually ended:

mysql> SELECT * FROM modeloutage WHERE model_key=<XXXX> and end_time is null\G

 

If there is an ongoing outage, you have to manually end it, by running the following MySQL command line, supply the correct end_time and model_outage_ID:

mysql> UPDATE modeloutage SET end_time='2021-02-01 08:05:18' where model_outage_ID=1835911;

 

###### [Possible Root Cause] ######
The Max Log Size value is too low. Increased it to 2000000.

For further information review the following KB article:

Title:  CA Spectrum Event Storage Best Practices
https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=20967

Additional Information

How to connecto to MySQL command prompt:

1. Open a bash shell on the OneClick + SRM machine

2. Navigate to the $SPECROOT/mysql/bin/ directory

$ cd mysql/bin

3. Run the following syntax to connect to the MySQL command line:

$ ./mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting -A (Linux)
$ ./mysql.exe -uroot -proot reporting (Windows)

Attachments