When generating the Availability report for some global collections, the data is being displayed incorrectly.
Release : 20.2
Component : Jaspersoft for CA Spectrum
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)
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
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)