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.
milauria
Advanced member
Advanced member
Posts: 64
Joined: Mon Aug 15, 2016 12:32 pm

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

Postby milauria » Tue Nov 13, 2018 10:58 pm

Hello -- my single zimbra server runs behind a reverse proxy server (nginx).

As today it reads as all successful connection are coming from the reverse proxy IP.

I noticed that my Zimbra server audit.log trace correctly the "oip=xxx.xxx.xxx.xxx" but someway the script reports the "$ip" as the reverse proxy server IP

Anyway to allow the script read the originating IP ?

Many thanks for sharing this little nice piece of code ... :o


User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 324
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: Release 8.7.11_GA_1854.RHEL6_64.P7
Contact:

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

Postby JDunphy » Tue Nov 13, 2018 11:18 pm

milauria wrote:Hello -- my single zimbra server runs behind a reverse proxy server (nginx).

Anyway to allow the script read the originating IP ?

As today it reads as all successful connection are coming from the reverse proxy IP.

Try this... where X.X.X.X is your single server ip.

Code: Select all

# allow /opt/zimbra/log/audit.log to show correct client ip address
# because we are now behind proxy.
% zmprov mcf +zimbraMailTrustedIP 127.0.0.1 +zimbraMailTrustedIP  X.X.X.X
% zmmailboxdctl restart
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 324
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: Release 8.7.11_GA_1854.RHEL6_64.P7
Contact:

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

Postby JDunphy » Wed Nov 14, 2018 1:01 am

Labsy wrote:Maybe another one for you, since you seem to know what sort is about - would it be possible to sort users by failure count? So healthy ones would be listed first, followed by more and more failed ones.
BTW...Your base program was really nice to work on :)

Hi Labsy,
I took your modifications as my new base and reworked it... This should work much better for large sites that require filtering. I found I liked your ip to domain name feature even for the non failure cases so there is switch for that now too! It allows me to understand what are normal providers for my users. To show only failed accounts which I believe you were asking about, we have some new switches. Note: that fail=ip, fail=all, fail=none are possible options now. Because it can be expensive for large sites to do reverse lookups on large logs, the default is only for login failures and it can be turned off completely via options. Note: -g none| --gethost=all | --gethost=fail

Code: Select all

% check_login.pl --fail=ip

Here is the help from the updated version.

Code: Select all

usage: % check_login.pl
        [--color=<color name (i.e. RED)>]
        [--srchuser=<username>]
        [--fail=<user|ip|none>]
        [--gethost=<all|fail|none>]
        [--help]
    where:
        --color|c: color to be used for FAILED login message information
        --srchuser|s: print ONLY the logins/failed logins for <username>
        --fail|f: if 'user': print ONLY users who have failed logins. If 'ip': print ONLY the failed login attempts. 'none': print all records regardless if failure
        --gethost|g: values of 'all', 'fail' or 'none'. Perform a GETHOSTBYADDR for all IPs, only on FAILED login attempts, or don't perform this action (none)
        --help|h: this message
";
example: % check_login.pl -f=user    #only the accounts with failed logins
         % check_login.pl -f ip      #only the accounts and the ip that failed
         % check_login.pl -fail=ip   # same as above
         % check_login.pl --fail ip  # same as above
         % check_login.pl -f ip -h   #list only ip's that failed for accounts resolve ip to domain name
         % check_login.pl -g fail    #list only accounts that had a failed login
         % check_login.pl -g all     #list all accounts and resolve ip to domain name
         % check_login.pl -c RED -f ip #change color and list only failed ip's     

Here is the revised code.

Code: Select all

#!/usr/bin/perl
#
# NOTE: To jump to the various sections within this program search
#     on the string 'SECTION'
#
# usage: check_login.pl [Options]
#
# ======================================================================

#========================================================================
# SECTION -  Modules, Variables, etc.
#========================================================================
use Term::ANSIColor;
use Data::Dumper qw(Dumper);
use Getopt::Long;
use Socket qw( inet_aton AF_INET );

%ip_list = ();  #ip list
%fip_list = ();   #failed ip list

#========================================================================
# SECTION -  FUNCTIONS
#========================================================================
# Displays program usage
sub usage {

print <<"END";
usage: % check_login.pl
        [--color=<color name (i.e. RED)>]
        [--srchuser=<username>]
        [--fail=<user|ip|none>]
        [--gethost=<all|fail|none>]
        [--help]
    where:
        --color|c: color to be used for FAILED login message information
        --srchuser|s: print ONLY the logins/failed logins for <username>
        --fail|f: if 'user': print ONLY users who have failed logins. If 'ip': print ONLY the failed login attempts. 'none': print all records regardless if failure
        --gethost|g: values of 'all', 'fail' or 'none'. Perform a GETHOSTBYADDR for all IPs, only on FAILED login attempts, or don't perform this action (none)
        --help|h: this message\n";
example: % check_login.pl -f=user    #only the accounts with failed logins
         % check_login.pl -f ip      #only the accounts and the ip that failed
         % check_login.pl -fail=ip   # same as above
         % check_login.pl --fail ip  # same as above
         % check_login.pl -f ip -h   #list only ip's that failed for accounts resolve ip to domain name
         % check_login.pl -g fail    #list only accounts that had a failed login
         % check_login.pl -g all     #list all accounts and resolve ip to domain name
         % check_login.pl -c RED -f ip #change color and list only failed ip's     
         % check_login.pl -s user -f ip -g fail #list all failed ip addresses with ip to domain name
         % check_login.pl -s user@example.com -f ip -g fail #list all failed ip addresses with ip to domain name
END
    exit 0;
}

sub setlists {
    my ($user, $ip, $typeval) = @_;

    ++$ip_list{$user}{$ip};      #we loop by this for report
    ++$fip_list{$user}{$ip}{'count'};
    ++$fip_list{$user}{$ip}{$typeval};

    return;
}

# get the hostname for the iP address if requested
sub gethostname {
    my ($gethost) = @_;
    my $attacker = "";

    if (($gethost =~ m/all/i)
    || (($gethost =~ /fail/i) && ((exists $fip_list{$user}{$ip}) && ($fip_list{$user}{$ip}{count})))) {
   if((gethostbyaddr(inet_aton($ip), AF_INET)) =~ /([a-z0-9_\-]{1,5})?(:\/\/)?(([a-z0-9_\-]{1,})(:([a-z0-9_\-]{1,}))?\@)?((www\.)|([a-z0-9_\-]{1,}\.)+)?([a-z0-9_\-]{3,})(\.[a-z]{2,4})(\/([a-z0-9_\-]{1,}\/)+)?([a-z0-9_\-]{1,})?(\.[a-z]{2,})?(\?)?(((\&)?[a-z0-9_\-]{1,}(\=[a-z0-9_\-]{1,})?)+)?/) {
                   $attacker="$10$11$15";
      }
    }

   return $attacker;
}

# Print results out
sub printresults {
    my($ucolor, $uattr, $user, $msgcolor, $msg) = @_;

    print color($ucolor, $uattr), " ";
    printf "%-47s", $user;
    print color('reset');
    print color($msgcolor), $msg;
    print color('reset');

    return;
}

# Pretty clear - output a big line
sub drawline {
  print "\n------------------------------------------------------------------------------------------------------------\n";
  return;
}
 

#========================================================================
# SECTION -  GET input parameters
#========================================================================
# Get the command line parameters for processing
    my $fcolor = 'YELLOW';
    my $srchuser = '@';
    my $failtype = 'none';
    my $gethost = 'fail';
    my $help, $dbug = 0;
    &GetOptions( "color=s" => \$fcolor,
                "srchuser=s" => \$srchuser,
                "fail=s" => \$failtype,  # user, ip, none
                "gethost=s" => \$gethost,   # all, fail, none
                "debug" => \$dbug,  # turn on debugging
                "help" => \$help);

    # call Help if parameters do not meet expected values or help is requested
    usage() if($help || ($failtype !~ m/^user|ip|none$/i) || ($gethost !~ m/^all|fail|none$/i));


#========================================================================
# SECTION -  PARSE audit.log files & process accordingly
#========================================================================
chdir "/opt/zimbra/log";

for (glob 'audit.log*') {

  $lines = 0;
  $audit_log = $_ eq 'audit.log' ? 1 : 0;
  #print "Opening file $_";
  open (IN, sprintf("zcat -f %s |", $_))
       or die("Can't open pipe from command 'zcat -f $filename' : $!\n");

  # part the audit logs looking for access types
  while (<IN>)
  {
   if (m#invalid#i)
   {
          #print $_;
      if ((m#ImapS#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 $_;
     setlists($user, $ip, $uagent);
      }
      elsif ((m#Pop#i) && !(m#INFO#))
      {
         my($ip,$user) = m#oip=(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3});.* failed for\s+\[(.*)\].*$#i;
         $uagent = "pop";
         #print " - ip is $ip, user is $user, agent is $uagent\n";
         #print $_;
    setlists($user, $ip, $uagent);
      }
      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 $_;
     setlists($user, $ip, $uagent);
      }
      elsif (m#qtp#i)
      {
          my($ip,$user) = m#.*\s+\[name=.*;oip=(.*);oport.*\]\s*.* failed for\s+\[(.*)\].*$#i;
          $uagent = "smtp";
          #print " - ip is $ip, user is $user, agent is $uagent\n";
          #print $_;
     setlists($user, $ip, $uagent);
      }
   }
   elsif (m#AuthRequest#i && ($_ !~ m/zimbra/i))
   {
      my($user,$ip,$uagent) = m#.*\s+\[name=(.*);oip=(.*);ua=(.*);\].*$#i;
      ++$ip_list{$user}{$ip};
      #if ($audit_log == 1) { print $_; }
      #printf "%4d: - ip is %15s, user is %45s, agent is %s\n",$lines,$ip,$user,$uagent;
   }
   $lines++;
  } # End While (<IN>) loop
  close (IN);

}

#========================================================================
# SECTION -  PRINT / MAIN
#========================================================================
#debug
#print Dumper \%ip_list;
#print Dumper \%fip_list;


   drawline();

# Print out the arrays by username. Flag failures.
for $user (sort keys %ip_list )
{

  # Skip this user, if -s parameter is given and user is not in search string
  next if(index($user,$srchuser) == -1);

  # Proceed only  if we're only looking for users who have failed logins recorded
  next if(($failtype =~ /user|ip/i) && !(exists $fip_list{$user}));

  $total = 0;
  $totalf = 0;

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

       #  See count of how many times
   if(($failtype !~ /ip/i)  || (($failtype =~ /ip/) && exists $fip_list{$user}{$ip})) {
             printf ("[%4d] logins from IP %15s ", $ip_list{$user}{$ip},$ip);
   }
        $total = $total+$ip_list{$user}{$ip}; # Count all for this username

   # lookup the domain if requested
   my $attacker = "[" . gethostname($gethost) . "]";

        if ((exists $fip_list{$user}{$ip}) && ($fip_list{$user}{$ip}{count}))
        {
            print color($fcolor);
            printf "%-30s", $attacker if ($gethost !~ /none/i);

            printf " Failed [%4d] : ", $fip_list{$user}{$ip}{count};
       for $etypes (keys %{$fip_list{$user}{$ip}}) {
         next if $etypes =~ /count/;
                printf " using %s  [%4d] ", $etypes, $fip_list{$user}{$ip}{$etypes};
        }
             print color('reset');
             printf ("\n");
             $totalf = $totalf+$fip_list{$user}{$ip}{count}; # Count all failed for this username
         } elsif (($gethost =~ /all/i) && !($failtype =~ /ip/i))
    {
      printf "$attacker\n";
    } elsif ($failtype !~ /ip/) {
            printf ("\n");
    }
   }

   # Print out user information & message totals
   if ($totalf>0)  {
        my $msg = sprintf("%d failed of total %d  login attempts!!!", $totalf, $total);
   $msgcolor = $totalf==$total ? "RED" : "YELLOW";
        printresults("WHITE", "BOLD", $user, $msgcolor, $msg);
   } elsif ($failtype !~ /ip/i) {
        my $msg = " No failed logins. Yeeee :)";
        printresults("WHITE", "BOLD", $user, "GREEN", $msg);
   }

   drawline();
}
   printf ("\n");
   print color('reset');  # make sure we clean up

Next I have some code to incorporate maillog so that a user that is sending out a ton of email with large recipient counts will show with the account information. I will also bring in the bounces so that abnormal bounces are shown. The goal is to show not only the accounts under attack but also accounts that may have become compromised and are now sending out large amounts of email. My github for this is: https://github.com/JimDunphy/ZimbraScripts/blob/master/src/check_login.pl.
Labsy
Outstanding Member
Outstanding Member
Posts: 367
Joined: Sat Sep 13, 2014 12:52 am

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

Postby Labsy » Wed Nov 14, 2018 7:10 am

Hi JDunphy,
that's one huge code rewrite, really love it how you incorporated all changes. And love it being organized :)

BUT there's still work to do - I noticed results are buggy/different, if I switch the order of log parsing elsif's for "http" and "qtp". Results are much different and it looks like WEB and SMTP parsers rule out each other:
QTP before HTTP:

Code: Select all

------------------------------------------------------------------------------------------------------------
[   7] logins from IP      84.41.99.5
[   6] logins from IP    89.212.81.25 [hostmachine.net]              Failed [   6] :  using smtp  [   6]
[   1] logins from IP 212.103.140.219
 user1@domain.com                                  6 failed of total 14  login attempts!!!
------------------------------------------------------------------------------------------------------------
[   2] logins from IP   89.212.238.52
[   2] logins from IP  90.157.166.108
[   1] logins from IP   46.123.249.67
[   1] logins from IP  46.123.250.247
[   1] logins from IP     46.164.7.43
 user2@domain.com                          No failed logins. Yeeee :)
------------------------------------------------------------------------------------------------------------

HTTP before QTP:

Code: Select all

------------------------------------------------------------------------------------------------------------
[   7] logins from IP      84.41.99.5
[   1] logins from IP 212.103.140.219
 user1@domain.com                                   No failed logins. Yeeee :)
------------------------------------------------------------------------------------------------------------
[   2] logins from IP  90.157.166.108
[   2] logins from IP   46.123.249.67 [simobil.net]                  Failed [   1] :  using web  [   1]
[   2] logins from IP   89.212.238.52
[   1] logins from IP     46.164.7.43
[   1] logins from IP  46.123.250.247
 user2@domain.com                         1 failed of total 8  login attempts!!!
------------------------------------------------------------------------------------------------------------

So it makes me wonder if other checks also rule each other out?
If I get some free time I'll check those out, too.
milauria
Advanced member
Advanced member
Posts: 64
Joined: Mon Aug 15, 2016 12:32 pm

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

Postby milauria » Wed Nov 14, 2018 12:22 pm

JDunphy wrote:
milauria wrote:Hello -- my single zimbra server runs behind a reverse proxy server (nginx).

Anyway to allow the script read the originating IP ?

As today it reads as all successful connection are coming from the reverse proxy IP.

Try this... where X.X.X.X is your single server ip.

Code: Select all

# allow /opt/zimbra/log/audit.log to show correct client ip address
# because we are now behind proxy.
% zmprov mcf +zimbraMailTrustedIP 127.0.0.1 +zimbraMailTrustedIP  X.X.X.X
% zmmailboxdctl restart


The zimbraMailTrustedIP was already set correctly
Instead in my nginx reverse proxy I was missing the 2 headers to be able to transfer correctly the real IP:

Code: Select all

proxy_set_header      X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header      Host $http_host;

Thanks anyway for the hint
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 324
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: Release 8.7.11_GA_1854.RHEL6_64.P7
Contact:

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

Postby JDunphy » Wed Nov 14, 2018 2:13 pm

Labsy wrote:Hi JDunphy,
that's one huge code rewrite, really love it how you incorporated all changes. And love it being organized :)

BUT there's still work to do - I noticed results are buggy/different, if I switch the order of log parsing elsif's for "http" and "qtp". Results are much different and it looks like WEB and SMTP parsers rule out each other:

This is one area where I will need your help. I wasn't able to verify your qtp section. When I generated a MUA authentication error it didn't appear in my 8.7+ audit.logs. The only place I could find it was in /var/log/maillog. Could you provide me with the output of:

Code: Select all

% zcat -f /opt/zimbra/log/audit.log* |grep -i invalid | grep qtp

so I could observe that line we are parsing. My guess is that we probably need to pay a little more attention than simply saying anything with http and invalid or qtp and invalid. Should be an easy fix.
I know in my logs, simply asking for qtp will not work because I have lines like this...

Code: Select all

2018-11-04 19:12:39,934 WARN  [qtp2036958521-453494:http://localhost:8080 ...

Which belong to the web interface and not part of smtp authentication. I suppose we could always do something like this:
((m#http#) && (#zclient#)) for the http section... or require 'soap' or 'failed', etc. Without knowing what the qtp line we are pattern matching against, I hesitate to recommend a fix. Certainly with my logs, it would yield incorrect results to place qtp in front of http in the parsing order.

Thanks

Jim
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 324
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: Release 8.7.11_GA_1854.RHEL6_64.P7
Contact:

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

Postby JDunphy » Wed Nov 14, 2018 5:56 pm

milauria wrote:The zimbraMailTrustedIP was already set correctly
Instead in my nginx reverse proxy I was missing the 2 headers to be able to transfer correctly the real IP:

Code: Select all

proxy_set_header      X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header      Host $http_host;

Thanks anyway for the hint

Yep that is it. Glad you got it working. I am guessing for whatever reason this wasn't defined.

Code: Select all

% zmlocalconfig zimbra_http_originating_ip_header
zimbra_http_originating_ip_header = X-Forwarded-For

I went looking to why and ended up in a little self discovery of my own how this all worked... Didn't realize that zmproxyconfgen had a dry-run option without rewriting the nginx files.

Code: Select all

% zmproxyconfgen -v -n

Frustrating... I think I prefer the old way of just updating a config file with an editor sometimes. :-)
Labsy
Outstanding Member
Outstanding Member
Posts: 367
Joined: Sat Sep 13, 2014 12:52 am

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

Postby Labsy » Wed Nov 14, 2018 9:25 pm

JDunphy wrote:This is one area where I will need your help. I wasn't able to verify your qtp section. When I generated a MUA authentication error it didn't appear in my 8.7+ audit.logs. The only place I could find it was in /var/log/maillog. Could you provide me with the output of:

Code: Select all

% zcat -f /opt/zimbra/log/audit.log* |grep -i invalid | grep qtp

Jim

Hi Jim,
here's the output you asked for.
Seems like there are two different types of logs under "qtp" type:

Code: Select all

04.11.2018 17:12:41 WARN  [qtp1684106402-196362:http://localhost:8080/service/soap/AuthRequest] [name=sara@domain.com;oip=46.123.248.184;ua=zclient/8.8.7_GA_1964;soapId=1e47190;] security - cmd=Auth; account=sara@domain.com; protocol=soap; error=authentication failed for [sara@domain.com], invalid password;
04.11.2018 20:52:46 WARN  [qtp1684106402-197556:https:https://zimbra.domain.com:7073/service/admin/soap/] [name=info@domain.com;oip=145.249.106.201;oport=41692;oproto=smtp;soapId=1e4782f;] security - cmd=Auth; account=info@domain.com; protocol=soap; error=authentication failed for [info@domain.com], invalid password;

As you already said, one is for WEB interface, and the other for SMTP failures.
I think we need to parse them BOTH, each under its own category.
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 324
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: Release 8.7.11_GA_1854.RHEL6_64.P7
Contact:

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

Postby JDunphy » Wed Nov 14, 2018 10:01 pm

Labsy wrote:Seems like there are two different types of logs under "qtp" type:

Code: Select all

04.11.2018 17:12:41 WARN  [qtp1684106402-196362:http://localhost:8080/service/soap/AuthRequest] [name=sara@domain.com;oip=46.123.248.184;ua=zclient/8.8.7_GA_1964;soapId=1e47190;] security - cmd=Auth; account=sara@domain.com; protocol=soap; error=authentication failed for [sara@domain.com], invalid password;
04.11.2018 20:52:46 WARN  [qtp1684106402-197556:https:https://zimbra.domain.com:7073/service/admin/soap/] [name=info@domain.com;oip=145.249.106.201;oport=41692;oproto=smtp;soapId=1e4782f;] security - cmd=Auth; account=info@domain.com; protocol=soap; error=authentication failed for [info@domain.com], invalid password;


This is what I added to my version:
For http

Code: Select all

elsif ((m#http#i) && (m#zclient#))

and for smtp

Code: Select all

elsif ((m#oproto=smtp#) && (m#failed#))

While I was at it, I changed the RE to this for the smtp case.

Code: Select all

 my($ip,$user) = m#oip=(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3});.* failed for\s+\[(.*)\].*$#i;
 

Changes are in my github if you want to test. Unfortunately, the best I can do to test this on 8.7+ is plug your log entries into a test script which is what I did. When I move to 8.8+ it will be nice to have more capability in audit.log. In the meantime, I will add some maillog stuff to compensate. I put a sample of what I am thinking of including under my github called... check_recipient.pl ... It will identify users that have added more than 20 recipients and had more than 10 bounces. Those numbers are too low but they did uncover a few interesting users for me. I am thinking of including the number of bounces a users has in addition to max recipient count to that area you created below the account information with the totals for each account.
rickygm
Posts: 15
Joined: Sat Sep 13, 2014 1:48 am

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

Postby rickygm » Wed Nov 28, 2018 4:57 pm

I think it would be a good idea to add the date and time.

Return to “Administrators”

Who is online

Users browsing this forum: No registered users and 12 guests