SLA Service Names are missing from the CABI/JaspeReports Input Controls

Products

Network Observability Spectrum

Issue/Introduction

The customer suddenly lost 300 GB of MySQL data overnight and was unable to restore it from backups.
The SRM database was initialized, but only some Service Manager reports could be run because the others were not being populated in CABI.

Environment

DX NetOps Spectrum: Any version

Cause

The SLA models are missing in the reporting.model table.

The following MySQL query is returning just a few entries:

mysql> SELECT slaName, HEX(slaMH) FROM sm_slas INNER JOIN model m ON slaMH = m.model_h AND slaMH NOT IN (1) AND slaDestroyTime is NULL ORDER BY slaName;

There are only a few SLA names in the model table:

mysql> select * from model where mtype_h=0x4500002;

These are the SLA names from the Service Manager sm_slas table:

mysql> select slaName , HEX(slaMH) from sm_slas;

Resolution

Customer is not willing to initialize the reporting db as they already did it and the issue showed up.

The workaround is renaming the SLA names, synchronize with SRM, then revert the change and synchronize with SRM again.

Here is a CLI script (Bash Script) to accomplish it:

1) Create the SLA_orig_name.txt file under the $SPECROOT/vnmsh/ directory with the following entries:

model_handle model_name

Example:

0x10001f9 SLA_Name1
0x10001fe SLA_Name2

To show SLA names in CLI (Command Line Interface):

CLIMNAMEWIDTH=200; export CLIMNAMEWIDTH

./show models | grep 0x4500002

0x10001f9 SLA_Name1 0x4500002 SM_SLA
0x10001fe SLA_Name2 0x4500002 SM_SLA

MySQL query to display the model_handle and model_name of SLA:

MySQL query to generate the csv output file:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p -e 'select CONCAT("0x",HEX(slaMH)),slaName from reporting.sm_slas;' | sed 's/\t/ /g' > /tmp/out.csv

You will need to teak the output file and remove the first lines.

cat /tmp/out.csv

CONCAT("0x",HEX(slaMH)) slaName
0x0 All SLAs
0x1 No SLA
0x10001F9 SLA_Name1
0x10001FE SLA_Name2

2) Create the SLA_new_name.txt file under the $SPECROOT/vnmsh/ directory with the following entries:

model_handle model_name

Example:

0x10001f9 _SLA_Name1
0x10001fe _SLA_Name2

MySQL query do display the model_handle and model_name with prefix ("_") of SLA:

MySQL query to generate the csv output file:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p -e 'select CONCAT("0x",HEX(slaMH)),CONCAT("_",slaName) from reporting.sm_slas;' | sed 's/\t/ /g' > /tmp/out.csv

You will need to teak the output file and remove the first lines.

cat /tmp/out.csv

CONCAT("0x",HEX(slaMH)) CONCAT("_",slaName)
0x0 _All SLAs
0x1 _No SLA
0x10001F9 _SLA_Name1
0x10001FE _SLA_Name2

3) Create the renameSLA.sh file under the $SPECROOT/vnmsh/ directory with the following entries (CLI script to rename model):

#!/bin/bash

export CLISESSID
CLIPATH=/usr/Spectrum/vnmsh
model_name_ID=0x1006e

$CLIPATH/connect
# For each model_handle in the file, rename the model_name

while read line; do
  model_handle=$(echo $line | cut -d ' ' -f 1)
  model_name=$(echo $line | cut -d ' ' -f 2)
  echo "Renaming model_handle = " $model_handle
  $CLIPATH/update mh=$model_handle attr=$model_name_ID,val=$model_name
done

$CLIPATH/disconnect

exit 0

4) Change the file permission to:

chmod 775 renameSLA.sh

5) Run the renameSLA.sh script to rename the SLA name temporarily:

./renameSLA.sh < SLA_new_name.txt

6) Toggle the "Monitor?" checkbox in OneClick Administration --> Report Manager --> Spectrum Status page.

https://knowledge.broadcom.com/external/article?articleNumber=210890

If you want the SRM to update the reporting database immediately you will need to uncheck (disable) the Monitor? checkbox, save the change, and then check back (enable) the Monitor? checkbox and save the change.

7) Run the following MySQL query on the SRM to confirm the SLA names have been renamed:

mysql> select HEX(model_h), model_name from model where mtype_h=0x4500002;

+--------------+------------+
| HEX(model_h) | model_name |
+--------------+------------+
| 10001F9 | _SLA_Name1 |
| 10001FE | _SLA_Name2 |
+--------------+------------+
2 rows in set (0.00 sec)

8) Run the renameSLA.sh script to rename back the SLA name:

./renameSLA.sh < SLA_orig_name.txt

9) Toggle the "Monitor?" checkbox in OneClick Administration --> Report Manager --> Spectrum Status page.

10) Run the following MySQL query on the SRM to confirm the SLA names have been restored to their original names:

mysql> select HEX(model_h), model_name from model where mtype_h=0x4500002;

+--------------+------------+
| HEX(model_h) | model_name |
+--------------+------------+
| 10001F9 | SLA_Name1 |
| 10001FE | SLA_Name2 |
+--------------+------------+
2 rows in set (0.00 sec)

11) Run the following MySQL query to confirm the issue was fixed:

mysql> SELECT slaName, slaMH FROM sm_slas INNER JOIN model m ON slaMH = m.model_h AND slaMH NOT IN (1) ORDER BY slaName;

Additional Information

Here are the steps to dump and restore the reporting database in case initializing the reporting db did not help:

a) Stop the Spectrum Tomcat service to stopping writing to the reporting db.

$SPECROOT/tomcat/bin/stopTomcat.sh

b) Dump the reporting db:

cd $SPECROOT/mysq/bin/

./mysqldump --defaults-file=../my-spectrum.cnf -uroot -p<password> --opt --routines --ignore-table=reporting.v_active_user_model --ignore-table=reporting.v_alarm_activity --ignore-table=reporting.v_ncm_config_diff --ignore-table=reporting.v_security_string_accessibility_by_landscape --ignore-table=reporting.v_user_report_security --compress --databases reporting > /tmp/dump_reporting.sql

Note: Ensure you have enough free disk space to save the dump under the /tmp/ directory (/tmp/dump_reporting.sql)

c) Initialize the reporting database:

cd $SPECROOT/bin/

./RpmgrInitializeLandscape.bat root <password> -initHist 45 -all

Ensure the scrip successfully truncated the tables from reporting db.

d) Start the Spectrum Tomcat service.

$SPECROOT/tomcat/bin/startTomcat.sh

e) Watch the SRM populating the reporting db. It will start populating the model / devicemodel /virtualdevicemodel tables.

mysql> select (select count(*) from model) as model,(select count(*) from devicemodel) as devicemodel,(select count(*) from interfacemodel) as interfacemodel,(select count(*) from virtualinterfacemodel) as virtualinterfacemodel,(select count(*) from globalcollection) as globalcollection,(select count(*) from event) as event,(select count(*) from alarminfo) as alarminfo,(MAX(time)) AS Lastest_Event_Record_Time FROM event;

Also watch SRM populating the SLA models:

mysql> select count(*) from model where mtype_h=0x4500002;

f) Once the model table is successfully populated, check the CABI/JasperReports MySQL query output:

mysql> SELECT slaName, HEX(slaMH) FROM sm_slas INNER JOIN model m ON slaMH = m.model_h AND slaMH NOT IN (1) AND slaDestroyTime is NULL ORDER BY slaName;

=============================================

How to restore the reporting db from the dump file:

g) Stop the Spectrum Tomcat service to stopping writing to the reporting db.

$SPECROOT/tomcat/bin/stopTomcat.sh

h) Restore the data from the dump file:

cd $SPECROOT/mysq/bin/

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<password> reporting < /tmp/dump_reporting.sql

i) Start the Spectrum Tomcat service.

$SPECROOT/tomcat/bin/startTomcat.sh