Manual extract of critical alarms from the Spectrum Report Manager (SRM) reporting db

Products

• CA Spectrum

Issue/Introduction

Management needs to have the list of all critical alarms generated in last 3 months.  How to obtain this list without using Spectrum Jaspersoft Report?  

How can all critical alarms generated in a specific interval time be extracted from the reporting db?

Environment

Any Spectrum version with Spectrum Report Manager (SRM)  installed.

Resolution

NOTE: In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.

To manually extract all critical alarms for a specific interval time you can run the following select SQL on the reporting database.

1. Log into the SRM system as the user that owns the Spectrum installation

2. If on Windows, start a bash shell by running "bash -login"

3. cd to the $SPECROOT/mysql/bin directory and enter the following command to log into mysql:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> reporting

4. Enter the following command at the mysql prompt, changing the "set_time" parameters to meet your needs:

SELECT `landscape`.`domain_name`, 
`alarminfo`.`alarm_key`, 
`alarminfo`.`landscape_h`, 
`model`.`model_name`, 
`alarminfo`.`condition_id`, 
`alarminfo`.`set_time` 
FROM ((`reporting`.`alarminfo` `alarminfo` 
INNER JOIN `reporting`.`landscape` `landscape` 
ON `alarminfo`.`landscape_h` = `landscape`.`landscape_h`) 
INNER JOIN `reporting`.`model` `model` 
ON `alarminfo`.`model_key` = `model`.`model_key`) WHERE (`alarminfo`.`set_time` >={ts '2018-04-01 00:00:00'} AND `alarminfo`.`set_time`<{ts '2018-07-01 00:00:00'} AND `alarminfo`.`condition_id` = 3); 

The condition_id = 3 identifies the Critical alarms, You will need to specify the interval time you want in the WHERE statement (in my sample from 1 Apr to 1 July of 2018). 

You can also use the following more complex select extracted from the Jaspersoft  Alarm-Log all report  that considers only the alarms outages longer than 5 minutes.

SELECT 
`alarminfo`.`condition_id`, 
`alarminfo`.`landscape_h`, 
`landscape`.`domain_name`, 
`model`.`model_name`, 
`alarminfo`.`set_time`, 
`alarminfo`.`clear_time` , 
`alarminfo`.`alarm_key`, 
`alarmtitle`.`title`, 
TIMESTAMPDIFF(SECOND,alarminfo.set_time ,alarminfo.clear_time) seconds_to_clear, 
TIMESTAMPDIFF(SECOND,alarminfo.set_time ,"2018-04-01 00:00:00.0") alerttime 

FROM alarminfo 
INNER JOIN landscape ON (alarminfo.landscape_h=landscape.landscape_h) 
INNER JOIN alarmtitle ON (alarminfo.alarm_title_id=alarmtitle.alarm_title_id) 
INNER JOIN model ON (alarminfo.model_key=model.model_key) 
INNER JOIN alarmcondition on (alarminfo.condition_id=alarmcondition.condition_id) 

WHERE
(((alarminfo.set_time>="2018-04-01 00:00:00.0" AND alarminfo.set_time<"2018-07-01 00:00:00.0") AND 
(alarminfo.clear_time>="2018-04-01 00:00:00.0" AND alarminfo.clear_time<"2018-07-01 00:00:00.0")) OR 
(alarminfo.set_time<"2018-04-01 00:00:00.0" and (alarminfo.clear_time>="2018-04-01 00:00:00.0" AND alarminfo.clear_time<"2018-07-01 00:00:00.0")) OR 
((alarminfo.set_time>="2018-04-01 00:00:00.0" AND alarminfo.set_time<"2018-07-01 00:00:00.0") AND (alarminfo.clear_time IS NULL OR alarminfo.clear_time>"2018-07-01 00:00:00.0"))) AND 
(alarmcondition.condition_name IN ("Critical")) AND ( alarminfo.clear_time is null or TIMESTAMPDIFF(SECOND,alarminfo.set_time ,alarminfo.clear_time)>60*5 )
order by `alarminfo`.`landscape_h`, `alarminfo`.`condition_id`,`alarminfo`.`set_time`; 

Additional Information

Please reference the "Sample reporting DB queries" section of the documentation for additional examples.