ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.
Spectrum MySQL Query to Identify Highest Event counts in the Report Manager Database
Article ID: 145858
Updated On:
Products
CA Spectrum
CA eHealth
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.
- 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.
Attachments
Feedback
Yes
No
Powered by
