[Logstash] How to process Amavis logs via filebeat and logstash for elasticsearch

First activate JSON logging for amavis by following this:

in rsyslog.conf add:

$MaxMessageSize 32k

in amavisd.conf add: 

$logline_maxlen = ( 32*1024 ) - 50; # 32k max message size, keep 50 bytes for syslog
$log_templ = <<'EOD';
[:report_json]
EOD

Now the thing is amavis logs via syslog to file so the line structure is syslog + json message.
For Filebeat add the following to your config:

  fields:
        tags: ['json']

  fields_under_root: true

Now you do not need the json tag per se but I am using it because I got json and non-json logs. (can't use hilite me, will break page :D in case you have issues use this https://pastebin.com/raw/DtRJ5mDX )

filter
{
if "json" in [tags] {

grok {
    match => { "message" => "%{SYSLOGTIMESTAMP:logtime} %{SYSLOGHOST} %{DATA:program}(?:\[%{POSINT}\])?: (?:\((.*?)\))? %{GREEDYDATA:message}" }
    overwrite => "message"
}
}
}

Now in your logstash config you need the following to process the message part properly and use the json as source:

You can add the following to it or keep it in a separate config but what you need then is:

    if "json" in [tags] { 
    json {
      source => "message"
       }   
    }

[Logstash] How to match @timestamp with syslog timestamp

If you have a time/date field in your data, e.g. syslog time and you want to match @timestamp with it you do this as the following:
Add to filter {}

date {
    match => [ "logtime", "MMM  d HH:mm:ss", "MMM dd HH:mm:ss" ] #syslog pattern
    target => "@timestamp"
    timezone => "UTC" #set the timezone to the one your logs are originally in
}

The 'logtime' field is my syslog date field.
Logstash Version: 5.4.1-1

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