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