Manual extract of critical alarms from the Spectrum Report Manager (SRM) reporting db
Article ID: 105643
Updated On:
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?
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
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 -proot 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`;
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 -proot 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.
Feedback
Powered by
