Hi Jon,
The following MySQL queries may display sensitive data, so please attach the output in your current open case 20013432.
1) Navigate to the $SPECROT/mysql/bin directory
> cd mysql/bin
2) Launch the MySQL command prompt
> ./mysql -uroot -proot reporting (Windows)
or
> ./mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting (Linux/Solaris)
3) Redirect the MySQL output to text file (in the MySQL command prompt):
mysql> \T device_outage.txt
4) MySQL command to list the first and the last event in the event table:
SELECT (MIN(time)) AS FIRST, (MAX(time)) AS LAST FROM event;
5) MySQL command to display the model_key of the problematic model based on the model_handle: (Replace the 0xfe00891b value with your device's model_handle)
SELECT * FROM model where WHERE model_h=0xfe00891b\G
Take note of model_key value to use on the next MySQL command
6) MySQL command to display the last 5 outages from the modeloutage table of model_key=4768: (Replace the 4768 value with your device's model_key)
SELECT * FROM modeloutage WHERE model_key=4768 ORDER BY start_time DESC LIMIT 5;
7) MySQL command to display all outages from the modeloutage table of model_key=4768: (Replace the 4768 value with your device's model_key)
SELECT * FROM modeloutage WHERE model_key=4768;
8) MySQL command to display all entries with outage_type=0 from the modeloutage table:
SELECT * FROM modeloutage WHERE outage_type=0;
9) MySQL command to display all events of model_key=4768: (Replace the 4768 value with your device's model_key)
SELECT * FROM event where model_key=4768;
10) MySQL command - This is the MySQL query that the JasperReports run to generate the Availability report: (Replace the 4768 value with your device's model_key and set the appropriate time range)
SELECT DISTINCT outagetype.outage_desc AS outage_desc,
outagetype.outage_type AS Outage_outage_type,
modeloutage.end_time,
modeloutage.start_time,
modeloutage_notes.notes,
modeloutage.outage_type AS ModelOutage_outage_type,
model.model_key,
model.model_name,
modeltype.mtype_name,
model.network_address,
modelclass.mclass_name,
model.model_h,
model.destroy_time
FROM reporting.modelclass modelclass
INNER JOIN (reporting.modeltype modeltype
INNER JOIN reporting.model model ON modeltype.mtype_h=model.mtype_h) ON modelclass.model_class=model.model_class AND model.model_key IN ('-1', '4768') and ((model.destroy_time IS NULL)
OR (model.destroy_time>='2019-01-01 00:00:00'))
LEFT OUTER JOIN reporting.modeloutage modeloutage ON model.model_key=modeloutage.model_key AND ((modeloutage.start_time IS NULL)
OR (modeloutage.outage_type=0
AND modeloutage.start_time<'2019-04-26 15:51:16')
OR (modeloutage.start_time<'2019-01-01 00:00:00'
AND ((modeloutage.end_time IS NULL)
OR (modeloutage.end_time>='2019-04-26 15:51:16')))
OR ((modeloutage.start_time>='2019-01-01 00:00:00'
AND modeloutage.start_time<'2019-04-26 15:51:16'))
OR ((modeloutage.end_time>='2019-01-01 00:00:00'
AND modeloutage.end_time<'2019-04-26 15:51:16')))
LEFT OUTER JOIN reporting.outagetype outagetype ON modeloutage.outage_type=outagetype.outage_type
LEFT OUTER JOIN reporting.modeloutage_notes modeloutage_notes ON modeloutage.model_outage_ID=modeloutage_notes.model_outage_ID
ORDER BY model.model_key,
modeloutage.start_time;
11) MySQL command - This is the MySQL query that the JasperReports run to generate the Alarm report: (Replace the 4768 value with your device's model_key and set the appropriate time range)
select distinct
abc.set_time,
abc.clear_time,
abc.seconds_to_clear,
abc.model_name,
abc.title,
abc.domain_name,
abc.condition_id,
abc.alarm_key,
abc.landscape_h,
abc.alerttime,
abc.`model_key`
FROM
(SELECT
`alarminfo`.`condition_id`,
`alarminfo`.`landscape_h`,
`landscape`.`domain_name`,
`model`.`model_name`,
`alarminfo`.`set_time`,
`alarminfo`.`clear_time`,
`alarminfo`.`alarm_key`,
`alarmtitle`.`title`,
`model`.`model_key`,
TIMESTAMPDIFF(SECOND,alarminfo.set_time ,alarminfo.clear_time) seconds_to_clear,
TIMESTAMPDIFF(SECOND,alarminfo.set_time ,'2019-01-01 00:00:00') 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>='2019-01-01 00:00:00' AND alarminfo.set_time<'2019-04-26 00:00:00') AND
(alarminfo.clear_time>='2019-01-01 00:00:00' AND alarminfo.clear_time<'2019-04-26 00:00:00'))
OR
(alarminfo.set_time<'2019-01-01 00:00:00' and (alarminfo.clear_time>='2019-01-01 00:00:00' AND alarminfo.clear_time<'2019-04-26 00:00:00'))
OR
((alarminfo.set_time>='2019-01-01 00:00:00' AND alarminfo.set_time<'2019-04-26 00:00:00') AND
(alarminfo.clear_time IS NULL OR alarminfo.clear_time>'2019-04-26 00:00:00'))) AND
(model.model_key IN ('-1', '4768')) AND
(alarmcondition.condition_name IN ('Critical')) AND
( alarminfo.clear_time is null or TIMESTAMPDIFF(SECOND,alarminfo.set_time ,alarminfo.clear_time)>60*0 )
order by `alarminfo`.`landscape_h`, `alarminfo`.`condition_id`,`alarminfo`.`set_time`)abc;
12) End MySQL output redirection in the MySQL command prompt:
mysql> \t
------------------------------
Technical Support Engineer IV
Broadcom Inc
------------------------------
Original Message:
Sent: 06-25-2019 02:04 PM
From: Jon Velazquez
Subject: Availabilty events in SRM reporting database
Here's the output from the reporting db when I ran it earlier. Reviewing the stdout.log which starts from June 9th. The report changed between May 7th and May 12th.
mysql> SELECT * FROM landscape\G;
*************************** 1. row ***************************
landscape_h: 5242880
domain_name: itgc2w000145
avail_sync_time: 2019-06-25 12:56:19
dev_sync_time: NULL
int_sync_time: NULL
process_if_app_events: 0
spm_sync_time: NULL
alarm_sync_time: 2019-06-25 12:56:19
event_sync_time: 2019-06-25 12:56:25
event_polling: 1
device_polling: 1
accountable_sync_time: 2019-06-25 13:12:58
SS_connected: true
ArcMgr_connected: true
ss_version: 10.3.2.000
ip_address:
*************************** 2. row ***************************
landscape_h: 6291456
domain_name: wdae2acaspectrum1
avail_sync_time: 2019-06-25 12:59:27
dev_sync_time: NULL
int_sync_time: NULL
process_if_app_events: 0
spm_sync_time: NULL
alarm_sync_time: 2019-06-25 12:20:20
event_sync_time: 2019-06-25 13:07:24
event_polling: 1
device_polling: 1
accountable_sync_time: 2019-06-25 13:12:52
SS_connected: true
ArcMgr_connected: true
ss_version: 10.3.2.000
ip_address:
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> SELECT COUNT(1) FROM bucketactivitylog WHERE destroy_time IS NULL AND bucket_table_name LIKE "modelavailbucket%";
+----------+
| COUNT(1) |
+----------+
| 0 |
+----------+
1 row in set (0.42 sec)
Original Message:
Sent: 06-25-2019 07:54 AM
From: Silvio Okamoto
Subject: Availabilty events in SRM reporting database
Hi Jon,
First check if the reporting database is synchronized by running this MySQL query:
SELECT * FROM landscape\G
Then run this MySQL query to check if there is any unprocessed Model Availability bucket file:
SELECT COUNT(1) FROM bucketactivitylog WHERE destroy_time IS NULL AND bucket_table_name LIKE "modelavailbucket%";
And review the Tomcat log file ($SPECROOT/tomcat/logs/stdout.log - Windows or catalina.out - Linux)
------------------------------
Technical Support Engineer IV
Broadcom Inc
Original Message:
Sent: 06-24-2019 04:02 PM
From: Jon Velazquez
Subject: Availabilty events in SRM reporting database
A Jasper availability report for a group of devices in a CA Spectrum global collection is showing 100% availability for all devices however for many weeks,however, the event tab for a few of the devices show 0x10d35 down events during the report period i looked. The outage editor showing no outages though. Suspect the events are not showing up in the SRM reporting db. The reporting db is running. How do I check the connection where the events from DDMdb go to the SRM reporting DB if I understand correctly?