Bash Scripts for cbpolicyd

Revision as of 07:20, 16 November 2013 by Maumar (talk | contribs) (→‎The script)
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Introduction

You can do many things using policies, but not all.

Here you can find some bash examples, a bare starting point to implement new functionalities.

How to query sqlite

Basically, you can

echo "query string;"  | sqlite3 /opt/zimbra/data/cbpolicyd/db/cbpolicyd.sqlitedb


where "query string" is an sql statement you have tested on sql interactive cli

sqlite3 /opt/zimbra/data/cbpolicyd/db/cbpolicyd.sqlitedb

Trapping abused sasl users

I was interested in trap sasl senders, when sasl credentials are abused and you have many connections per second each coming from a different ip.

The table to use is session_tracking.

To search for sasl username and orininating ip, you can isse:


echo "select distinct  SASLUsername, ClientAddress from session_tracking;" | sqlite3 /opt/zimbra/data/cbpolicyd/db/cbpolicyd.sqlitedb | more


If you want restrict it to last 10 minutes:


echo "select distinct  SASLUsername, ClientAddress from session_tracking where ( (strftime('%s','now') - UnixTimestamp) < 600) | sqlite3 /opt/zimbra/data/cbpolicyd/db/cbpolicyd.sqlitedb | more


You have to filter out all connections coming from mtas that can send to your server w/out authentication:


select distinct  SASLUsername, ClientAddress from session_tracking where ( (strftime('%s','now') - UnixTimestamp) < 3600) and (ClientAddress != '1.2.3.4') and (ClientAddress != '4.3.2.1') ;

The script

This is the complete bash script example:

#! /bin/bash

rm -f /tmp/temp

echo "select distinct  SASLUsername, ClientAddress 
from session_tracking 
where ( (strftime('%s','now') - UnixTimestamp) < 600) 
and (ClientAddress != 'a.b.c.d') and (ClientAddress != '1.2.3.4') ;" | sqlite3 /opt/zimbra/data/cbpolicyd /db/cbpolicyd.sqlitedb | cut -f 1 -d '|' \
           | sort | uniq -c | awk '{ if( $1 > 5 ) print $2 " has sent from: " $1 " uniq ip " }'  > /tmp/temp


[ -s /tmp/temp ] && cat /tmp/temp  |  mail -s "alarm: botnet attack botnet in act" alarm@yourdomain

[ -s /tmp/temp ] && user=`cat /tmp/temp |  awk '{ print $1 }'` && su - zimbra -c "zmprov sp $user changeme"


Verified Against: Zimbra Collaboration Suite 8.4 Date Created: 11/15/2013
Article ID: https://wiki.zimbra.com/index.php?title=Bash_Scripts_for_cbpolicyd Date Modified: 2013-11-16



Try Zimbra

Try Zimbra Collaboration with a 60-day free trial.
Get it now »

Want to get involved?

You can contribute in the Community, Wiki, Code, or development of Zimlets.
Find out more. »

Looking for a Video?

Visit our YouTube channel to get the latest webinars, technology news, product overviews, and so much more.
Go to the YouTube channel »

Jump to: navigation, search