Availabilty events in SRM reporting database

[Jon Velazquez]

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?

[Silvio Okamoto]

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

[Jon Velazquez]

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)

[Matthew Gay]

Hey Jon,

If you run an event report for that time period, so you see the events that would have started an outage for that device/devices?
This will tell us if SRM even got the down events at all.

Thanks,
Matt

------------------------------
[Designation]
[City]
------------------------------

[Jon Velazquez]

I ran an event report for the device in question.  Actually ran event report for a global collection group with the device in question.  It turns out the occurenced of the events like 0x10d35 in the Event tab in Spectrum for same date/time range match the Jasper event report for the same date/time range.  It seems that reporting db has the same events that I'm seeing in Spectrum with same timestamps.  Now I just don't understand why the device would show 100% availability for the whole month when the events indicate down conditions multiple times during that same month.

[Silvio Okamoto]

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

[Jon Velazquez]

Silvio,

 

Loading that output onto the case.

 

Thanks

Jon V.

 

 

 

 

[Jon Velazquez]

Silvio, the mysql query results have been uploaded to the case for review.

[Silvio Okamoto]

Let me review the attached file.

------------------------------
Technical Support Engineer IV
Broadcom Inc
------------------------------

[Silvio Okamoto]

I don't see the output of this query in the attached file:
8) MySQL command to display all entries with outage_type=0 from the modeloutage table:
SELECT * FROM modeloutage WHERE outage_type=0;

------------------------------
Technical Support Engineer IV
Broadcom Inc
------------------------------

[Jon Velazquez]

Uploaded query output in Type0.zip.

 

Jon V.

 

 

[Jon Velazquez]

Updated case.  I noticed that the SRM and DDMdb have the same events however the outage editor is not showing any outages for June/July 2019 dates for the preselected month range.  It seems the outage editor is not seeing the outage events.

[Jon Velazquez]

The SRM and DDMdb have the same outage events when I do a comparison of 10d35 events from both databases for June/July for our device VEAU7ASA1.  However, the outage editor does not display any outages for the device VEAU7ASA1.  It seems the outage editor is not seeing what it needs to see.  How do I debug further.  I've updated the case.

[Jon Velazquez]

Discovered actually that after our Spectrum upgrade on April 29 there seems to be no outage data for device availability in the modeloutage SRM table.   Although the table shows entries for July for Interface mostly.  I ran several device availability reports on large Cisco switch and router global collections for past 2 weeks and all devices show 100% availability across the board which is highly unlikely.  It seems whatever the mechanism to recognize outages or put outage info in the modeloutage SRM table is not functioning or impaired.

[Jon Velazquez]

When running a Interface (not Device) availability report on an interface global collection group for last 7 days or 2 weeks it actually shows availability less than 100%, along with downtime and number of outages.  It seems the SRM modeloutage table has valid interface outage entries but is not getting Device outage entries.