[Ejabberd] log extraction from mysql to email

Log extraction script for ejabberd logs from mysql.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80

import MySQLdb import sys import collections import smtplib from email.mime.text import MIMEText # open a database connection # be sure to change the host IP address, username, password and database name to match your own connection = MySQLdb.connect (host = "localhost", user = "root", passwd = "DBPASSWORD", db = "ejabberd") # prepare a cursor object using cursor() method cursor = connection.cursor () # execute the SQL query using execute() method. cursor.execute ("select created_at,username,bare_peer,txt from archive where (created_at BETWEEN NOW() - INTERVAL 1 DAY AND NOW())") #cursor.execute ("select created_at,username,bare_peer,txt from archive where (created_at BETWEEN '2017-05-30 13:00:00' AND '2017-05-30 14:00:00')") # fetch all of the rows from the query data = cursor.fetchall () conversations = collections.defaultdict(list) raw = collections.defaultdict(list) #check if convo exists, if AB in conversations() #potential issue with morning rangeswitch=0 #use xrange step 2 if data[2][3]==data[3][3]: #if third and fourth messsage are equal xrange step 2 rangeswitch=2 elif data[0][3]==data[2][3]: rangeswitch=0 #for each recipient read the sender and copy the list entry into the value list of the sender, in the end sort by timestampLq #key=recipient, value=all messages to him in a list for item in xrange(0,len(data),rangeswitch): recipient = data[item][2] raw[recipient].append(data[item]) #iterate over all recipients messages, add the message to each sender list too, in the end sort by timestamp and extract convokey=None for recipient in raw: for message in xrange(0,len(raw[recipient])): sender=str(raw[recipient][message][1]) if (recipient.split("@")[0]+sender) in conversations: convokey=recipient.split("@")[0]+sender conversations[convokey].append(raw[recipient][message]) elif (sender+recipient.split("@")[0]) in conversations: convokey=sender+recipient.split("@")[0] conversations[convokey].append(raw[recipient][message]) else: convokey=recipient.split("@")[0]+sender conversations[convokey].append(raw[recipient][message]) #sort by timestamp all convos for user, messages in conversations.items(): messages.sort(key=lambda tup: tup[0]) #send by email me = "root@localhost" # Create message container - the correct MIME type is multipart/alternative. text = "" for key in conversations: print(key) for conversation in conversations: for message in xrange(0,len(conversations[conversation])): text=text+str(conversations[conversation][message][0])+' '+str(conversations[conversation][message][1])+' '+str(conversations[conversation][message][2])+' '+str(conversations[conversation][message][3])+'\n' msg = MIMEText(text, 'plain') for line in xrange(0,len(conversations[conversation])): if str(conversations[conversation][line][2]) != str(conversations[conversation][line+1][2]): rec1=str(conversations[conversation][line][2]) rec2=str(conversations[conversation][line+1][2]) break msg['Subject'] = "Chat log between"+' '+rec1.split("@")[0]+' '+'and'+' '+rec2.split("@")[0] msg['From'] = me msg['To'] = rec1+','+rec2 s = smtplib.SMTP('localhost') s.sendmail(me, 'your@mailserver.com', msg.as_string()) text="" break #remove after testing s.quit() # close the cursor object cursor.close () # close the connection connection.close () # exit the program sys.exit()