source: contributed/trunk/billing-query/dcache-billing-query.py @ 27

#! /usr/bin/python
#
# $Id: $

''' This script pulls information about user activities from the
dCache billing database and logs. This will be useful if there is a
security incident at your site and you want to pick out all relevant
information about the activity of a particular user DN.

Usage
-----

$ python dcache-billing-query.py -s "2008-01-23 12:00:00" -e "2008-01-24 23:00:00" -i "User DN"

Once you have pinned down the start and end times to within a day of
each other, run the script again with the -l option to query the
billing log files (with out -l it just looks at the billing
database). 

References
----------

Script: http://trac.dcache.org/trac.cgi/browser/contributed/trunk/billing-query
PyGreSQL: http://www.pygresql.org/
'''

__author__ = 'Greig A Cowan'
__date__ = 'March 2008'
__version__ = 0.1

from time import strptime
import sys
import commands
import re
import pg
from optparse import OptionParser

def main():
    '''Parse command line options, call methods'''
    parser = OptionParser(
        usage = 'usage: %prog [options]')
    parser.add_option('-d', '--debug', dest='debug', action='store_true',
                      help='use debug flag only for testing.')
    parser.add_option('-s', '--start', dest='start',
                      default='2008-03-03 00:00:00',
                      help='start timestamp')
    parser.add_option('-e', '--end', dest='end',
                      default='2008-03-05 00:00:00',
                      help='end timestamp')
    parser.add_option('-b', '--billinghost', dest='host',
                      default='localhost',
                      help='host where the database is running')
    parser.add_option('-l', '--logs', dest='log',
                      action='store_true',
                      help='set this option if you want to parse the \
                      billing logs on the day of the START and END times')
    parser.add_option('-i', '--id', dest='identity',
                      default='/C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan',
                      help='user DN')
    
    # Parse commane-line input
    (options, args) = parser.parse_args()
    try:
        input_filename = args
    except ValueError:
        parser.print_help()
        
    if len(args) > 1:
        parser.error('incorrect number of arguments')
                    
    db = pg.connect(dbname='billing', host=options.host, user='srmdcache')
    
    transactions = get_doorinfo( db, options.identity,
                                 options.start, options.end)
    initiators = {}
    pnfsids = []
    for keys, values in transactions.iteritems():
        init = get_billinginfo( db, keys, options.start, options.end)
        initiators.update( init)
        pnfsids.append( values[8])
        
    display_details( transactions, initiators)

    if options.log:
        if len( pnfsids) != 0:
            for id in uniq(pnfsids):
                check_billing_logs( id, options.start, options.end)

    db.close()

def display_details( transactions, initiators):
    '''Display the information from the billinginfo and doorinfo tables.'''

    initiatorStrings = ['\t isnew: \t', '\t datestamp: \t',\
                        '\t pool cell: \t',\
                        '\t pnfsid: \t', '\t fullsize (B): \t', \
                        '\t transfersize: \t',\
                        '\t storageclass: \t', '\t client: \t',\
                        '\t errorcode: \t', '\t errormessage: \t',\
                        '\t protocol: \t']
    transactionStrings = ['\t datestamp: \t', '\t door cell: \t',\
                          '\t uid: \t\t',\
                          '\t gid: \t\t', '\t client: \t', \
                          '\t errorcode: \t', '\t errormessage: \t',\
                          '\t path: \t']

    for key, value in initiators.iteritems():
        for i in range( len( initiatorStrings)):
            if i == 0:
                if value[0] == 't':
                    print '#######'
                    print '# PUT #'
                    print '#######'
                elif value[0] == 'f':
                    print '#######'
                    print '# GET #'
                    print '#######'
                print 'Pool information:'
            else:
                print initiatorStrings[ i], value[ i]
        print 'Door information:'
        for i in range( len( transactionStrings)):
            print transactionStrings[ i], transactions[key][i]


def get_doorinfo( db, owner, start, end):
    '''Query for the transactions that the doors processed.'''

    query = 'select datestamp, cellname, mappeduid, mappedgid, client, \
    errorcode, errormessage, path, pnfsid, transaction \
    from doorinfo where owner=\'%s\' \
    and datestamp >= \'%s\'\
    and datestamp <= \'%s\';' % (owner, start, end)

    out = db.query( query).getresult()

    trans = {}

    for entry in out:
        trans[ entry[9]] = entry

    return trans

def get_billinginfo( db, transaction, start, end):
    '''Get more information about this pnfsid.'''
    
    query = 'select isnew, datestamp, cellname, pnfsid, fullsize,\
    transfersize, storageclass, client, errorcode, errormessage, \
    protocol, initiator \
    from billinginfo where initiator=\'%s\' \
    and datestamp >= \'%s\'\
    and datestamp <= \'%s\';' % (transaction, start, end)

    out = db.query( query).getresult()

    trans = {}

    for entry in out:
        trans[ entry[11]] = entry

    return trans


def fix_time( time):
    '''If the day or month number is < 10, we need to add a leading 0.'''
    if int( time) < 10:
        return "0%s" % time
    else:
        return time


def check_billing_logs( pnfsID, start, end):
    '''grep billing logs for information. Need both start and end times
    in case the billing logs are split over two days.'''
    
    # need to deal with this
    start_list = strptime(start, "%Y-%m-%d %H:%M:%S")
    end_list   = strptime(end,   "%Y-%m-%d %H:%M:%S")
    
    s_year = start_list[0]
    s_month = start_list[1]
    s_day = start_list[2]
    
    e_year = end_list[0]
    e_month = end_list[1]
    e_day = end_list[2]

    grep = "grep %s /opt/d-cache/billing/%s/%s/billing-%s.%s.%s" \
           % ( pnfsID, s_year, fix_time( s_month), s_year, \
               fix_time( s_month), fix_time( s_day))

    command = []
    command.append( grep)

    if s_day != e_day:
        # Check if the start and end times are on the same day
        grep2 = "grep %s /opt/d-cache/billing/%s/%s/billing-%s.%s.%s" \
                % ( pnfsID, e_year, fix_time( e_month), \
                    e_year, fix_time( e_month), fix_time( e_day))
        command.append( grep2)

    reg = re.compile( "RemoveFiles")
    print "\n################\n# BILLING LOGS #\n################"
    for c in command:
        ( stat, out) = commands.getstatusoutput( c)
        print out
        for line in out.split(' '):
            if reg.search( line):
                print "\n######################"
                print "# SUMMARY OF BILLING #"
                print "######################"
                print "%s was deleted\n" % pnfsID
                print "NOTE: dCache does not record an attempt to delete a file that is already deleted"
        
def uniq( seq):
    # Not order preserving
    keys = {}
    for e in seq:
        keys[e] = 1
        return keys.keys()


def getCredentialID( db, identity):
    '''Map DN to credential ID.'''

    query = "select id from srmrequestcredentials where \
    credentialname like \'%s\'" % identity

    id = db.query( query).getresult()[0][0]

    return id


if __name__ == "__main__":
    main()