CABI - Incorrect Availability report for some Global Collections

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;

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)