Helpful MySQL command queries to query the Spectrum mysql database
Article ID: 192291
Updated On:
Products
Issue/Introduction
This is a document that will provide some basic mysql queries that can be used for the Spectrum mysql database (DDM db) and the reporting database.
Environment
Release: All Supported Releases
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 -p<pass>
or for a specific DB, such as the SRM (reporting):
./mysql -uroot -p<pass> reporting
or for the Archive Manager (DDMdb):
./mysql -uroot -p<pass> ddmdb
To log into mysql on linux, navigate in a shell to $SPECROOT/mysql/bin and run:
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<pass> ddmdb
Or for reporting:
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<pass> reporting -A
TRACING QUERIES
At the mysql prompt after logging into mysql:
\T
This will turn on the outfile logging. Syntax:
\T Outfile.out
Without specifying a location it will default to $SPECROOT/mysql/bin directory. If a non-spectrum installation is being used, it will go to $MYSQL_ROOT/bin/
To disable the outfile logging:
\t
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;
To show the time and model name of a deleted device in the DDMdb database you can use either of these:
SELECT model.model_name, DATE_FORMAT(FROM_UNIXTIME(event.utime), '%Y-%m-%d %H:%i:%s') AS readable_utime
FROM model
JOIN event ON model.model_h = event.model_h
WHERE type=0x10202;
SELECT m.model_name, concat("0x",hex( e.type)) as type, FROM_UNIXTIME(e.utime) as time from event e inner join model m on m.model_h=e.model_h where e.ty
pe=0x10202 order by time desc;
*********************************
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=<Event_Type>;
NOTE: replace <Event_Type> with the value of the specific event type you wish to delete. For example - 0x1001c
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=<Model_Handle>;
Where model_h can be obtained from:
OneClick -> Information view -> Attributes tab -> Model_Handle
For example 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(utime) > '2019-07-10 10:00:00' and from_unixtime(utime) < '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
TechDocs : DX NetOps Spectrum 22.2 : Database Maintenance and Optimization
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
Feedback
