Finding out the top occurring Events in CA Spectrum

Document ID : KB000068015
Last Modified Date : 02/01/2019
Show Technical Document Details
Introduction:
There are various scenarios that sometimes occur that cause Spectrum to slow down due to performance degradation.  This can be due to devices flooding Spectrum with traps or network faults resulting in an excessively large number of alarms being generated. In these cases, as part of the troubleshooting process, it's good to find out what events have been generated the most so as to help identify the root cause of the problem.
 
Question:

How can the top occurring events be identified for a specific time period?

Environment:
Spectrum 10.x
Answer:
You can find out which are the top events generated by querying the DDM db (Archive Manager Data base) as follows;

1.     Login to the SpectroSERVER command line (under Windows, run bash -login)

2.    cd $SPECROOT/mysql/bin

3.     Enter the following command to log into the Archive Manager MySQL DB: 

Under Linux/Solaris;
./mysql --defaults-file=../my-spectrum.cnf -uroot -proot ddmdb 

Under Windows;
./mysql -uroot -proot ddmdb

4.     When the MySQL prompt appears, to specify a particular date range (In this example just after midnight on 30th Dec through to midnight on 2nd Jan);
SELECT hex(type), COUNT(*) AS cnt FROM event WHERE utime >= UNIX_TIMESTAMP("2018-12-30 00:00:00") AND utime <= UNIX_TIMESTAMP("2019-01-02 23:59:59") GROUP BY type ORDER BY cnt DESC LIMIT 10;
 
 Or, the last 24 hours worth of events, run;
SELECT hex(type), COUNT(*) AS cnt FROM event WHERE (utime > (unix_timestamp(curdate()) - 1*24*3600 ))

This will list out the top 10 occurring event codes (in Hexadecimal) and the number of times they've occurred in this time range. You can then check to see what events they are by search for them in the Event Configuration (under Spectrum OneClick client -> Tools menu -> Utilities).

5. Type quit to exit MySQL
Additional Information:
The following Guide has further information related to this and Best practices in maintaining the Reporting and Archive Manager DBs;
Best practices to maintain the size of reporting database (SRM -Spectrum Report Manager)