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

Discuss your pilot or production implementation with other Zimbra admins or our engineers.
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 889
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: 9.0.0_P39 NETWORK Edition

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

Post 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.
MedfastSupport
Posts: 2
Joined: Fri Jun 08, 2018 12:28 pm

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

Post by MedfastSupport »

I love it. great script
milauria
Advanced member
Advanced member
Posts: 96
Joined: Mon Aug 15, 2016 12:32 pm

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

Post by milauria »

This is why I love open-source! Nice and easy... thanks
User avatar
DavidMerrill
Advanced member
Advanced member
Posts: 126
Joined: Thu Jul 30, 2015 2:44 pm
Location: Portland, ME
ZCS/ZD Version: 8.8.15 P19
Contact:

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

Post by DavidMerrill »

Nicely done...and in Perl!
___________________________________
David Merrill - Zimbra Practice Lead
OTELCO Zimbra Hosting, Licensing and Professional Services
Zeta Alliance
didier
Posts: 3
Joined: Thu Sep 20, 2018 2:01 pm

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

Post 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.
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 889
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: 9.0.0_P39 NETWORK Edition

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

Post 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");
	}

}
didier
Posts: 3
Joined: Thu Sep 20, 2018 2:01 pm

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

Post 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.
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 889
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: 9.0.0_P39 NETWORK Edition

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

Post 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;
didier
Posts: 3
Joined: Thu Sep 20, 2018 2:01 pm

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

Post by didier »

Thank you indeed it works now!
Thank you for your help and your scripts!

If you're in others I'm interested ;)
Labsy
Outstanding Member
Outstanding Member
Posts: 411
Joined: Sat Sep 13, 2014 12:52 am

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

Post 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]
Post Reply