I know you already resolved your issue, so this isn't directly of use, but
there is also a CA provided script called db_purge.pl that can be used for
removing all event data for a given time range. The script contains the
specific SQL queries used to purge the data. They're actually quite simple,
and the DDMDB database schema is pretty basic.
Although CA support will probably frown on you making any changes to the DB
directly, it isn't difficult to modify the event table purge query to just act
on a specific device. Normally, I'd never recommend modifying an application's
database directly. It's too unpredictable and you don't want to screw up the
application. However, when the vendor supplies a script that does just that,
and you can see exactly what the script is doing, and how it is doing it, that
makes this type of modification a lot more reasonable. I had to do something
similar to this, at CA Support's direction, a few years ago when we ran into a
bug that caused extra events to accumulate in the DB.
>From the script, the purge/delete query used is:
$delete_commands{ $table } = "DELETE FROM $table WHERE from_unixtime($field) >
'$min_date' and from_unixtime($field) < '$max_date'";
A few tweaks to limit it to a single device and you'd end up with something
like the following to delete all events for device with model handle 0x3dc0001
that occurred more than 7 days ago.
DELETE FROM ddmdb.event
WHERE model_h = conv("3dc0001",16,10)
AND WHERE utime < UNIX_TIMESTAMP(SUBDATE(CURRENT_TIMESTAMP(),7));
Now, you'd basically just connect to the database and run your delete query:
spectrum@spectroserver01:~$ $SPECROOT/mysql/bin/mysql
--defaults-file=my-spectrum.cnf -uroot -proot
mysql> use ddbdb;
mysql> DELETE FROM ddmdb.event WHERE model_h = conv("3dc0001",16,10) AND WHERE
utime < UNIX_TIMESTAMP(SUBDATE(CURRENT_TIMESTAMP(),7));
--
Christopher P. Cashell
________________________________
EIT Platform Engineering
E-Mail: [email protected]
Infrastructure Monitoring, Management, and Automation Division EIT ~
Converging People and Technologies
West Corporation<http://www.west.com/>
________________________________
From: Bohra, Saurabh [mailto:[email protected]]
Sent: Thursday, September 17, 2015 11:27 AM
To: spectrum
Subject: RE: Antwort: RE:[spectrum] Delete all alarms from a model
Someone asked me how did by DDMdb and Archive manager handle this situation.
They were pretty stable. The events table reached to its max capacity of 16 GB
on 1K block size and after running optimize script I gained back 11+ GB on my
disk. I still have to clean up SRM/reporting db.
-rw-rw---- 1 spectrum spectrum 8.7K Sep 8 11:02 mysql/data/ddmdb/event.frm
-rw-rw---- 1 spectrum spectrum 1.9G Sep 17 09:09 mysql/data/ddmdb/event.MYI
-rw-rw---- 1 spectrum spectrum 16G Sep 17 09:09 mysql/data/ddmdb/event.MYD
After running optimize script
-rw-rw---- 1 spectrum spectrum 8.7K Sep 8 11:02 mysql/data/ddmdb/event.frm
-rw-rw---- 1 spectrum spectrum 4.8G Sep 17 09:22 mysql/data/ddmdb/event.MYD
-rw-rw---- 1 spectrum spectrum 628M Sep 17 09:28 mysql/data/ddmdb/event.MYI
From: Bohra, Saurabh
Sent: Wednesday, September 16, 2015 4:51 PM
To: spectrum
Subject: RE: Antwort: RE:[spectrum] Delete all alarms from a model
Thanks everyone who sent me the suggestions. I had to delete 180,000 + alarms
from Spectrum, which got created in a week because of a SBG integration I was
testing with other NMS.
Anyways I first took the approach of deleting all the Stale alarms only
(filtering on "grep Yes" below) from all the Event Models which got created.
However this approach was taking too much of time. So I killed the nested for
loop in the 4th command below, restored the database from previous day and
destroyed all the Event models (except Default) using cli. After that I
restated SS and OC and everything looks good now.
Below are the commands I used on RHEL, just in case if anyone is interested.
== Find all Event Models and write their model handle in a file.
./show models mth=0x3dc0001 | cut -d' ' -f1 > /tmp/EventModels
== Write alarm id of all the stale alarms on different event models in
different files.
for EM in `cat /tmp/EventModels`; do sleep 3 && ./show alarms -s mh=$EM | grep
Yes | cut -d' ' -f1 > /tmp/alarm_$EM; done
== Remove all the event model files which are empty (has no alarms)
find /tmp -maxdepth 1 -size 0 -exec rm '{}' \;
== Delete alarms on the event models which have least alarms first.
for file in `ls -Sr /tmp/alarm_*` ; do echo "Processing file $file" && for ala
in `cat $file`; do ./destroy alarm -n aid=$ala; done; done
== Destroy Event models after editing the file (remove default event model)
from command 1 above
for EM in `cat /tmp/EventModels`; do sleep 5 && ./destroy model -n mh=$EM; done
Saurabh
From: Christian Fieres [mailto:[email protected]]
Sent: Wednesday, September 16, 2015 2:31 AM
To: spectrum
Subject: Antwort: RE:[spectrum] Delete all alarms from a model
Robert, list,
the correct CLI command is "destroy" - but I think it*s only suitable for user
clearable alarms, which leaves the necessity to raise a specific clear event
for every other alarm.
Freundliche Grüße
Christian Fieres
Mainova AG
Sachgebiet Netz- und Infrastruktur (M3-ST4)
Teamleiter Service Operation Center
Solmsstraße 38
60623 Frankfurt am Main
Telefon 069 213 23617
Mobil 0170 5601563
Telefax 069 213 9623617
E-Mail [email protected]<mailto:[email protected]>
Internet http://www.mainova.de<http://www.mainova.de/>
Von: <[email protected]<mailto:[email protected]>>
An: "spectrum"
<[email protected]<mailto:[email protected]>>,
Datum: 16.09.2015 08:28
Betreff: RE:[spectrum] Delete all alarms from a model
________________________________
Must be pretty simple to write a script that does that, something like below..
show alarms mh=<model_hangle> | while read ALARM
Do
Clear $ALARM
Done
/R
From: Bohra, Saurabh [mailto:[email protected]]
Sent: den 15 september 2015 18:57
To: spectrum
Subject: [spectrum] Delete all alarms from a model
Is there a way to bulk delete all the alarms from a model using cli or do I
have to destroy them one at a time?
Also, if bulk deleting is not available, does anyone know what will be the
impact if a destroy a model which has a couple of thousand active alarms on it?
Saurabh
* --To unsubscribe from spectrum, send email to
[email protected]<mailto:[email protected]>with the body: unsubscribe spectrum
[email protected]<mailto:[email protected]>
* --To unsubscribe from spectrum, send email to
[email protected]<mailto:[email protected]>with the body: unsubscribe spectrum
[email protected]<mailto:[email protected]>
Mainova Aktiengesellschaft - Solmsstraße 38 - D-60623 Frankfurt am Main
Vorsitzender des Aufsichtsrates: Stadtkämmerer Uwe Becker
Vorstand: Dr. Constantin H. Alsheimer (Vorsitzender), Norbert Breidenbach,
Lothar Herbst
Sitz der Aktiengesellschaft: Frankfurt am Main - Amtsgericht Frankfurt HRB 7173
- USt-IdNr. DE 114184034
Mainova steht für besten Service, faire Verträge und top Preise für Ihre
Energie - mit Auszeichnung!
Mehr Infos unter: http://www.mainova.de/auszeichnung
* --To unsubscribe from spectrum, send email to
[email protected]<mailto:[email protected]> with the body: unsubscribe spectrum
[email protected]<mailto:[email protected]>
* --To unsubscribe from spectrum, send email to
[email protected]<mailto:[email protected]> with the body: unsubscribe spectrum
[email protected]<mailto:[email protected]>
* --To unsubscribe from spectrum, send email to
[email protected]<mailto:[email protected]> with the body: unsubscribe spectrum
[email protected]<mailto:[email protected]>
---
To unsubscribe from spectrum, send email to [email protected] with the body:
unsubscribe spectrum [email protected]
