Manual extract of critical alarms from reporting db

Document ID : KB000105643
Last Modified Date : 06/07/2018
Show Technical Document Details
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?  
Question:
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.
Answer:
To manually extract all critical alarms for a specific interval time you can run the following select SQL on the reporting database.

1) open a bash -login command prompt 
2) cd mysql/bin 
3) ./mysql -uroot -proot reporting (to login to the mysql reporting db) 

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:
see "Sample reporting DB queries" at:
https://docops.ca.com/ca-spectrum/10-2-3/en/managing-client-applications/report-manager/report-manager-db-schema/sample-reporting-db-queries