Bash Scripts for cbpolicyd: Difference between revisions
(Created page with "===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 ...") |
|||
Line 26: | Line 26: | ||
To search for sasl username and orininating ip, you can isse: | 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 | 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: | 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: | 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') ; | |||
This is the complete bash script example: | |||
echo "select distinct SASLUsername, ClientAddress | #! /bin/bash | ||
from session_tracking | |||
where ( (strftime('%s','now') - UnixTimestamp) < 600) | rm -f /tmp/temp | ||
and (ClientAddress != 'a.b.c.d') and (ClientAddress != '1.2.3.4') ;" | sqlite3 /opt/zimbra/data/cbpolicyd/db/cbpolicyd.sqlitedb | cut -f 1 -d '|' \ | |||
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 | | 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@ | [ -s /tmp/temp ] && cat /tmp/temp | mail -s "alarm: botnet attack botnet in act" alarm@ | ||
yourdomain | yourdomain | ||
[ -s /tmp/temp ] && user=`cat /tmp/temp | awk '{ print $1 }'` && su - zimbra -c "zmprov sp $user Cambiami.001" | [ -s /tmp/temp ] && user=`cat /tmp/temp | awk '{ print $1 }'` && su - zimbra -c "zmprov sp $user Cambiami.001" | ||
Revision as of 13:27, 15 November 2013
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
[code] echo "query string;" | sqlite3 /opt/zimbra/data/cbpolicyd/db/cbpolicyd.sqlitedb [/code]
where "query string" is an sql statement you have tested on sql interactive cli [code] sqlite3 /opt/zimbra/data/cbpolicyd/db/cbpolicyd.sqlitedb [/code]
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') ;
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 Cambiami.001"