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.

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)

Environment

Release : 20.2

Component : Jaspersoft for CA Spectrum

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)