Spectrum MySQL Query to Identify Highest Event counts in the Report Manager Database

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

Spectrum MySQL Query to Identify Highest Event counts in the Report Manager Database

Environment

Release : Any

Component : Spectrum Report Manager

Resolution

NOTE: Starting from DX NetOps Spectrum 21.2.4, the default root password for MySql is "MySqlR00t". For DX NetOps Spectrum versions prior to 21.2.4, the default root password is "root". In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.

Users can run through the following commands in order to generate a list of the highest event counts within the SRM database for a specific time period.

 

- Log into the system as the user that owns the Spectrum installation

 

- If on Windows, start a bash shell by running "bash -login"

 

- cd to the $SPECROOT/mysql/bin directory and enter the following command to log into mysql:

 

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> srmdbapi -A

 

- Enter the following commands at the mysql prompt. Adjust the dates highlighted below to meet your needs.

 

SELECT de.title, de.type_hex, COUNT(1) as event_count FROM v_fact_event e, v_dim_event de WHERE e.type_dec = de.type_dec AND e.time BETWEEN '2020-01-01 00:00:00' AND '2020-02-24 23:59:59' GROUP BY de.title ORDER BY event_count DESC LIMIT 20;

 

quit;

An example of the output is shown below. NOTE: This particular query is for the time frame of 2/1/20 and 2/24/20, users may need to adjust these parameters for the desired output.