Good to know about this script. I had seen with other DDM scripts however never used it in the past. Does this script also recover the disk space the events file has used or it just purges the records.
Also is there a similar script for SRM events.ibd file? From: Cashell, Christopher P. [mailto:[email protected]] Sent: Friday, September 18, 2015 1:10 AM To: Bohra, Saurabh Cc: spectrum Subject: RE: Antwort: RE:[spectrum] Delete all alarms from a model 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]<mailto:[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]
