* is run from cron once a week
* searches through the database for all events older than ${age_in_days} (defined within the script)
* moves the contents of the selected event to an archive directory (defined as ${archive_dir} in the script)
* removes the corresponding record from the event table.
A simple report of all events removed from the database is also stored in the archive directory.
The intention is to then either move the archive directory to offline media (CD/DVD/Tape) or remove the directory after a sufficient time period.
Programmer's notes:
1. This is the first release of this script. It works quite well for my purposes. I will not be insulted if you check my work, make additions or alterations, or offer suggestions. I have been known to make mistakes...
2. There are a couple of variables defined early in the script:
age_in_days - defines the time in days you think is reasonable to remove events. Any event older than this time period is a candidate for archival. Default value is 14 days
archive_dir - this is the root directory you wish to move your archived events into. Current value is /usr/local/archive/zm/`date +%b%Y`. Events will be moved under this directory structure, into a directory archived_event/${zone_name}/${event_id}
db_name - the name of your database (probably "zm")
3. Currently I don't use any database verification to log into the db - the script runs from root's crontab, root does not have a database password on my host
4. I assume your events are stored in /var/www/html/zm/events. If not you will need to alter this path in the script.
Installation:
1. Cut and paste the code below into a script /usr/local/bin/zm_archive_old_events. chmod u+x the script
2. Make any alterations per the above notes
3. Add a cron entry for the script. I suggest once or twice a week is sufficient
Code: Select all
#!/bin/bash
# zm_archive_old_events
# Script to clean up the database/events directory on a regular basis by
# removing "old events" - an event older than ${age_in_days}
# event data is copied to an archive directory for safe keeping/later
# archival/deletion
#
#
# Author: Nugget, aka Stuart Carmichael 20/06/2004
#
# Define some vars used later:
age_in_days=14 # keep events online for 2 weeks, then archive
archive_dir="/usr/local/archive/zm/`date +%b%Y`"
db_name="zm"
archive_date="`date --date=\"${age_in_days} days ago\" +%Y-%m-%d`"
# Now get a list of all "old" events
mysql -D "${db_name}" << EOF > /tmp/old_event.$$
select e.Id,m.Name,e.StartTime,e.EndTime
from Events e
left join Monitors m on e.MonitorId = m.Id
where e.StartTime <="${archive_date}"
order by m.Name,e.Id;
EOF
if [ `cat /tmp/old_event.$$|wc -l` -eq 0 ]; then
echo "No matching records found, exiting..."
exit 0
fi
# Using the temporary file created above, move the video files to
# the archive directory and remove the event record from the database.
# The archive directory chosen will be for the month/year for which the
# event occurred.
# An alternate method if you don't want to archive the video files is
# to simply remove the record from the Event table, and allow the
# cleanup daemon to remove the video files (requires the option
# ZM_OPT_FAST_DELETE to be turned on).
cat /tmp/old_event.$$ | grep -v "^Id" | while read event; do
id="`echo $event|awk '{ print $1 }'`"
zone="`echo $event|awk '{ print $2 }'`"
event_date="`echo $event|awk '{ print $3 }'`"
outdir="/usr/local/archive/zm/`date --date=${event_date} +%b%Y`/archived_event/$zone"
if [ ! -d "$outdir" ]; then
mkdir --parents $outdir
fi
# find the event directory, and move it to the archive location
mv /var/www/html/zm/events/$zone/$id ${outdir}/
# remove the record from the database
mysql -D "${db_name}" << EOF
delete from Events where Id=${id};
EOF
done
# now copy the event listing file to the archive directory for safe keeping
outfile="$archive_dir/archived_event/archived_events.`date +%d%m%y_%H%M`"
mv /tmp/old_event.$$ $outfile
num_events="`cat $outfile|wc -l`"-1
echo "${num_events} archived events found and processed."
echo "Output report can be found in the following location:"
echo "$outfile"
# at this point the db and the events directory are cleaned up. You can either
# burn these files to CD for safe keeping, or remove them later.
or to unarchive back to ZM.