As an old school system administrator this is a scary welcome. I do not want ANY bots, scripts, or other ips attempting logins on my servers. There are many ways to block, firewall, redirect, and be proactive to lessening these types of login attempts. What kinda fun is that? That also doesn’t change the fact that they are still trying to “hack” anything online with an IP address. I do not like hackers or login bots and I have a lot of IP addresses.

Today I had an idea to trap the login bot’s ip addresses, log them to a public website, while automating an abuse complaint back to the originating ISP. My goal in making this setup will be something I can install into many systems and collect thousands of ips. Maybe even a unix flavor repo I can yum install as tests on various systems I currently administrate. Some of my other goals here are to use basic command line commands and basic procedures. Eventually I would like to have a system of reporting the IPs off the server via an API versus using a local database. For that some transport method will be pre-requisite anyway so for now I do not want to focus on that.

First thing I do is create a database `failed_logins`.`ips` and then find some sweet command lines that will process my /var/log/secure and the log rotate /var/log/secure-[date] files for “failed”, count, and create a file of IPs and counts. For now I am only concerned with the ip and # of times its trying to login. I could get timestamps for all failures, what ports, usernames, but again, moving fast, pass. The command I settled on was:

Third, I need a way to process this data to have it ready for the final goals: available for website and a notification back to ISP. Since data will be processed weekly, stored indefinitely, and it is possible for an IPs login attempts to continue through log rotations I will need counts to add up over time here too (not just insert). To do this at the SQL level I will be using some very clever queries and moving the final IP and Count data into the table `failed_logins`.`fail2notify` using current table as a staging platform for the inserts. Once the new data is ready the following SQL commands will process it into its permanent home:

After processing the first round of data, I have moved 289 ips into `failed_logins`.`fail2notify` and emptied the staging platform table `failed_logins`.`ip`. Notice now that I have included a timestamp and an ID necessary for some programming later:

If I run this again, it will process 289 again, seeing them as new data, and incrementing the counts again as I would expect if I am running a new log file. This time I run SQL all as one and verify counts are doubled:

The fourth part starts with evaluating what we need to do for programming. I need to automate lookup of each IP Addresss in order to find the IPs registered abuse address. This information will be used to formulate the automated message containing the IP, number of Attempts, and timestamp. I will need to be able to record that I have already notified the ISP this week so that will require another table`failed_logins`.`notifications`.

To send an abuse notification I need to do a whois on the ip and get the abuse email addresses. This is going to require some programming knowledge and the programming learning what whois server we need to use for different international IPs. For USA addresses “whois -h whois.arin.net 61.177.172.46” works. However for our # 1 example: we need this command to see all the emails:

Fifth and final for the day on this topic, I need a website. I decided to go with fail2notify.com as the name. If you research this failed login topic a popular solution is fail2ban. This is a great solution to ban an IP after a certain # of logins and I will probably use it. However, my idea is that once the IP is in the fail2notify trap, they will not be able to access any system running fail2notify. Ever.

My quick goal for the website is an easy one page application that looks like old school green screen program. I start with a bootstrap core, and quickly add some basic black and green css adjustments. Once I have a framework looking nice I want to bring in the data to display in a table. Seems like a pretty good time for DataTables. This will allow me to load the data externally from the user interface, include search, and pagination. I quickly get datatables working with some sample json data (data.txt) and now I have a website:

The next part is getting prepared for programming automation. I need a program to create a real json data.txt file that contains the full 289 results in the database table. At this time the application has zero programming, just the index, datatables, and the sample data.txt I made. To end the day I use phpmyadmin to create some json and quickly start wasting time trying to get that formatting correct for datatables. Once I had that sorted and some adjustments to the jquery: Viola!!

Day 2: 04/01/2017

Today I want to work on a few things; processing all rotated logs for March , create data.txt to show live data in datatables, and work on the process design for the notification to the ISP.

I can easily import data for all the existing logs by running my commands on each of existing log files. I empty the databases then created the ips.data files one at a time. Next I import and process each log to get all of the Ips and counts into `failed_logins`.`fail2notify`. I had just one minor adjustment to make in the SQL commands:

This was causing any IPs spanning more than one file to get a count of 0 on the second execution. With this completed I now have 1451 total IPs ranging in counts from 1 failed login to 40,540 failed logins. This data only covers the end of Feb and first 3 weeks of march. The last week of march will not process until it is rotated (4/3/17). After that rotation happens I will test the automation again and prepare to run automatically on all future rotations (4/7/17 and on).

Next I want to focus on getting this data available on the website. Last nite I left the site connected to a manually created data.txt file. Now I want that data to be delivered from a live call into the database for results. I was planning to use ruby on rails for this task, at the API as the application level, but that is another server setup that is hours away. I even further researched capabilities of going from mysql to json, tried a few ways, even some advanced queries, but was not able to wrap object array data directly like I needed into a local file without a programming language.

I need to move faster so I quickly create local php script to get sql, save json to data.txt. This only needs to be done when automation finishes, again one time per week. It’s almost funny how much time was wasted to test/research other ways when this is as simple as 9 lines of php:

I then adjusted index.html to use data.jsn. While I am in here I also adjust the sorting desc on counts as datatables seems to ignore the data order in the query above. Now I have the website showing all 1451 results with the highest count on top:

These are some major login attempts that are easily seen within the interface vs never seen in some log file.

In order to view sample whois lookups I am going to make some processes that will get the IPs geo-data, and link to an ip whois report viewable in the screen. Being able to see what that data looks like will help me in programming the notification system but also make content for the website too. Providing a link to the IP geo-data, and a link to the whois report, will create the website with number ips times 2 pages of content.

I found an API for ip data in json format:

http://ip-api.com/json/223.99.60.46

It should be possible during the process of my data to fetch the output of above and store that as a json data type in SQL. Now lets find a whois API that has json output:

http://adam.kahtava.com/services/whois.json?query=223.99.60.46

I will need to append the output of each of these into ips.data file and then modify `failed_logins`.`ips` and `failed_logins`.`fail2notify` to include 2 new json fields. Then this data will flow through to the application. Including this all the way out to data.jsn in the application will make the file loading into the user interface very large. This poses some serious points:

When will it be too large to load in a reasonable time?

Why not just fetch the data from the source links above directly in the U/I?

What happens when the stored information changes, etc?

For the website purposes I will keep that data light with the current 3 fields, and only make the deeper data available from buttons in the U/I upon request. I do not need these to be realtime lookups. It will be okay to store just a snapshot of this data at time the ip notifies out. I am not trying to maintain that information historically just show what was captured while having text/content in the website. The only functional purpose of my lookups is to fetch the abuse email address.

Day 3: 04/03/2017

Today I start out with combining some of my working commands into a single line to process the latest log rotation, process that data to SQL, and make a new public data.jsn file. This is most of the automation to run about 4am sunday morning:

after executing this command I now have a total of 1807 failed logins:

Next I want to roll through all of these ips and store the output of the first API Call (ip_data):

http://ip-api.com/json/116.31.116.5

This was a bigger battle at the server level to get mysql working with the json data type. There was also quite a few issues with phpmyadmin and the json data type. This turned into issues with mysql data formats along the way to upgrading to php 56 and current version phpmyadmin. Once I got everything sorted my script is now running all the ip_data jsn into mysql.

Later I will add the country to the datatables and begin to build a method to view this full json objects inside of the application:

Next I am going to add the ip_whois and store that information into mysql. I already noticed that sample above does not have the right abuse email for chinese addresses. I am going to run them all through and see how many are China and then try to find a China whois api for the second API Call (ip_whois):

http://adam.kahtava.com/services/whois.json?query=116.31.116.5

This process for whois data was easy to create using the first one as an example. Later for automation I will likely combine them. For now they are running separately and processing data in the background. Both are taking a good deal of time to complete. I am adding some 2 second sleep calls in each loop so that this script is not hitting the source urls too many times per second.

With the scripts running I switch over to the application and start adding Country to the table view. I need to get that value from the json data type and I can do that with some very kewl new SQL:

The scripts are not even done running yet, but this is still a valid evaluation. Process automation often begins with removing as many “non-needles” from the haystack as possible, then evaluating the haystack again for needles. Find another batch of non-needles, process them out of the haystack, rinse repeat. When complete there should be a very small sets of results left (hay) that will allow me to provide a more finite solution (find the needle) without other results getting in the way.

Managing this list initially will help route emails that are correct (green) versus ones that need deeper lookup in red. I dont want to spam the high level whois services email addresses with my complaints. For example a chinese address is giving an email of:

It looks like these lower whois systems (africa, latin america) might not have any unique ip owners. If there is no contact at that deeper level, then I do want to use the upper level abuse@ address. Maybe spamming them will forward the email or they might respond back with better abuse contact. For China I am going to try and find a url to get the whois json. I know most of our IPs are going to be from China, so this whois server is as important as arin.net.

I found his url from searching an IP on the apnic website. It gives json and a match for abuse-mailbox:

http://wq.apnic.net/whois-search/query?searchtext=116.31.116.5

Shifting gears towards the end of processing the first servers data, I ran the main script on a different server as a test and I only had to make an adjustment for the mysql host and paths of the created files. I was very easily able to push the total # of rows over 3,000. To make things work better for multiple systems, I added a column reported_by to represent the IP (from the hostname) of the server which provided the counts. Now I need to adjust the command to use the servers hostname in the ips_data file:

With this done I make another minor adjustment to the automate.sql to use the new column and any new data processing should pass the reported_by IP. This does pose one potential issue; at some point 2 servers may report the same IP. I do not want to maintain separate counts per server. For now, I will use whatever Server IP has last touched the count (incremented). This will make this metric more like “Last Reported By IP” as well the timestamp when it was last reported.

Last but not least for the day I created .htaccess, and a application page to handle url requests in the format /ip/[IP ADDRESS]/ and /whois/[IP ADDRESS]/

Tomorrow I will make these a lil more pretty since at end of the day they are just variable dumps.

Day 4: 04/04/2017

Today will be a light sitting for me on this topic. I am just going to work on the /ip/ and /whois/ pages more than var dumps.

and for the whois page from the View Whois button above:

I need to spend some time looking at all of the data before I do too much design work with samples. The first page /ip/ showing all of ip_data should work for any output. The second page /whois/ showing all of ip_whois will be quite a bit different. For now I will leave the output raw in the scrollable div.

This gives me tools I need to focus on actually delivering some of the abuse notifications by being able to lookup both the ip data and whois data very easily. I have a lot of research into the process design but I have not made any scripts to parse the abuse emails, send any messages, or store data into `failed_logins`.`notifications`.

The last thing I do today is create a view link to the new /ip/ page on the right side of the screen:

Day 5: 04/17/2017

Last week I was in Costa Rica so did not make any moves here. Today I am back at it and I need to run the logs from yesterday and last week. When I login to the server I am welcomed by:

At this point I am pretty confident in my commands. Today they executed without any issues on both platforms. At the end I now have 4,221 ips at the application level:

The next step is to process the ip json data with the getIpData and getIpWhois scripts. Since the 2nd script will use the country from the first script the first one is required to run before the 2nd one. These scripts properly run on their own now so I set the first one to run in screen and focus on some other tasks while it finishes.

First I want to add an Extreme Tracker to the html source code. I have no stats and no visibility if anyone other than myself is accessing the website. I have used Extreme Tracker on many sites so this is a quick task. In a few days I will check back and see if anything interesting is happening on the tracker page.

Second I want to start the programming for sending the notifications back to the IP owner. I am going to write the program initially to run against all IPs who have country = USA. I know that these are getting correct abuse emails in the IP whois data. I am able to very quickly get a script setup (sendNotifications) to send a test message to myself for the first sample IP. However, before I can start sending to real abuse emails I need to record data that the message is sent. I also need to be able to use this data to NOT send the complaint over and over again. Going further, when a new complaint comes in after the sent date, then we would want to be able to resend the complaint again.

After adjusting the scripts main query with a left join to check for an existing notification, or an older count notification I add the insert into notification table and test a few loops sending to myself. As I complete a notification, it is properly excluded from the next execution. I then empty the notification table and prepare my script to run and send emails to the real abuse address. I had 2 issues

Notification Bounces

said: 554 Sending address not accepted due to spam filter (in reply to MAIL FROM command)

Once I had sent a few real messages I monitored my local mail box for bounce messages. I am going to need to do some work to the IP to make sure it can deliver messages without bouncing back.

I did a delisting at Barracuda, Sorbs, and INPS. It appears sometime back in 2015 the ip was used to send Spam.

I set rDNS at the host for my IP and hostname

No Email Address

sendmail: fatal: root(0): No recipient addresses found in message header

I noticed while sending messages there are some rows of data with the ip_whois field = “null”. For some reason these are not being ignored by the IS NOT NULL in the original pickup query for sendNotifications. It is also not possible to query these with an sql statement ” where ip_whois = ‘null’ “.

I will pick back up again once I get further traction in delisting and I am ready to send messages again.

Day 6: 04/18/2017

This morning I start out by doing some work to fix data issues. I have some reported_by still NULL (automate.sql not updated on remote server to pass new field) and I have some ip_whois field value equal to “null” (issue with original database structure default). I manually fixed all of these and ran getIpWhois process again. When I am done I have about 50 IPs without any ip_whois data, and 11 Ips without any ip_data data. It appears that the lookup url does not respond with any data:

With the data ready, I start sendNotifications for Unite States again and monitor the local mailbox for bounces. Out of 93 sent notifications there was less than 10 bounce issues. Some non existing emails and still some spam listing issues:

These are other countries than China reporting the same whois server. After reviewing the data I adjust sendNotification to run if the email is not (search-apnic-not-arin@apnic.net, whois-contact@lacnic.net, and abuse@ripe.net). I will handle these later. After 114 notifications I have just 3 bounces. This puts the total notifications up to 289. That leaves a giant number (3,000-4,000) in the realm of apnic, lacnic, and ripe.

756 Apnic (other than china)
802 Lacnic
837 Ripe

Next I adjust sendNotification for China by adding the following code to parse the json, find the china whois abuse-mailbox:

Day 7: 04/19/2017

I start today off with moving the fail2notify.com from its current IP (spam listed from Previous Owner in 2015) to a clean IP Address on its own cloud server. The cloud host I am using, hostkey.com has had another IP of mine offline for over 7 days with very spotty support regarding the issue. I will likely be moving all my assets off hostkey.com as a result. I create an Amsterdam based digital ocean node with centos7 and get php56, and mysql57 ready with just these commands:

This now gives me 3 datacenters around the world to report auto logins. I am certain by sunday this new IP will have quite a few failed logins as well.

Now that I do not have to do deal with bouncing my notifications, I want to focus on the results of sending the notifications so far. Yesterday a total of 1526 total notifications were sent. Out of that about 10% actually bounced so I see that as pretty good. What I also noticed was that some emails, get A LOT of notifications from different ips. If fail2notify was to send an email for every IP, they may see this as SPAMMING. Fail2notify will need to deliver a single message with all their IPs and each IPs counts, and timestamps.

Picking up from yesterday with ApNic, LatNic, and Ripe I need to adjust getIpWhois similarly to how I have with Country = China. For Ripe all the countries are:

The only update I need to do in my getIpWhois script is creating the country settings for ripe, and apnic, then comparing those during processing to get the correct whois url and json data into mysql. In validating all the data below, it appears that some IPs with countries (Us, Russia, France, Netherlands) have results across multiple whois. They are red below and need to be investigated.

I then run my processes getIpData and getIpWhois. After processing: 762 new ips.

At this point I believe the main commands should change to execute for both invalid user and failed login. This will likely increase the counts and # of ips greatly per log cycle. At start of this task the focus was user logins failing but any abusive ip action can be a count for reporting. With the “Invalid user” an ip that is trying many different users would never actually fail to login (invalid password). These are likely very low level login bots creating more requests on public ip addresses than root or existing user login attempts.

Day 9: 04/25/2017

Today I want to spend some time back on the sendNotification process. This main process needs to run for all distinct emails where the IP qualifies to notify. Then doing the process execution the notification summarizes all IP/Counts in a single message. To do this I create a distinct array of emails during the query to build ip data array. Once I have both arrays, I roll through the email array, then for each email I check any matching IPs. During the process loop for a distinct emails, the message is built, sent, then all ips are rolled through again inserting each IP into `failed_logins`.`notifications`.

After execution:

133 notifications sent (today)

Total Ips: 4,983

Total Sent Notifications: 2,772

Looking at the delivery counts, some of the abuse emails had 50+ ips in the message body. This time around sending notifications went very well compared to the last tests. The new IP is not spam listed already so thats a better start but not delivery 50+ messages at same time is a bigger improvement.

At this point in the fail2notify project I have a pretty constant system to generate data, process data, show data in the application, and send notifications. The next parts of this project will be making the shell commands run automatically, creating a more public method to send/generate data (API) from sources without mysql credentials, using the current data to start blocking IP ranges, and working on how to bundle it all for distribution.

Viola, I then quickly login to the first server(over 10,000 failed attempts since last login), fetch a copy of block list, create shell script and execute. Tomorrow I would expect the number of failed login attempts to drop significantly.

Day 10: 05/01/2017

Today is a log processing day so the first thing I do is login to the 3 servers. On server #1: I can finally see some light at the end of the tunnel, only 2500 failed logins since 4/27/2017:

On the 3rd server I again ran into some issues with the command. The issue turned out to be hostname -I (returns all ip addresses – 2 in this case) and had to use hostname -i (one ip address) . After running in the new automate data, I run makeData, getIpData (780 total), getIpWhois(829 total), sendNotifications(60 notifications sent for a total of 1065 ips) processes.