logwatch and http log analysis script.

I have had this website set up for a while now. Since day one I have perused the logs to see the activity but I never really had the time to correlate some of the activity. Well, I just wrote a some scripts to grab the data, put it into a database and use the sql commands to obtain the relationships

Except for the few days when it spiked, the scans are pretty consistent. Something out there is always probing the site waiting for a weakness, like zombies at the gates. Just waiting.

Obvious things:

  1. The bad guys really like to target Windows ms-sql.
  2. China is the most prominent on the port scans, followed by … the United States.
  3. Lots of people are trying to break in using the login page by guessing a password.

I have printed some snippets of the canned reports that the script produces. The last report, the http login, is where I try to determine who is hitting my login page and attempting to break in by guessing the password.
There seems to be a lot of that going on. My only advice to you, dear reader, is to read as much as you can on securing a Word Press web site and then secure it in depth.

Here are the scripts. Hopefully this might help someone if for nothing else its a good little perl/mysql project.

The logwach data for the port scanning looks like this and uses the default logwatch set up.

The header is required.

Notice that it requires “Detail Level of Output: 0”

################### Logwatch 7.3.6 (05/19/07) ####################
        Processing Initiated: Sat Apr 20 03:10:13 2013
        Date Range Processed: yesterday
                              ( 2013-Apr-19 )
                              Period is day.
      Detail Level of Output: 0
              Type of Output: unformatted
           Logfiles for Host: zebulak01
  ##################################################################

 --------------------- iptables firewall Begin ------------------------

 Listed by source hosts:
 Dropped 151 packets on interface eth0
   From 14.42.192.130 - 1 packet to udp(5060)
   From 24.151.54.23 - 6 packets to icmp(8)
   From 58.218.209.36 - 2 packets to tcp(1433)
. . . . . . .
From 94.240.9.253 - 1 packet to tcp(5900)

---------------------- iptables firewall End -------------------------

The http logs use the “combined” log option.

The scripts. These were built on a Fedora system but should work on any *nix system.

log_crunch.pl
create_logwatch.sql

type log_crunch.pl -h for help.

./log_crunch.pl -h
Usage:
./log_crunch.pl:   [options]
      where:
            -f datafile of logwatch data from an email - type = unformatted
            -b insert log data from file into database using bulk load. 
            -i insert log data from file into database using insert statements. Very slow 

            -w get the nmap-services file , requires wget
            -s datafile.  load services data into services table of database and exit
            -l load services data using insert statement. default is to bulk load. -l is very slow.

            -a load the http logs. Assumes logs written in combined format and will only load the log entries with POST to wp-login.php
            -m filename - load data from mod_evasive. data is in the form of following, one per line.
                    Thu, 09 May 2013 17:45:52 -0500 mod_evasive HTTP Blacklisted 76.73.235.105

            -t ip-address - lookup a single ip address and exit - like whois at the command line but uses perl modules
            -r print a report. Prints to standard out.
            -v verbose on some options
            -d debug
            -h help - this message

To load the logwatch data, you have to copy it to a file and  the run

log_crunch.pl -f datafile -b

Be careful not to load twice or you will get duplicate entries.

The script will also accommodate mod_evasive data if you have that set up. I get the data from the raw email messages that mod_evasive sends to me.

A snippet of a sample report follows. This is generated with log_crunch.pl -r > textfile.
It will seem to hang but it is simply waiting for you to type in the mysql password.
You need to set up the user for the database. Do this as you see fit.

Report Generated at: Fri May 31 21:48:41 2013
==============================================================================

DATE ACTIVITY
------------------------------------------------------------------------------
Item       Process Date Frequency
---------- ------------ ----------
1          2013-05-30   48        
2          2013-05-29   49        
3          2013-05-28   68        
4          2013-05-22   63        
5          2013-05-21   58        
6          2013-05-20   58        
7          2013-05-19   51        
8          2013-05-18   53        
9          2013-05-17   57        
10         2013-05-16   89        
11         2013-05-15   69        
12         2013-05-14   72        
13         2013-05-13   52        
14         2013-05-12   67        
15         2013-05-11   56        
16         2013-05-10   75        
17         2013-05-09   53        
18         2013-05-08   64        
19         2013-05-07   73        
20         2013-05-06   56        
21         2013-05-03   61        
22         2013-05-02   83        
23         2013-05-01   70        
24         2013-04-30   61        
25         2013-04-29   69        
26         2013-04-28   50        
27         2013-04-27   62        
28         2013-04-26   58        
29         2013-04-25   53        
30         2013-04-24   43        
31         2013-04-23   36        
32         2013-04-21   58        
33         2013-04-20   110       
34         2013-04-19   212       
35         2013-04-18   187       
36         2013-04-17   179       
37         2013-04-16   176       
38         2013-04-15   267       
39         2013-04-14   277       
40         2013-04-13   309       
41         2013-04-11   161       
42         2013-04-10   113       

COUNTRY ACTIVITY
------------------------------------------------------------------------------
Item       Country                         Frequency
---------- ------------------------------  ----------
1          China                          583       
2          United States                  269       
3          India                          132       
4          Russian Federation             88        
5          Republic of Korea              71        
6          Taiwan Province of C           67        
7          Brazil                         63        
8          Germany                        54        
9                                         45        
10         Turkey                         29        
11         Ukraine                        23        
12         Iran (Islamic Republ           21        
13         United Kingdom                 20        
14         Canada                         20        
15         France                         19        
16         Viet Nam                       16        
17         Mexico                         16        
18         Japan                          16        
19         Hong Kong Special Ad           15        
20         Netherlands                    15        
21         Morocco                        13        
22         Spain                          12        
23         Colombia                       12        
24         Indonesia                      11        
25         Thailand                       11        
26         Chile                          11        
27         Italy                          10        
 . . . . . .
IP ACTIVITY - sort by count
------------------------------------------------------------------------------
Item       IP Address         IP Name                                           Country                       Frequency
---------- ------------------ ------------------------------------------------- ----------------------------- ----------
1          178.162.231.211    pardis2.langgo.ir                                  Germany                        63        
2          24.151.54.23       24-151-54-23.dhcp.nwtn.ct.charter.com              United States                  42        
3          88.98.34.229       no-dns-yet-88-98-34-229.zen.net.uk                 United Kingdom                 42        
4          61.77.93.203       No name found                                      Republic of Korea              40        
5          216.240.147.167    mail.gb-host.com                                   United States                  33        
6          175.45.25.35       No name found                                      Hong Kong Special Ad           21        
7          209.190.6.252      fc.6.be.static.xlhost.com                          United States                  20        
8          109.230.213.150    No name found                                      Germany                        20        
9          10.228.237.218     No name found                                      United States                  18        
10         203.135.30.94      No name found                                      Pakistan                       18        
11         69.175.64.173      converts.cinderblockers.com                        United States                  18        
12         41.249.251.134     static41-134-251-249-251.adsl41-16.iam.net.ma      Morocco                        16        
13         110.11.202.187     No name found                                      Republic of Korea              15        
14         177.100.112.216    177-100-112-216.viacaboip.com.br                   Brazil                         15        
15         190.147.205.215    Static-IP-cr190147205215.cable.net.co              Colombia                       15        
16         112.14.156.0       No name found                                      China                          15        
17         184.106.116.141    184-106-116-141.static.cloud-ips.com               United States                  13        
18         112.175.243.40     No name found                                      Republic of Korea              13        
19         65.121.135.196     65-121-135-196.dia.static.qwest.net                United States                  12        
20         75.126.86.3        75.126.86.3-static.reverse.softlayer.com           United States                  12        
21         80.82.66.113       evilfedora.info                                    Netherlands                    11        
22         200.98.161.134     200-98-161-134.clouduol.com.br                     Brazil                         11        
23         206.72.198.84      No name found                                      United States                  10        
24         190.0.40.234       Static-BAFibra190-0-40-234.epm.net.co              Colombia                       10        
25         117.192.23.3       No name found                                      India                          10        
26         173.203.71.192     173-203-71-192.static.cloud-ips.com                United States                  10        
27         2.184.200.205      No name found                                      Iran (Islamic Republ           10        
28         216.38.25.68       No name found                                      United States                  9     
 . . . . .

PORT ACTIVITY
------------------------------------------------------------------------------
Item       Port     Port Name            Protocol Frequency
---------- -------- -------------------- -------- ----------
1          1433     ms-sql-s             tcp      734       
2          8080     http-proxy           tcp      532       
3          3306     mysql                tcp      414       
4          3389     ms-wbt-server        tcp      309       
5          6666     irc                  tcp      252       
6          23       telnet               tcp      142       
7          5060     sip                  udp      98        
8          25       smtp                 tcp      82        
9          3128     squid-http           tcp      70        
10         8443     https-alt            tcp      64        
11         5900     vnc                  tcp      64        
12         808      ccproxy-http         tcp      40        
13         8000     http-alt             tcp      33        
14         4899     radmin               tcp      33        
15         81       hosts2-ns            tcp      29        
16         21       ftp                  tcp      25        
17         8081     blackice-icecap      tcp      20        
18         1080     socks                tcp      17        
19         9090     zeus-admin           tcp      17        
20         2967     symantec-av          tcp      16        
21         110      pop3                 tcp      16        
22         8118     privoxy              tcp      14        
23         53       domain               tcp      12        
24         9000     cslistener           tcp      12        
25         5631     pcanywheredata       tcp      9         
26         8888     sun-answerbook       tcp      9         
27         8181     unknown              tcp      7      

HTTP_LOGIN ACTIVITY
------------------------------------------------------------------------------
Item       IP Address         IP Name                                           Frequency 
---------- ------------------ ------------------------------------------------- ----------
1          91.224.160.135     hosted-by.bergdorf-group.com                       2169      
2          207.58.170.203     london.succeeddesigns.com                          2052      
3          67.228.244.10      stcommunications2.sttelhost.com                    2050      
4          89.163.166.234     89.163.166.234.static.rdns-uclo.net                2046      
5          173.212.247.98     server.optimizedsales.com                          2046      
6          210.245.90.150     ns150.nhanhoa.com                                  2045      
7          199.47.222.251     vmw01.kahunahost.com                               2000      
8          163.43.132.41      toyomi.komako.net                                  1164      
9          85.214.45.181      h2081923.stratoserver.net                          1112      
10         188.190.98.26      hosted-in.infiumhost.com                           969       
11         94.23.27.29        ns367628.ovh.net                                   948       
12         77.66.3.219        No name found                                      917       
13         46.252.193.47      ip-46-252-193-47.ip.secureserver.net               801       
14         82.194.82.102      hs-1086.dedicated.hostalia.com                     704       
15         178.255.225.89     89.225.255.178.static.occentus.net                 673       
16         46.32.226.96       ds-61525.ds-10.com                                 638       
17         37.1.223.19        No name found                                      610       
18         118.69.198.230     No name found                                      567       
19         184.107.237.66     minecraft-multiplayer.com                          546       
20         108.163.128.206    No name found                                      528       
21         74.117.220.10      ns10.dnchosting.com                                511       
22         188.143.232.153    No name found                                      497       
23         173.166.75.217     hostvermont.com                                    495       
24         87.106.133.227     s15350380.onlinehome-server.info                   473       
25         93.114.43.144      hosting.mediaserv.ro                               457       
26         188.165.243.45     ns390077.ovh.net                                   418       
27         89.237.41.3        hosting.trktvs.ru                                  414       
28         176.31.234.69      ns227417.ovh.net                                   336       
29         83.170.121.209     demeter.safeukdns.net                              330       
30         188.143.233.220    No name found                                      320

Leave a Reply

Your email address will not be published. Required fields are marked *

*