Page 1 of 4

Simple program to report successful/fail ip logins and sorted by count

Posted: Sat Jan 28, 2017 12:17 am
by JDunphy
In case anyone wants to track ip's with user login and the counts by ip. I use it to look for compromised accounts or ones that will be soon. ;-)

It resulted from an active dictionary attack on one of our 8.7.1 servers so this quick/dirty script to see what was happening.

Run the program without any arguments and as the zimbra user. You need read access to /opt/zimbra/log for whatever user to run the script as.

Output is by user and sorted by ip reference count and looks like this.

user_a@example.com
[ 4] - 189.170.193.209
[ 4] - 164.136.12.177
[ 4] - 144.136.12.187
[ 3] - 51.62.160.157
[ 1] - 118.253.220.248
[ 1] - 129.55.121.74
[ 1] - 47.115.1.60
[ 1] - 127.244.31.10
[ 4] - 174.136.12.187 failed imap
[ 4] - 188.170.193.209 failed imap
[ 4] - 174.136.12.177 failed imap
[ 3] - 50.62.160.157 failed imap
[ 1] - 117.253.220.248 failed imap
[ 1] - 119.55.121.74 failed imap
[ 1] - 117.244.31.10 failed imap
[ 1] - 37.115.1.60 failed imap

user_b@example.com
[ 9] - 137.142.50.234
[ 2] - 25.170.150.130
[ 1] - 137.142.50.234 failed web

So user_b@example.com mistyped their zimbra login information but was able to login 9 times correctly.

Code: Select all

#!/usr/bin/perl

use Data::Dumper qw(Dumper);

%ip_list = ();  #ip list
%fip_list = ();	#failed ip list
$audit_log = 0;	#todays logging

chdir "/opt/zimbra/log";

for (glob 'audit.log*') {

  # audit.log is always todays stuff
  #print "Opening file $_";
  if ($_ eq 'audit.log')
  {
     $audit_log = 1;
     open (IN, sprintf("cat %s |", $_))
       or die("Can't open pipe from command 'zcat $filename' : $!\n");
  }
  else
  {
     $audit_log = 0;
     open (IN, sprintf("zcat %s |", $_))
       or die("Can't open pipe from command 'zcat $filename' : $!\n");
  } 

  while (<IN>) 
  {
	if (m#invalid password#i)
	{ 
		#print $_;
		if (m#ImapServer#i) {
		my($ip,$user) = m#.*\s+\[ip=.*;oip=(.*);via=.*;\]\s*.* failed for\s+\[(.*)\].*$#i;
		$uagent = "imap";
		#print " - ip is $ip, user is $user, agent is $uagent\n";
		#print $_;

		++$ip_list{$user}{$ip};		#we loop by this for report
		++$fip_list{$user}{$ip}{'count'};
		++$fip_list{$user}{$ip}{'imap'};
		}
		elsif (m#Pop3Server#i) 
		{
		my($ip,$user) = m#.*\s+\[ip=.*;oip=(.*);\]\s*.* failed for\s+\[(.*)\].*$#i;
		$uagent = "pop";
		#print " - ip is $ip, user is $user, agent is $uagent\n";
		#print $_;
		++$ip_list{$user}{$ip};		#we loop by this for report
		++$fip_list{$user}{$ip}{'count'};
		++$fip_list{$user}{$ip}{'pop'};
		}
		elsif (m#http#i) 
		{
		my($user,$ip,$uagent) = m#.*\s+\[name=(.*);oip=(.*);ua=(.*);\].*$#i;
		$uagent = "web";
		#print " - ip is $ip, user is $user, agent is $uagent\n";
		#print $_;
		++$ip_list{$user}{$ip};		#we loop by this for report
		++$fip_list{$user}{$ip}{'count'};
		++$fip_list{$user}{$ip}{'web'};
		}
	}
	elsif (m#AuthRequest#i && ($_ !~ m/zimbra/i)) 
	{
		my($user,$ip,$uagent) = m#.*\s+\[name=(.*);oip=(.*);ua=(.*);\].*$#i;
		++$ip_list{$user}{$ip};
		#$ip_list{$user}{'Agent'} = $uagent;
		if ($audit_log == 1) { print $_; }
		#print " - ip is $ip, user is $user, agent is $uagent\n";
	}
  }
  close (IN);

}


#debug
#print Dumper \%ip_list;
#print Dumper \%fip_list;

for $user (sort {$ip_list{$b} <=> $ip_list{$a}}  keys %ip_list )
{

  print "\n",$user,"\n";


	for $ip (sort {$ip_list{$user}{$b} <=> $ip_list{$user}{$a}}  keys %{$ip_list{$user}} )
	{
		#  See count of how many times
		printf (" [%4d] - %s\n", $ip_list{$user}{$ip},$ip);
		#printf ("%s\n",$ip);
	}

	# failed
	for $ip (sort {$fip_list{$user}{$b} <=> $fip_list{$user}{$a}}  keys %{$fip_list{$user}} )
	{
		#  See count of how many times
		printf (" [%4d] - %s ", $fip_list{$user}{$ip}{count},$ip);
		printf " failed web " if exists $fip_list{$user}{$ip}{'web'};
		printf " failed imap " if exists $fip_list{$user}{$ip}{'imap'};
		printf " failed pop " if exists $fip_list{$user}{$ip}{'pop'};
		printf ("\n");
#%%% we can have different user's in fip_list that ip_list doesn't have.
		#printf ("%s\n",$ip);
	}
}

It might be useful to others.

Re: Simple program to report successful/fail ip logins and sorted by count

Posted: Thu Jun 14, 2018 1:50 pm
by MedfastSupport
I love it. great script

Re: Simple program to report successful/fail ip logins and sorted by count

Posted: Thu Jun 14, 2018 9:32 pm
by milauria
This is why I love open-source! Nice and easy... thanks

Re: Simple program to report successful/fail ip logins and sorted by count

Posted: Thu Jun 14, 2018 11:37 pm
by DavidMerrill
Nicely done...and in Perl!

Re: Simple program to report successful/fail ip logins and sorted by count

Posted: Thu Sep 20, 2018 2:28 pm
by didier
Hello, thank you for the script, it's happiness.
Su my zimbra (8.6) it works perfectly except for the request imapssl, it does not appear, I only have the web. And since I'm bad at perl :( ...
Do you have an idea ?

Do you have a v2 in the pipe :roll: ? Have the connection hours and everything .. But I exaggerate. :oops:

Thank you in advance.

Re: Simple program to report successful/fail ip logins and sorted by count

Posted: Thu Sep 20, 2018 5:13 pm
by JDunphy
imap ssl should be displayed... The program is just doing this.

Code: Select all

grep ImapServer /opt/zimbra/log/audit.log
This appears to be what I am currently using.

Code: Select all

#!/usr/bin/perl

use Data::Dumper qw(Dumper);

%ip_list = ();  #ip list
%fip_list = ();	#failed ip list
$audit_log = 0;	#todays logging

chdir "/opt/zimbra/log";

for (glob 'audit.log*') {
#for (glob 'audit.log') {

  # audit.log is always todays stuff
  #print "Opening file $_";
  if ($_ eq 'audit.log')
  {
     $audit_log = 1;
     open (IN, sprintf("cat %s |", $_))
       or die("Can't open pipe from command 'zcat $filename' : $!\n");
  }
  else
  {
     $audit_log = 0;
     open (IN, sprintf("zcat %s |", $_))
       or die("Can't open pipe from command 'zcat $filename' : $!\n");
  } 

  while (<IN>) 
  {
	#if (m#invalid password#i)
	if (m#invalid#i)
	{ 
		#print $_;
		if ((m#ImapServer#i) && !(m#INFO#)) {
		my($ip,$user) = m#.*\s+\[ip=.*;oip=(.*);via=.*;\]\s*.* failed for\s+\[(.*)\].*$#i;
		$uagent = "imap";
		#print " - ip is $ip, user is $user, agent is $uagent\n";
		#print $_;

		++$ip_list{$user}{$ip};		#we loop by this for report
		++$fip_list{$user}{$ip}{'count'};
		++$fip_list{$user}{$ip}{'imap'};
		}
		elsif (m#Pop3Server#i) 
		{
		my($ip,$user) = m#.*\s+\[ip=.*;oip=(.*);\]\s*.* failed for\s+\[(.*)\].*$#i;
		$uagent = "pop";
		#print " - ip is $ip, user is $user, agent is $uagent\n";
		#print $_;
		++$ip_list{$user}{$ip};		#we loop by this for report
		++$fip_list{$user}{$ip}{'count'};
		++$fip_list{$user}{$ip}{'pop'};
		}
		elsif (m#http#i) 
		{
		my($user,$ip,$uagent) = m#.*\s+\[name=(.*);oip=(.*);ua=(.*);\].*$#i;
		$uagent = "web";
		#print " - ip is $ip, user is $user, agent is $uagent\n";
		#print $_;
		++$ip_list{$user}{$ip};		#we loop by this for report
		++$fip_list{$user}{$ip}{'count'};
		++$fip_list{$user}{$ip}{'web'};
		}
	}
	elsif (m#AuthRequest#i && ($_ !~ m/zimbra/i)) 
	{
		my($user,$ip,$uagent) = m#.*\s+\[name=(.*);oip=(.*);ua=(.*);\].*$#i;
		++$ip_list{$user}{$ip};
		#$ip_list{$user}{'Agent'} = $uagent;
		if ($audit_log == 1) { print $_; }
		#print " - ip is $ip, user is $user, agent is $uagent\n";
	}
  }
  close (IN);

}


#debug
#print Dumper \%ip_list;
#print Dumper \%fip_list;

for $user (sort {$ip_list{$b} <=> $ip_list{$a}}  keys %ip_list )
{

  print "\n",$user,"\n";


	for $ip (sort {$ip_list{$user}{$b} <=> $ip_list{$user}{$a}}  keys %{$ip_list{$user}} )
	{
		#  See cont of how many times
		printf ("Total [%4d] - %15s ", $ip_list{$user}{$ip},$ip);
                if (exists $fip_list{$user}{$ip}) {
                   if ($fip_list{$user}{$ip}{count})
                   {
		        printf (" Failed [%4d] - %s ", $fip_list{$user}{$ip}{count},$ip);
			printf " failed web [%4d] ", $fip_list{$user}{$ip}{'web'} if exists $fip_list{$user}{$ip}{'web'};
			printf " failed imap [%4d] ",$fip_list{$user}{$ip}{'imap'} if exists $fip_list{$user}{$ip}{'imap'};
			printf " failed pop [%4d] " ,$fip_list{$user}{$ip}{'pop'} if exists $fip_list{$user}{$ip}{'pop'};
                   }
		}
		printf ("\n");
	}

}

Re: Simple program to report successful/fail ip logins and sorted by count

Posted: Fri Sep 21, 2018 7:30 am
by didier
Thank you for your answer.

In the script it is ImapServer which is searched, but in my logs I have rather ImapSSLServer. So I modified the script but it does not put me the imap failed:
But by cons at the end of the files I have a count of imap failed
Total [22211] - Failed [22211] - failed web [ 555] failed imap [21656]

By grep :
grep ImapSSLServer /opt/zimbra/log/audit.log | grep failed | grep didier
2018-09-21 03:36:45,304 WARN [ImapSSLServer-16] [ip=93.116.201.15;] security - cmd=Auth; account=didier@test.fr; protocol=imap; error=authentication failed for [didier@test.fr], invalid password;
By scripts :
didier@test.fr
Total [ 8] - 10.1.2.185
Total [ 8] - 92.171.175.157
Total [ 6] - 2.3.122.206 Failed [ 3] - 2.3.122.206 failed web [ 3]
The web authentication errors are well counted but not the imaps, I tried to modify the scripts but nothing conclusive ...
If you have an idea. Thank you in advance.

Re: Simple program to report successful/fail ip logins and sorted by count

Posted: Fri Sep 21, 2018 12:31 pm
by JDunphy
I see that you are not running nginx so the regular expression is off. It is looking for oip to pull the ip address from which you don't have in your logs. So the logs are completely different. When you move to 8.7+, the proxy became a required feature. In the meantime, Replace this regular expression

Code: Select all

m#.*\s+\[ip=.*;oip=(.*);via=.*;\]\s*.* failed for \[(.*)\].*$#i;
with this.

Code: Select all

m#.*\s+\[ip=(.*);\]\s*.* failed for \[(.*)\].*$#i;
Also, Change ImapServer and PopServer with ImapS and PopS. That should get you going.
So in nginx logs, the regular expression looked like this. That is why the oip= in the original expression.

Code: Select all

2018-09-20 08:53:14,114 WARN  [ImapServer-0] [ip=Y.Y.Y.Y;oip=X.X.X.X;via=Y.Y.Y.Y(nginx/1.7.1);ua=Zimbra/8.7.11_GA_3012;] security - cmd=Auth; account=didier@test.fr; protocol=imap; error=authentication failed for [didier@test.fr], invalid password;

Re: Simple program to report successful/fail ip logins and sorted by count

Posted: Tue Sep 25, 2018 6:24 am
by didier
Thank you indeed it works now!
Thank you for your help and your scripts!

If you're in others I'm interested ;)

Re: Simple program to report successful/fail ip logins and sorted by count

Posted: Sat Nov 03, 2018 4:14 pm
by Labsy
Hi JDunphy,

that one is EXCELLENT! Really one of the most useful log parsers I found around.

Just maybe an idea, why with POP failures it cannot strip "cid=xxxxxx" from IP?
ZCS 8.8 with nginx.
For WEB and IMAP it's OK.

Code: Select all

Total [   1] - 89.143.173.78;cid=221369  Failed [   1] - 89.143.173.78;cid=221369  failed pop [   1]
Total [   1] - 213.143.79.48;cid=184467  Failed [   1] - 213.143.79.48;cid=184467  failed pop [   1]
Total [   1] - 213.143.79.48;cid=218035  Failed [   1] - 213.143.79.48;cid=218035  failed pop [   1]
Total [   1] - 213.143.79.48;cid=234819  Failed [   1] - 213.143.79.48;cid=234819  failed pop [   1]
Total [   1] - 213.143.79.48;cid=254927  Failed [   1] - 213.143.79.48;cid=254927  failed pop [   1]
Total [   1] - 213.143.79.48;cid=302686  Failed [   1] - 213.143.79.48;cid=302686  failed pop [   1]
Total [   1] - 213.143.79.48;cid=182582  Failed [   1] - 213.143.79.48;cid=182582  failed pop [   1]