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

Products

• CA Spectrum
• CA eHealth
• DX NetOps

Issue/Introduction

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

Environment

Release : 10.3.x , 10.4.x

Component : Spectrum Report Manager

Resolution

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.

- Below are the commands needed if the Spectrum Report Manager Server is running on Linux:

cd <SPECROOT>/mysql/bin

./mysql --defaults-file=../my-spectrum.cnf -uroot -proot srmdbapi -A

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;


- Below are the commands needed if the Spectrum Report Manager Server is running on Windows:

Open a command prompt and type:

bash -login

cd /mysql/bin

./mysql -uroot -proot srmdbapi -A

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.