Dec 13 2011

The big post of Kippo scripts, front-ends, bash one-liners and SQL queries

Continuing on the previous posts about Kippo, and assuming you have already setup, configured it and logged some probes or intrusions, let’s take a look at some of the scripts, front-ends, commands, and other useful 3rd party stuff available in our disposal to get a better understading of what’s going on with our honeypots.

1. First of all, Andrew wrote a quite useful script to provide a daily review of activity on the honeypot. Essentially this does two things, lists session interaction and files downloaded within the last 24hours. You can modify the time interval to more days as well. The cool thing is that you can create a cron job with it and you’ll get a daily email with any “human” activity in your system.

The script can be downloaded here. For backup purposes here it is as code:


# Author:    Andrew Waite / www.infosanity.co.uk
# Date:        2011-05-20
# Version:    0.2
# License:    Beerware - http://www.infosanity.co.uk/resources/beerware-license.txt


SESSION_ID="0"     # I've never seen a Kippo logfile with multiple IDs, if I'm wrong, let me know


#Number of days worth of sessions to list.

ECHO="/bin/echo -e"

# TTY sessions in last $DAYS
# () make an array rather than string
RECENT_TTY=(`/usr/bin/find $LOG_DIR/tty -ctime -$DAYS`)

$ECHO "***Sessions***nn" >> $REPORT_FILE

for TTY in "${RECENT_TTY[@]}"

# set -m to 0 to reduce run time

$ECHO "n---END:$TTY---nn" >> $REPORT_FILE


# new downloads in last $DAYS
RECENT_DL=(`/usr/bin/find $DL_DIR/ -ctime -$DAYS`)

for DL in "${RECENT_DL[@]}"

/bin/cat $REPORT_FILE

## Crontab entry for daily exection
## m h dom mon dow   command
#0 6 * * * /opt/kippo-svn/kippo-sessions.sh | mail -s "Daily Kippo" some@email-address.com

You will have to enter the corrent paths per your Kippo installation (if you followed my guide these would be /home/kippo/kippo/…).

2. Next, a web application to display stats about your honeypot.

I have not tried to set it up yet, it’s called kippo-stats and you can find it here.

At present, it seems to be the default script that people use to visualize data stored by Kippo.

3. If you run your honeypot on a let’s say low-end openvz VPS with low ram and you can’t afford a mysql server to log the probes and intrustions, you will find the following bash commands extremely helpful when dealing with the mess of raw text-based Kippo logs. They come from the South African Chapter of the HoneyNet Project. Read the post by Matt here. For backup purposes here they are:

First of all, I pulled all my logs into a master log file “masterlog”.

Pull hosts from the log file:
This will check the master log file for all the unique IP connections connecting to the server before they started their brute force attacks. I’ll use the ipcons.txt file in the next scripts.

grep -e “New connection” masterlog.log| awk -F” ” ‘{ print $6 }’ | awk -F”:” ‘{ print $1 }’  | sort | uniq >> ipcons.txt

Pull username attempts per host:
This will pull out a list of all the usernames used in attacks against the server.

for x in `cat ipcons.txt ` ; do echo $x >> users.txt ; grep -e “$x” masterlog.log| grep -e “login attempt” | awk -F”[" '{ print $3 }' | awk -F"/" '{ print $1 }' | sort | uniq >> users.txt ; echo " " >> users.txt ; done

Pull passwords per host:
This will pull out a list of all the passwords used against the server for each connection.

for x in `cat ipcons.txt ` ; do echo $x >> passwords.txt ; grep -e "$x" masterlog.log | grep -e "login attempt" | awk -F"[" '{ print $3 }' | awk -F"/" '{ print $2 }' | awk -F"]” ‘{ print $1 }’ | sort | uniq >> passwords.txt ; echo ” ” >> passwords.txt ; done

4. On the contrary, if you have setup MySQL logging as per the instructions, then have in mind the following useful SQL queries, again taken from the blog of Andrew, here and here. For backup purposes, here they are:

Top 10 most common passwords attempted:

select count(password), password
from auth
where password <> ''
group by password
order by count(password) desc
limit 10;

Top 10 most common username attempted:

select count(username), username
from auth
where username <> ''
group by username
order by count(username)
desc limit 10;

Success ratio:

select count(success),success
from auth
group by success
order by success;

Number of connections per unique IP:

select count(ip), ip
from sessions
group by ip
order by count(ip) desc;

Source IPs for same user (based on pass):

SELECT sessions.id AS Session, sessions.ip AS Source, auth.password AS Password, auth.timestamp AS Time
FROM sessions, auth
WHERE sessions.id = auth.session AND auth.success = 1 AND auth.password = 'mariusbogdan';

Successful logins from same source:

SELECT COUNT(sessions.ip) AS Num, sessions.ip AS Source
FROM sessions, auth
WHERE auth.success = 1 AND auth.session = sessions.id
GROUP BY sessions.ip
ORDER BY COUNT(sessions.ip) desc

There are more interesting queries of course, and you can come up with your own. Some additional ones are included in the Kippo-Graph package (see below).

5. Dave has written a post on setting up Ajaxterm for fancy diplaying of Kippo logs within a web browser. See an example of what it looks like by clicking here. For backup purposes here it is:

Configuring Ajaxterm-kippo:

Downloaded my modified code here ajaxterm-kippo.zip

(local mirror: ajaxterm-kippo)

When you download the code make sure to modify ajaxterm.py and change the first lines that will look like this:

PLAYLOG_UTIL = '/home/honeypot/kippo-0.5/utils/playlog.py'
PLAYLOG_TTY = '/home/honeypot/kippo-0.5/log/tty/'

(if you followed my guide the above paths should be /home/kippo/kippo/…)

Create an unprivileged user:

Ajaxterm is a python script that is a webserver in itself, so since it will be accepting outside connections on a specific port it would be good practice to create an unprivileged user to run ajaxterm. This part is optional… but highly recommended.

sudo useradd -s /bin/false honeypot
sudo mkdir /home/honeypot
sudo chown honeypot /home/honeypot

These commands will create a limited user account named ‘honeypot’ that will have no login shell by default, and no password in the shadow file (so you can’t login as this user). They will also create a home directory for the user, and this is where you can store the ajaxterm files.

Making it a service:

I created a small init.d script that can be used to start ajaxterm as the unprivileged user, and also check on the status (if it is running or not). Just copy the following script to /etc/init.d/ajaxterm and then you will be able to start and stop it just like any service.

dave@[daveeddy]:/home/honeypot/ajaxterm/$ sudo service ajaxterm start
 * Starting Ajax terminal webserver Ajaxterm [ OK ]
dave@[daveeddy]:/home/honeypot/ajaxterm/$ sudo service ajaxterm status
Ajaxterm :: service is running -- pid 17028

Here is the script:

# init script for ajaxterm
# no logging supported

DESC="Ajax terminal webserver"
USER="honeypot" # the unprivileged user to run as, if unsure use 'nobody'


[ -x "$DAEMON" ] || exit 0

# Load the VERBOSE setting and other rcS variables
. /lib/init/vars.sh

# Define LSB log_* functions.
# Depend on lsb-base (>= 3.0-6) to ensure that this file is present.
. /lib/lsb/init-functions

case "$1" in
        log_daemon_msg "Starting $DESC" "$NAME"
        [ -f "$PIDFILE" ] || $DAEMON --port=$PORT --daemon --pidfile=$PIDFILE --uid=$USER
        log_end_msg $?
        log_daemon_msg "Stopping $DESC" "$NAME"
        [ -f "$PIDFILE" ] && kill "`cat $PIDFILE`"
        rm -f "$PIDFILE"
        log_end_msg $msg
        [ -f "$PIDFILE" ] && echo "$NAME :: service is running -- pid `cat $PIDFILE`" || echo "$NAME :: service is NOT running -- no pid file found"
        exit 0
        $0 stop
        sleep 1
        $0 start
        echo "Usage: $0 {start|stop|status|restart|force-reload}" >&2
        exit 3


UPDATE 22/12/11:

6. I have heard in the past about Tomasz Miklas‘ kippo-stats script (not to be confused with kippo-stats web app above) but it was nowhere to be found. Even the attachment link in Kippo’s Google Group was invalid.

I finally found it inside a debian package that mig5 created for easy installation of Kippo on Debian/Ubuntu (see next number on the list). Here it is:

# Generate simple kippo instance stats
# Original Author: Tomasz Miklas
# Modified by Miguel jacq  for Debian package
# GPLv2
use strict;
use warnings;

# Paths to various kippo components
# Data directory
my $kippodatadir = '/var/lib/kippo/';

# Config directory
my $kippoconfdir = '/etc/kippo/';

# Log directory
my $kippologdir = '/var/log/kippo';

my $date = $ARGV[0] || 'Lifetime';

my (%sources, %usernames, %passwords, %sshversions, %userpasscombo);
my ($left,$right,$cnt,$connections);
my $sensorid = `md5sum $kippoconfdir/kippo.cfg | cut -d " " -f 1`;

open (IN, "cat $kippologdir/kippo* |") || die "Can't open log stream: $!n";
while () {
  next if $date ne 'Lifetime' and !/^$date/;
  next if !/(login attempt|New connection:|Remote SSH version:)/;
  # New connection: xx.xx.xx.xx:
  # Remote SSH version: SSH-2.0-libssh-0.1
  # login attempt [nurmi/nurmi] failed
  if (/New connection: (.*?):/) { $sources{$1}++; $connections++ };
  if (/Remote SSH version:s+(.*?)$/) { $sshversions{$1}++ };
  if (/login attempt [(.*?)/(.*?)]/) { $usernames{$1}++; $passwords{$2}++; $userpasscombo{"$1 / $2"}++ };
close (IN);

format STDOUT =
@< @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<< $cnt, $left,$right . print "$date stats for kippo instancenInstance $sensoridnUnique values ($connections connections):n - usernamest" , scalar keys %usernames , "n - passwordst" , scalar keys %passwords , "n - sourcest" , scalar keys %sources , "nnn"; print "# SSH client versions Countn"; print "--------------------------------------------------------------n"; $cnt=1; foreach my $version (sort {$sshversions{$b}  $sshversions{$a}} keys %sshversions) {   $left = $version;   $right = $sshversions{$version};   write;   $cnt++; } print "nn"; print "# Top 10 usernames Countn"; print "--------------------------------------------------------------n"; $cnt = 1; foreach my $username (sort {$usernames{$b}  $usernames{$a}} keys %usernames) {   last if $cnt > 10;
  $left = $username;
  $right = $usernames{$username};
print "nn";

print "# Top 10 passwords Countn";
print "--------------------------------------------------------------n";
$cnt = 1;
foreach my $password (sort {$passwords{$b}  $passwords{$a}} keys %passwords) {
  last if $cnt > 10;
  $left = $password;
  $right = $passwords{$password};
print "nn";

print "# Top 10 'user / pass' combos Countn";
print "--------------------------------------------------------------n";
$cnt = 1;
foreach my $combo (sort {$userpasscombo{$b}  $userpasscombo{$a}} keys %userpasscombo) {
  last if $cnt > 10;
  $left = $combo;
  $right = $userpasscombo{$combo};
print "nn";

print "# Top 10 offenders Countn";
print "--------------------------------------------------------------n";
foreach my $src (sort { $sources{$b}  $sources{$a} } keys %sources) {
  last if $cnt > 10;
  $left = $src;
  $right = $sources{$src};
print "nn";

print "# Current Logs in log/tty Folder ";
my $fileCnt = 0;
open (lineCounts, "ls $kippologdir/tty/ |") || die "Can't open log dir: $!n";
while (){$fileCnt++;}
print " $fileCnt Filesn";
close (lineCounts);
print "--------------------------------------------------------------n";

7. As stated above, mig5 created a Kippo deb package on his own. You can found the kippo-deb package on his website here, and the public git repository on github here.

8. Don’t forget my own project, Kippo-Graph. A full featured tool with a web interface to visualize and display statistics, geolocation information and generated graphs from Kippo MySQL data.

UPDATE 31/12/11:

9. Finally, another project of mine, Kippo2MySQL. A simple script based on kippo-stats perl above that reads data from Kippo’s text-based log files and inserts them in a MySQL database.

UPDATE 11/3/12:

10. Another visualization project written in Ruby by Jay Scott. Github link: https://github.com/jayscott/honeypot-source and here is a backup archive: jayscott-honeypot-source-64c056f

That’s the end of this post. Hopefully someone will find it useful in the future by having all of the Kippo add-ons joined together. If you have any other suggestions leave a comment.

In the future, all of the above would be categorized into individual pages under a parent Kippo-Scripts/Tools page for better browsing.

  • http://SethLeedy.Name Seth Leedy

    I sure love Kippo now. Nice collection of scripts here.
    Feel free to add mine to the collection.

    Here is some code I made up for Kippo usage. It allows you to record the IP,USERNAME,PASSWORD captured by Kippo and then test if those same auths can be used on the originating IP. If it does login, then records the Valid auths for later usage.


  • http://www.thehackerwiki.com/ ptrac3

    What a useful post! : ) I love Kippo and your SQL query were so useful to analyze kippo logs..I’m tryin’ to find a MySQL query to display the latest attacks with ip and a counter to see how many time they are tryin’ to get into kippo. Thanks for any suggestion : )

  • bahamas10

    In regards to the broken link above for daveeddy.com, i’ve updated the URL. The post is still available here. http://www.daveeddy.com/2011/03/27/ajaxterm-for-kippo-logs/

More in Honeypots, Programming
Logging Kippo events using MySQL DB
Installing Kippo SSH Honeypot on Ubuntu
Εγκατάσταση του Kippo SSH Honeypot (Ubuntu 11.04)
Φιλοξενία έργων λογισμικού και διαχείριση πηγαίου κώδικα
Script αναβάθμισης του BackTrack 5