utils/spacewalk-archive-audits | 213 ++++++++++++++++++++++++++++++++++++
utils/spacewalk-archive-audits.sgml | 98 ++++++++++++++++
2 files changed, 311 insertions(+)
New commits:
commit 9868543d14407e287d65edcc5fa52a6141f004ab
Author: Grant Gainey <ggainey(a)redhat.com>
Date: Mon Aug 5 13:37:56 2013 -0400
993254 - Script to enable us to purge audit-log tables
diff --git a/utils/spacewalk-archive-audits b/utils/spacewalk-archive-audits
new file mode 100755
index 0000000..8000a1e
--- /dev/null
+++ b/utils/spacewalk-archive-audits
@@ -0,0 +1,213 @@
+#!/usr/bin/python
+#
+# Utility for archiving/purging audit-log tables
+#
+# Copyright (c) 2013 Red Hat, Inc.
+#
+#
+# This software is licensed to you under the GNU General Public License,
+# version 2 (GPLv2). There is NO WARRANTY for this software, express or
+# implied, including the implied warranties of MERCHANTABILITY or FITNESS
+# FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
+# along with this software; if not, see
+#
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+#
+# Red Hat trademarks are not licensed under GPLv2. No permission is
+# granted to use or replicate Red Hat trademarks that are incorporated
+# in this software or its documentation.
+#
+
+"""spacewalk-archive-audits - a tool for extracting and purging the _log
tables in the SW database"""
+
+import csv
+import os
+import sys
+
+from os import getenv
+from optparse import OptionParser, OptionGroup
+from csv import writer
+from datetime import datetime
+
+try:
+ from spacewalk.common import rhnLog
+ from spacewalk.common.rhnConfig import CFG, initCFG
+ from spacewalk.common.rhnLog import LOG, log_clean
+ from spacewalk.server import rhnSQL
+except ImportError:
+ # pylint: disable=F0401
+ _LIBPATH = '/usr/share/rhn'
+ if _LIBPATH not in sys.path:
+ sys.path.append(_LIBPATH)
+ from server import rhnSQL
+ from common import rhnLog
+ from common.rhnLog import LOG, log_clean
+ from common.rhnConfig import CFG, initCFG
+
+LOG_LOCATION = '/var/log/rhn/archive-audit-tables.log'
+LOG_COLS = ['id', 'stamp', 'user_id']
+
+SQL_PIECES = {'oracle': {
+ 'COUNT' : 'select count(*) from {0};',
+ 'FIND_COLS' : "select column_name from user_tab_columns
where table_name = :table;",
+ 'FIND_TABLE' : "select table_name from user_tables where
table_name like '%!_LOG' escape '!';",
+ 'GET_DATA' : "select {0}, {1} from {2} t inner join log l
on l.id = t.log_id where l.stamp <= to_date(:tstamp, 'YYYY-MM-DD hh24:mi:ss')
order by l.id;",
+ 'PURGE_DATA' : """
+ delete {0}
+ where exists (
+ select *
+ from log l
+ where l.id = {0}.log_id
+ and l.stamp <= to_date(:tstamp, 'YYYY-MM-DD
hh24:mi:ss')
+ );
+ """,
+ 'PURGE_LOG_DATA' : "delete from log l where l.stamp <=
to_date(:tstamp, 'YYYY-MM-DD hh24:mi:ss');"
+ },
+ 'postgresql' : {
+ 'COUNT' : 'select count(*) from {0};',
+ 'FIND_COLS' : 'select column_name from
information_schema.columns where table_name = :table;',
+ 'FIND_TABLE' : "select table_name from
information_schema.tables where table_name like '%\_log';",
+ 'GET_DATA' : 'select {0}, {1} from {2} t inner join log l
on l.id = t.log_id where l.stamp <= (timestamp :tstamp) order by l.id;',
+ 'PURGE_DATA' : 'delete from {0} t using log l where l.id =
t.log_id and l.stamp <= (timestamp :tstamp);',
+ 'PURGE_LOG_DATA' : 'delete from log l where l.stamp <=
(timestamp :tstamp);'
+ }
+ }
+
+def setup_options():
+ usage = 'usage: %prog --list --archive --purge --timestamp'
+ parser = OptionParser(usage=usage)
+
+ parser.add_option('-l', '--list', action='store_true',
dest='list',
+ help='list number of log entries before --timestamp (or current size if
--timestamp not specified), of all *_log tables')
+ parser.add_option('-a', '--archive', action='store_true',
default=False, dest='archive',
+ help='archive as CSV the contents of all *_log tables')
+ parser.add_option('-p', '--purge', action='store_true',
dest='purge',
+ help='purge all entries made on or before --timestamp')
+ parser.add_option('-t', '--timestamp', action='store',
dest='purge_date', metavar='YYYY-MM-DD hh24:mi:ss',
+ help='all entries made at or before this time will be purged')
+
+ return parser
+
+def validate_options():
+ parser = setup_options()
+ (options, args) = parser.parse_args()
+ if not (options.list or options.archive or options.purge):
+ parser.error("At least one of --list, --archive, --purge is required")
+
+ if (options.archive or options.purge) and not options.purge_date:
+ parser.error("--timestamp is required for --archive and --purge")
+
+ return (options, args)
+
+def _log(stmt):
+ log_clean(0, '%s|%s|%s' % (datetime.now().isoformat(),
os.getenv("SUDO_USER"), stmt))
+
+def _get_sql(key):
+ return SQL_PIECES.get(CFG.DB_BACKEND).get(key)
+
+def _get_allcols(name):
+ all_cols = []
+ all_cols.extend(['l'+x for x in LOG_COLS])
+ all_cols.extend(columns(name))
+ return all_cols
+
+def _get_data_statement(name):
+ data_stmt = _get_sql('GET_DATA').format(
+ ', '.join(['l.'+x+' l'+x for x in LOG_COLS]),
+ ', '.join(['t.'+x for x in columns(name)]),
+ name)
+ return data_stmt
+
+def tables():
+ """What are the tables whose names fit the pattern
*_log?"""
+ stmt = rhnSQL.execute(_get_sql('FIND_TABLE'))
+ table_names = stmt.fetchall() or []
+ rc = []
+ for name in table_names:
+ rc.append(name[0])
+ return rc
+
+def columns(tbl):
+ """What are the columns of table <tbl>?"""
+ stmt = rhnSQL.execute(_get_sql('FIND_COLS'), table=tbl)
+ col_names = stmt.fetchall() or []
+ rc = []
+ for name in col_names:
+ rc.append(name[0])
+ return rc
+
+def count(tbl):
+ """How many rows are in table <tbl>?"""
+ tbl_str = _get_sql('COUNT').format(tbl)
+ stmt = rhnSQL.execute(tbl_str)
+ num_rows = stmt.fetchone()
+ return num_rows[0]
+
+def list(opt):
+ """List current state of all *_log tables found"""
+ names = tables()
+ for name in names:
+ print '%32s : %d' % (name, count(name))
+
+def archive(opt):
+ """Grab the requested data and store it in a .csv file
'here'"""
+ _log('Archiving everything before ' + opt.purge_date)
+ names = tables()
+ for name in names:
+ rows = 0
+ with open(name+'.'+datetime.now().isoformat()+'.csv',
'wb') as f:
+ writer = csv.writer(f)
+ hdr_names = _get_allcols(name)
+ writer.writerow(hdr_names)
+ stmt = rhnSQL.execute(_get_data_statement(name), tstamp=opt.purge_date)
+ rslts = None
+ while 1:
+ rslts = stmt.fetchone_dict()
+ if not rslts:
+ break
+ row = []
+ for col in hdr_names:
+ row.append(rslts.get(col))
+ writer.writerow(row)
+ rows += 1
+ print '%32s : %d rows archived' % (name, rows)
+
+def purge(opt):
+ """Remove from the _log tables anything that happened before
<timestamp>"""
+ _log('Purging everything before ' + opt.purge_date)
+ names = tables()
+ for name in names:
+ del_str = _get_sql('PURGE_DATA').format(name)
+ stmt = rhnSQL.execute(del_str, tstamp=opt.purge_date)
+ print '%32s : %s rows remain' % (name, count(name))
+ _log('%32s : %s rows remain' % (name, count(name)))
+ rhnSQL.execute(_get_sql('PURGE_LOG_DATA'), tstamp=opt.purge_date)
+ return
+
+if __name__ == '__main__':
+ (options, args) = validate_options()
+ initCFG('server')
+ rhnSQL.initDB()
+
+ if options.list:
+ list(options)
+ sys.exit(0)
+
+ rhnLog.initLOG(LOG_LOCATION)
+ if rhnLog.LOG.real == 0:
+ print "Failed to access logfile " + LOG_LOCATION + " -
exiting..."
+ print "(Try running as sudo)"
+ sys.exit(1)
+
+ if options.archive:
+ print 'Archiving...'
+ archive(options)
+
+ if options.purge:
+ print 'Starting counts:'
+ list(options)
+ print '...archiving'
+ rhnSQL.transaction('PURGE')
+ archive(options)
+ print '...purging'
+ purge(options)
+ rhnSQL.commit();
diff --git a/utils/spacewalk-archive-audits.sgml b/utils/spacewalk-archive-audits.sgml
new file mode 100644
index 0000000..5f007df
--- /dev/null
+++ b/utils/spacewalk-archive-audits.sgml
@@ -0,0 +1,98 @@
+<!DOCTYPE refentry PUBLIC "-//OASIS//DTD DocBook V3.1//EN"
+>
+<refentry>
+
+<RefMeta>
+<RefEntryTitle>spacewalk-archive-audits</RefEntryTitle><manvolnum>8</manvolnum>
+<RefMiscInfo>Version 1.0</RefMiscInfo>
+</RefMeta>
+
+<RefNameDiv>
+<RefName><command>spacewalk-archive-audits</command></RefName>
+<RefPurpose>
+Archive and purge the audit-logging tables introduced with Spacewalk 2.0
+</RefPurpose>
+</RefNameDiv>
+
+<RefSynopsisDiv>
+<Synopsis>
+ <cmdsynopsis>
+ <command>spacewalk-archive-audits</command>
+ <arg>options <replaceable>...</replaceable></arg>
+ </cmdsynopsis>
+ <cmdsynopsis>
+ <arg>-l</arg>
+ <arg> --list</arg>
+ </cmdsynopsis>
+ <cmdsynopsis>
+ <arg>-a</arg>
+ <arg>--archive</arg>
+ </cmdsynopsis>
+ <cmdsynopsis>
+ <arg>-p</arg>
+ <arg>--purge</arg>
+ </cmdsynopsis>
+ <cmdsynopsis>
+ <arg>-t<replaceable> YYYY-MM-DD
hh24:mi:ss</replaceable></arg>
+ <arg>-timestamp=<replaceable>YYYY-MM-DD
hh24:mi:ss</replaceable></arg>
+ </cmdsynopsis>
+ <cmdsynopsis>
+ <arg>-h</arg><arg>--help</arg>
+ </cmdsynopsis>
+</Synopsis>
+</RefSynopsisDiv>
+
+<RefSect1><Title>Description</Title>
+ <para>
+ <emphasis>spacewalk-archive-audits</emphasis> archives and purges the
audit-logging tables enabled in the spacewalk instance on which the command is executed
+ </para>
+ <para>
+ Since audit-entries grow over time, this command is provided to give the
spacewalk administrator a tool to cull old table-entries while preserving them for
historical reporting.
+ </para>
+ <para>
+ When --archive or --purge are chosen, an entry is made to
/var/log/rhn/archive-audit-tables.log with the action, timestamp, and results.
+ </para>
+</RefSect1>
+
+<RefSect1><Title>Options</Title>
+<variablelist>
+ <varlistentry>
+ <term>-h, --help</term>
+ <listitem>
+ <para>Display the help screen with a list of options.</para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>-l, --list </term>
+ <listitem>
+ <para> List the current sizes of all *_log tables found in the
Spacewalk database</para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>-a, --archive</term>
+ <listitem>
+ <para>Archives all entries occurring before --timestamp-specified time,
into files named TABLENAME.CURRENT-TIMESTAMP.csv
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>-p, --purge</term>
+ <listitem>
+ <para>Deletes all audit-entries occurring before --timestamp-specified
time
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>-l=<replaceable> YYYY-MM-DD hh24:mi:ss</replaceable>,
--timestamp=<replaceable>YYYY-MM-DD hh24:mi:ss</replaceable></term>
+ <listitem>
+ <para>Specifies the most-recent timestamp to --archive or --purge.
Any/all entries made <emphasis>before</emphasis> this mark will be recorded
and/or deleted. Required for --archive and --purge.</para>
+ </listitem>
+ </varlistentry>
+</variablelist>
+</RefSect1>
+
+<RefSect1><Title>Authors</Title>
+<simplelist>
+ <member>Grant Gainey
<email>ggainey(a)redhat.com</email></member>
+</simplelist>
+</RefSect1>
+</RefEntry>
+