Below is a Bash script that will output 3 CSV files from a DDM database showing the “top events”, “top event-generating models”, and a combination of those two queries.
You can pass it a date argument in format YYYY-MM-DD to get a specific day’s
events from the DDM history.
It will default to show counts from 00:00:00 to present time for the current
date.
You will need to adjust the path to $MYSQLEXE for your SpectroServer.
I limit the query output to events with 10,000 occurrences per day, or models
with over 10,000 events per day.
This usually gives me no more than 10-20 models or event codes when I have
event-related issues in my DDM.
You may need to adjust this value in the SQL queries for your installation to
get meaningful results.
I know the OP was concerned with the SRM database (“reporting” instead of
“ddmdb”),
hopefully this SQL can be adapted to SRM tables to help show what’s growing in
there.
HTH,
--Mark S
#!/bin/sh
if [ -z "$1" ] ; then
EVENTDATE=`date +%Y-%m-%d`
else
EVENTDATE="$1"
fi
SQLFILE=events.sql
MYSQLEXE=/d/win32app/SPECTRUM/mysql/bin/mysql.exe
QUERY="TopEvents"
echo -n "Gathering $QUERY..."
echo "SELECT CONCAT( '$EVENTDATE,0x', LPAD( eventhex, 8, '0' ), ',', eventcount
) AS $QUERY FROM ( SELECT HEX(type) AS eventhex, COUNT(1) AS eventcount FROM
event WHERE utime > UNIX_TIMESTAMP('$EVENTDATE 00:00:00') AND utime <
UNIX_TIMESTAMP('$EVENTDATE 23:59:59') GROUP BY HEX(type) ) myeventtable WHERE
eventcount > 10000 ORDER BY eventcount DESC, eventhex" > $SQLFILE
$MYSQLEXE -uroot -proot ddmdb < $SQLFILE > $QUERY-$EVENTDATE.csv
echo "done!"
QUERY="TopModels"
echo -n "Gathering $QUERY..."
echo "SELECT CONCAT( '$EVENTDATE,0x', LPAD( modelhex, 6, '0' ), ',', eventcount
) AS $QUERY FROM ( SELECT HEX(model_h) AS modelhex, COUNT(1) AS eventcount FROM
event WHERE utime > UNIX_TIMESTAMP('$EVENTDATE 00:00:00') AND utime <
UNIX_TIMESTAMP('$EVENTDATE 23:59:59') GROUP BY HEX(model_h) ) myeventtable
WHERE eventcount > 10000 ORDER BY eventcount DESC, modelhex" > $SQLFILE
$MYSQLEXE -uroot -proot ddmdb < $SQLFILE > $QUERY-$EVENTDATE.csv
echo "done!"
QUERY="TopModelEvents"
echo -n "Gathering $QUERY..."
echo "SELECT CONCAT( '$EVENTDATE,0x', LPAD( modelhex, 6, '0' ), ',0x', LPAD(
eventhex, 8, '0' ), ',', eventcount ) AS $QUERY FROM ( SELECT HEX(model_h) AS
modelhex, HEX(type) AS eventhex, COUNT(1) AS eventcount FROM event WHERE utime
> UNIX_TIMESTAMP('$EVENTDATE 00:00:00') AND utime < UNIX_TIMESTAMP('$EVENTDATE
23:59:59') GROUP BY HEX(model_h), HEX(type) ) myeventtable WHERE eventcount >
10000 ORDER BY eventcount DESC, modelhex, eventhex" > $SQLFILE
$MYSQLEXE -uroot -proot ddmdb < $SQLFILE > $QUERY-$EVENTDATE.csv
echo "done!"
exit
________________________________
Mark Serencha – Inforonics LLC – (m) +1-781-439-0519 –
[email protected] <mailto:[email protected]>
From: Mark Serencha
Sent: Tuesday, May 31, 2011 10:24 AM
To: 'Gasper Krajnik'; spectrum
Subject: RE: [spectrum] Reporting database isze increasing
I have some MySQL commands (received from CA support) which you can run against
your DDM databases (on your SpectroServers)
to determine rapidly which models are generating the most events, or which
events are the most common (across all models).
I’m sure there’s a way to run something similar in the SRM database, but I
always try to stop these sorts of things at the source.
I will re-post later today with those SQL queries.
Thanks,
--Mark S
________________________________
Mark Serencha – Inforonics LLC – (m) +1-781-439-0519 –
Mark.Serencha_AT_inforonics.com
From: Gasper Krajnik [mailto:[email protected]]
Sent: Tuesday, May 31, 2011 2:00 AM
To: spectrum
Subject: RE: [spectrum] Reporting database isze increasing
Hi,
I have opened ticket on CA Support for same problem. I think CA don't have
regular solution for this problem.
We solve it with these procedures:
1. Run bash -login
2. Go to folser "%SPECTROOT%/mysql/bin"
3. Run "./mysql -uroot -proot"
4. "use reporting"
5. "optimize tables event;"
When optimization will be finished, you have to flush tables:
6. "flush tables;"
You can repeat steps also for other tables.
BR,
mag. Gašper Krajnik | [email protected] | GSM: +386 41 999
599
From: [email protected] [mailto:[email protected]]
Sent: Tuesday, May 31, 2011 7:01 AM
To: spectrum
Cc: spectrum
Subject: [spectrum] Reporting database isze increasing
Hi,
From last one month we are facing reporting database issue, the reporting
database size is increasing immensely. On daily basis its consuming nearly
about 5 GB.Around 1 month back the reporting database size was just 60 GB, we
use to delete the archive database after every 1 month now we have to delete
the archive database daily. Pls suggest
With Regards,
MOhan
Unicenter Support
Wipro Infotech
TEL: +91-22-3075-3943/3944
Mail to: [email protected]
“Kindly log a call on http://usdprdweb2/CAisd/pdmweb.exe
<http://usdprdweb2/CAisd/pdmweb.exe> for any USD related assistance"
Calls logged in USD would be prioritized
HDFC Bank has been recognized as "The Strongest Bank in Asia Pacific Region" by
The Asian Banker, outperforming 59 top tier banks across 13 countries.
____________________________________________
Disclaimer:
"The information contained herein (including any accompanying documents) is
confidential and is intended solely for the addressee(s). If you have
erroneously received this message, please immediately delete it and notify the
sender. Also, if you are not the intended recipient, you are hereby notified
that any disclosure, copying, distribution or taking any action in reliance on
the contents of this message or any accompanying document is strictly
prohibited and is unlawful. The organization is not responsible for any damage
caused by a virus or alteration of the e-mail by a third party or otherwise.
The contents of this message may not necessarily represent the views or
policies of HDFC Bank Ltd."
* --To unsubscribe from spectrum, send email to [email protected] with the
body: unsubscribe spectrum [email protected]
* --To unsubscribe from spectrum, send email to [email protected] with the
body: unsubscribe spectrum [email protected]
---
To unsubscribe from spectrum, send email to [email protected] with the body:
unsubscribe spectrum [email protected]
<<image001.jpg>>
