Tuesday, June 6, 2017

[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()

No comments:

Post a Comment