Helpful MySQL command queries to query the Spectrum mysql databases

book

Article ID: 192291

calendar_today

Updated On:

Products

CA Spectrum

Issue/Introduction

This is a document that will provide some basic mysql queries that can be used for the Spectrum mysql databases ddmdb and reporting

Resolution

To log into mysql on Windows, go to Search and type: 
bash -login
In the bash shell, navigate to $SPECROOT/mysql/bin and run:

./mysql -uroot -proot ddmdb;

To log into mysql on linux, navigate in a shell to $SPECROOT/mysql/bin and run:

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

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

Queries for DDMDB:

To see landscape info:

select * from landscape \G;

To see status of what is running:

show full processlist \G;

To see the top 10 events: 

SELECT hex(type), COUNT(*) as cnt FROM event GROUP BY type ORDER BY cnt DESC LIMIT 10; 

To get the top ten models with the most events:

SELECT HEX(e.model_h), m.model_name, COUNT(*) AS cnt
  FROM event e, model m
  WHERE e.model_h=m.model_h
  GROUP BY e.model_h
  ORDER BY cnt DESC 
  LIMIT 10;

To see a count of all top events for a defined time range: 

SELECT hex(type), COUNT(*) as cnt FROM event where from_unixtime(utime) > '2020-05-01 00:00:00' and from_unixtime(utime) < '2020-06-01 00:00:00' GROUP BY type ORDER BY cnt DESC LIMIT 50;

To see a top 25 list of events, organized by event_handle and model_handle:

SELECT HEX(type), HEX(model_h), COUNT(*) AS c FROM event GROUP BY HEX(type) ORDER BY c desc LIMIT 25;

SELECT HEX(type), HEX(model_h), COUNT(*) AS c FROM event GROUP BY HEX(model_h) ORDER BY c desc LIMIT 25;

To see a list of events from a specific model using the model handle:

select hex(type), hex(model_h), count(type) cnt from event where model_h=<model_handle> group by type order by cnt desc;

To see a top 50 events list organized per model_handle & model_name:

select hex( type ), hex( e.model_h ), m.model_name, count( * ) as cnt from event e, model m where e.model_h =m.model_h group by type, e.model_h order by cnt desc limit 50;

To see how many events where type=X (0x1001d is for reconfigurations) and on which model handles:

SELECT HEX(type), HEX(model_h), COUNT(*) AS c FROM event WHERE type=0x1001d GROUP BY HEX(model_h) ORDER BY c desc LIMIT 25; 

To see a list top events per all models for a defined time range:

select hex(type), hex(e.model_h), m.model_name, count(*) as cnt from event e, model m where e.model_h = m.model_h and utime > UNIX_TIMESTAMP('2020-05-01 00:00:00') and utime < UNIX_TIMESTAMP('2020-06-01 00:00:00') group by type, e.model_h order by cnt desc limit 100;

To see a list of top events on a single model for a defined time range: 

SELECT HEX(type), HEX(model_h), COUNT(*) AS cnt FROM event where model_h=<model_handle> and  from_unixtime(utime) > '2020-09-01 00:00:10' and from_unixtime(utime) < '2020-09-30 00:00:10' GROUP BY type ORDER BY cnt DESC LIMIT 50; 

To see a count of event/alarm 0x10d35 per model handle, from a specified date range: 

SELECT HEX(type), HEX(model_h), COUNT(*) AS c 
FROM event
WHERE type=0x10D35 and from_unixtime(utime) > '2019-10-01 00:00:00' and from_unixtime(utime) < '2019-10-08 00:00:00' 
GROUP BY HEX(model_h) 
ORDER BY c desc 
LIMIT 25;

To see a count of event/alarm 0x10d35 per model handle, from a date until now: 

SELECT HEX(type), HEX(model_h), COUNT(*) AS c 
FROM event
WHERE type=0x10D35 and utime >= UNIX_TIMESTAMP("2019-10-01") 
GROUP BY HEX(model_h) 
ORDER BY c desc 
LIMIT 25;

To see the top ten high-volume days for events:

SELECT DATE(FROM_UNIXTIME(UTIME)) AS x, COUNT(*) AS cnt FROM event GROUP BY x ORDER BY cnt DESC LIMIT 10;

To see the last ten days of volume of events:

 SELECT date(from_unixtime(utime)) AS x, COUNT(*) AS cnt FROM event GROUP BY x ORDER BY x DESC LIMIT 10;

To see a count of the # of events that occurred after a set date:

 SELECT count(*) FROM event WHERE utime >= UNIX_TIMESTAMP("2020-06-01");

To show the first and the last event in the DDMdb database:

 SELECT FROM_UNIXTIME(MIN(utime)) AS FIRST, FROM_UNIXTIME(MAX(utime)) AS LAST FROM event;

 

*********************************

DELETING EVENTS: 

Here is command to manually delete events from the DDM or Reporting. WARNING: use this with CAUTION - be sure to back up your DDMDB or Reporting database before performing any manual Deletes. Manual deletion should only be done if absolute necessary to reduce DDMDB or Reporting size and to increase performance. The DELETE action cannot be reversed! Except only through a  database reload. 

To Delete events (both DDMDB and Reporting):

delete from event where type=0x10d0c;

To Delete a specific event from a model handle during specified time (DDMDB only):

delete from event where type=0x1001c and from_unixtime(utime) > '2019-07-10 10:00:00' and from_unixtime(utime) < '2019-07-10 10:45:00' and model_h=0x102877;

To delete specific events from a model handle during specified time (Reporting only):

delete from event where type=0x1001c and from_unixtime(time) > '2019-07-10 10:00:00' and from_unixtime(time) < '2019-07-10 10:45:00' and model_h=0x102877;

 

Note: Once data is manually deleted from either database, each database will need to be optimized.  For DDMDB there is a script provided for this:

db_optimize.pl

https://techdocs.broadcom.com/us/en/ca-enterprise-software/it-operations-management/spectrum/10-4-2/administrating/database-management/ddm-database-maintenance/database-maintenance-and-optimization.html#concept.dita_1a2c2eee265ea4028d711bb3125e3d8dceb332e6_dboptimizepl

For Reporting database, manual optimization is required.
Please be aware that the optimization process itself required significant disk space (1.5x the largest table available, Example: 200GB Event table would require 300GB free space).  The reason for this is that MySQL will rebuild the table in place.  After the process is complete, disk space will reduce and performance could increase.

Steps:

It is recommended to run optimization periodically on MySQL database. When data is written and deleted from tables, there is residual data blocks retained by MySQL (purpose to allow room in the table to re-insert of new data at a later period) but over time, this residual data can add up and cause disk bloat. Optimizing the table periodically helps clean up unused data blocks and reduce disk space usage. 

Commands to optimize SRM MySQL: 

    log into OneClick/SRM server as Spectrum Admin
    cd $SPECROOT/mysql/bin
    run;
    ./mysqlcheck -uroot -proot -o reporting

 
Note: on Linux OS you must declare the mysql config file location: ./mysqlcheck --defaults-file=../my-spectrum.cnf -uroot -proot -o reporting

Additional Information

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/spectrum/10-4-1/administrating/database-management/ddm-database-maintenance/ddm-database-queries.html