Manual extract of critical alarms from reporting db

Document ID : KB000105643
Last Modified Date : 06/07/2018
Show Technical Document Details
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?
any Spectrum version with Spectrum Report Manager (SRM)  installed.
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`, 
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.

`alarminfo`.`clear_time` , 
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) 

(((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: