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.
pup_seba
Outstanding Member
Outstanding Member
Posts: 506
Joined: Sat Sep 13, 2014 2:43 am
Location: Tarragona - Spain

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

Postby pup_seba » Sat Nov 03, 2018 4:48 pm

Script looks great and very usefull, so first of all, big thanks to JDunphy for sharing :)

I have no clue about perl and is kind of hard for me to understand what the output of the script means. From the output, JDunphy explanaitions and my 0 understanding of perl, I would say:
- Output is grouped by users.
- For each user, there seems to be a line for each IP the login attempt was tried.

I have this output from a real PR enviroment.

01. user1@domain1.com
02. Total [ 1] - xxx.xxx.xxx.1
03.
04. user1@domain2.com
05. Total [ 1] - xxx.xxx.xxx.2
06.
07. user1@domain4.com
08. Total [ 7] - 172.16.88.149 Failed [ 5] - 172.16.88.149 failed web [ 5]
09.
10. user1@domain3.com
11. Total [ 1] - xxx.xxx.xxx.1
12.
13. user2@domain2.com
14. Total [ 2] - xxx.xxx.xxx.3 Failed [ 2] - xxx.xxx.xxx.3 failed web [ 2]
15.
16. user3@domain2.com
17. Total [ 1] - xxx.xxx.xxx.4 Failed [ 1] - xxx.xxx.xxx.4 failed web [ 1]
18. Total [ 1] - xxx.xxx.xxx.5 Failed [ 1] - xxx.xxx.xxx.5 failed web [ 1]

Things I fail to understand:
a. When no detail like "failed web" is given (examples line 2 and 5), what does it means?
b. Why can't I see failed login attempts for pop and imap?
c. How to interpet the "Total" in line 8.
d. How to interpret the "Failed [ 5]" in line 8.

Thanks!


Sebastián Greco
Infrastructure Developer @https://www.essiprojects.com/
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 322
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 » Sat Nov 03, 2018 5:15 pm

I don't have any pop accounts here anymore so that is probably the reason for the bug... If you can do this, I could repair the regular expression. Do this...

Code: Select all

grep PopServer /opt/zimbra/log/audit.log

Post 1 or 2 lines is all I would need. If nothing displays then do grep -i pop /opt/zimbra/log/audit.log

Answering your questions about the output.
Total represents all connections from that ip address. 1st Failed represents total failed and the rest represents of that type.
So if we look at line 14, we can determined a few things for user@domain2.com
1) There were 2 total accesses by ip address xxx.xxx.xxx.3
2) Both of those failed
3) They were both from the web interface (ie. no pop or imap failures)

In contrast, line 8 says that the user typed their password 5 times wrong from the web interface but did manage to correctly do it twice. I would say they don't use a password manager if this is one of the valid addresses they come in from.

Note: you could see multiple types of fails on the same line... ie. Failed web, Failed imap, Failed pop ... or at least It should so that total failed would make more sense to why it is present.

Did this answer all your questions? If you believe you are not seeing all the login attempts for pop and imap, you might need to provide a little more information. It appears to work with 8.7.11 that I run but this was done for an active attack years ago so there was not a lot of thought put into it so it wouldn't surprise me if those regular expressions don't work for variations of log output... ie. not having a proxy for sure.

Given you have those blank lines... 3, 6, 12, 15, etc... that tells me we have a parsing issue most likely with the pop regular expression.

HTH,

Jim
pup_seba
Outstanding Member
Outstanding Member
Posts: 506
Joined: Sat Sep 13, 2014 2:43 am
Location: Tarragona - Spain

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

Postby pup_seba » Sat Nov 03, 2018 5:58 pm

Hi!

From "grep -i pop /opt/zimbra/log/audit.log":
2018-11-03 18:17:29,915 INFO [Pop3SSLServer-0] [ip=192.168.8.76;oip=xxx.xxx.xxx.xxx;cid=2514;] security - cmd=Auth; account=someuser@somedomain.com; protocol=pop3;

All lines have same structre and differences among them is in regards time, ip, oip, cid and account.

From "grep -i imap /opt/zimbra/log/audit.log"
2018-11-03 18:04:19,699 INFO [ImapSSLServer-198] [ip=192.168.8.76;oip=xxx.xxx.xxx.xxx;via=192.168.8.76(nginx/1.7.1);ua=Zimbra/8.8.9_GA_3019;cid=2489;] security - cmd=Auth; account=someuser@somedomain.com; protocol=imap;

No output if I search "grep -i popserver /opt/zimbra/log/audit.log" or "grep -i imapserver". Is adding an OR possible here? In bold the things I add as most likely are wrong perl code...I write them just to get the idea :) sorry!
if (((m#ImapServer#i) OR (m#ImapSSLServer#i)) && !(m#INFO#)) {

"Did this answer all your questions?" Yes! Thank you! :) that answer all the questions. Basically, I was not understanding that the difference between total and the detailed failed login attempts, is showing the successfull login attempts :)

Zimbra version is 8.8.9_GA_3019. I'm running this script in a server with store role. There is other server with LDAP and a 3rd server with mta/proxy.

Thank you!
Sebastián Greco
Infrastructure Developer @https://www.essiprojects.com/
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 322
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 » Sat Nov 03, 2018 6:54 pm

Looks like 2 lines to change. So give this a try.

Change

Code: Select all

if ((m#ImapServer#i) && !(m#INFO#))

to this

Code: Select all

if ((m#Imap#i) && !(m#INFO#)) {

Change

Code: Select all

elsif (m#Pop3Server#i)

to this

Code: Select all

elsif ((m#Pop#i) && !(m#INFO#))


I'll update my github account once I find where I put the code. :-)
pup_seba
Outstanding Member
Outstanding Member
Posts: 506
Joined: Sat Sep 13, 2014 2:43 am
Location: Tarragona - Spain

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

Postby pup_seba » Sat Nov 03, 2018 7:11 pm

Yeah :) that works perfectly!

Thank you very much!!!
Sebastián Greco
Infrastructure Developer @https://www.essiprojects.com/
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 » Sun Nov 11, 2018 10:48 am

Beside that, for proper POP3 parsing on my ZCS 8.8 I had to change regex in line 47 from this:

Code: Select all

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

to this:

Code: Select all

my($ip,$user) = m#.*\s+\[ip=.*;oip=(.*);cid=.*;\]\s*.* failed for\s+\[(.*)\].*$#i;

Without this change IP for POP3 access is displayed and sorted with cid=xy included, which messes things up.
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 322
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 » Sun Nov 11, 2018 3:40 pm

Very nice fix... that oip=(.*) is too greedy ... I wonder if this would work for both cases 8.7+ and 8.8+ with cid included or not in the logs.

Code: Select all

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

I tried to simulate the 8.8+ log pattern with oip=x.x.x.x;cid=xxxx; and the above seemed to work.
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 » Mon Nov 12, 2018 10:56 pm

Yep, beauty of regex...works even better :)
BTW...I've extended your code to also look for and analyze SMTP failures and to be able to only display specific account, included in search string. Here's what I came so far...not ideal, but serves me as a tool for helpdesk.

BTW...still cannot figure it out how to sort user accounts alphabetically. Idea?

Code: Select all

#!/usr/bin/perl

use Term::ANSIColor qw(:constants);
use Data::Dumper qw(Dumper);
use Getopt::Std;
use Socket qw( inet_aton AF_INET );

getopts ( 's:' );

if (not defined $opt_s) {$searchuser = "@"}
else {$searchuser = $opt_s}

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

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#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 $_;
       ++$ip_list{$user}{$ip};      #we loop by this for report
       ++$fip_list{$user}{$ip}{'count'};
       ++$fip_list{$user}{$ip}{'imap'};
      }
      elsif (m#Pop3S#i)
      {
       #my($ip,$user) = m#.*\s+\[ip=.*;oip=(.*);\]\s*.* failed for\s+\[(.*)\].*$#i;
       my($ip,$user) = m#.*\s+\[ip=.*;oip=(.*);cid=.*;\]\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#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 $_;
           ++$ip_list{$user}{$ip};      #we loop by this for report
           ++$fip_list{$user}{$ip}{'count'};
           ++$fip_list{$user}{$ip}{'smtp'};
          }
          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 $_; }
      #printf "%4d: - ip is %15s, user is %45s, agent is %s\n",$lines,$ip,$user,$uagent;
   }
   $lines++;
  } # End While (<IN>) loop
  close (IN);

}

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

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

  if(index($user,$searchuser) == -1) { next; } # Skip this user, if -s parameter is given and user is not in search string
  #print WHITE BOLD, ("\n",$user,"\n"), RESET;
  #print "$user\n"; next;
  print "\n";
  print "\n------------------------------------------------------------------------------------------------------------\n";

  $total = 0;
  $totalf = 0;

   for $ip (sort {$ip_list{$user}{$b} <=> $ip_list{$user}{$a}}  keys %{$ip_list{$user}} )
   {
      #  See cont of how many times
         printf ("[%4d] logins from IP %15s ", $ip_list{$user}{$ip},$ip);
         $total = $total+$ip_list{$user}{$ip}; # Count all for this username
                 if (exists $fip_list{$user}{$ip})
                 {
           if ($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";
                        }
                        print YELLOW;
                        printf " Failed [%4d] : %15s ", $fip_list{$user}{$ip}{count},$ip;
                printf " using web  [%4d] ", $fip_list{$user}{$ip}{'web'}  if exists $fip_list{$user}{$ip}{'web'};
                printf " using imap [%4d] ", $fip_list{$user}{$ip}{'imap'} if exists $fip_list{$user}{$ip}{'imap'};
                printf " using pop  [%4d] ", $fip_list{$user}{$ip}{'pop'}  if exists $fip_list{$user}{$ip}{'pop'};
                        printf " using smtp [%4d] ", $fip_list{$user}{$ip}{'smtp'} if exists $fip_list{$user}{$ip}{'smtp'};
                        print "[$attacker]";
                        print RESET;
                        $totalf = $totalf+$fip_list{$user}{$ip}{count}; # Count all failed for this username
           }
         }
       printf ("\n");
   }
    if (($totalf>0) && ($totalf==$total)) {
                print WHITE BOLD, " "; printf "%-47s", $user; print RESET; print RED, (" $totalf failed of total $total login attempts!!!"); print RESET;
        } elsif (($totalf>0) && ($totalf!=$total)) {
                print WHITE BOLD, " "; printf "%-47s", $user; print RESET; print YELLOW, (" $totalf failed of total $total login attempts!"); print RESET;
        } else {
                print WHITE BOLD, " "; printf "%-47s", $user; print RESET; print GREEN, (" No failed logins. Yeeee :)"); print RESET;
        }
   print "\n------------------------------------------------------------------------------------------------------------\n";
   #print "\n\n";
}
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 322
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 » Mon Nov 12, 2018 11:52 pm

Labsy wrote:BTW...still cannot figure it out how to sort user accounts alphabetically. Idea?

Well done and colour too. Fancy! :-)
Change this line:

Code: Select all

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

to this

Code: Select all

for $user (sort keys %ip_list )

And it should sort by user for you. As to why it didn't do this to begin with... I was sorting initially by ip address and then decided to add stuff including failures and forgot. Oops. ;-)
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 » Tue Nov 13, 2018 9:19 am

JDunphy wrote:
Labsy wrote:...Change this line:

Code: Select all

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

to this

Code: Select all

for $user (sort keys %ip_list )

And it should sort by user for you...
Excellent!
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 :)

Return to “Administrators”

Who is online

Users browsing this forum: Baidu [Spider] and 23 guests