Kippo SQL Reference

This post is a reference for SQL queries that you can use to derive information from Kippo – SSH Honeypot.

This post would be extremely helpful if you have been strugling with different databases for different kippo instances. For instance, in my setup I’d refer to three kippo databases k1, k2 and k3.

Top 10 IP addresses with Maximum Sessions

SELECT ip , count( * ) AS ‘count’
FROM (
SELECT k1.sessions.ip FROM k1.sessions
UNION ALL SELECT k2.sessions.ip FROM k2.sessions
UNION ALL SELECT k3.sessions.ip FROM k3.sessions
) AS topip
GROUP BY ip
ORDER BY `count` DESC
LIMIT 0 , 10

 

Top 10 Passwords with Highest Frequency

SELECT PASSWORD , count( * ) AS ‘count’
FROM (
SELECT k1.auth.password FROM k1.auth
UNION ALL SELECT k2.auth.password FROM k2.auth
UNION ALL SELECT k3.auth.password FROM k3.auth
) AS toppass
GROUP BY PASSWORD
ORDER BY `count` DESC
LIMIT 0 , 10

 

Top 10 Usernames with Highest Frequency

SELECT username , count( * ) AS ‘count’
FROM (
SELECT k1.auth.username FROM k1.auth
UNION ALL SELECT k2.auth.username FROM k2.auth
UNION ALL SELECT k3.auth.username FROM k3.auth
) AS topuser
GROUP BY username
ORDER BY `count` DESC
LIMIT 0 , 10

 

Top 10 Username and Password Combinations

SELECT username,password,count(*) AS ‘count’
FROM (
SELECT k1.auth.username, k1.auth.password FROM k1.auth
UNION ALL SELECT k2.auth.username, k2.auth.password FROM k2.auth
UNION ALL SELECT k3.auth.username, k3.auth.password FROM k3.auth
) AS topunp
GROUP BY USERNAME,PASSWORD
ORDER BY `count` DESC
LIMIT 0 , 10

 

All Distinct IP addresses

select distinct ip from (SELECT k1.sessions.ip
FROM k1.sessions
UNION ALL SELECT k2.sessions.ip
FROM k2.sessions
UNION ALL SELECT k3.sessions.ip
FROM k3.sessions) as beep

 

Top 10 Successful Logins

SELECT ip, count( * ) AS ‘count’
FROM (
SELECT k1.sessions . * , k1.auth.session, k1.auth.success FROM k1.sessions, k1.auth WHERE k1.sessions.id = k1.auth.session AND k1.auth.success =1
UNION ALL SELECT k2.sessions . * , k2.auth.session, k2.auth.success FROM k2.sessions, k2.auth WHERE k2.sessions.id = k2.auth.session AND k2.auth.success =1
UNION ALL SELECT k3.sessions . * , k3.auth.session, k3.auth.success FROM k3.sessions, k3.auth WHERE k3.sessions.id = k3.auth.session AND k3.auth.success =1
) AS topslog
GROUP BY ip
ORDER BY `count` DESC
LIMIT 0 , 10

 

Number of Attacks Per Day

SELECT date( starttime ) AS ‘date’, count( * ) AS ‘count’
FROM (
SELECT k1.sessions.starttime
FROM k1.sessions
UNION ALL SELECT k2.sessions.starttime
FROM k2.sessions
UNION ALL SELECT k3.sessions.starttime
FROM k3.sessions
) AS topday
GROUP BY date
ORDER BY `date` ASC

 

Numer of Attacks at specific hour

SELECT TIMEONLY, count( * ) AS ‘count’
FROM (
SELECT DATE_FORMAT( k1.sessions.starttime, ‘%H’ ) TIMEONLY FROM k1.sessions
UNION ALL SELECT DATE_FORMAT( k2.sessions.starttime, ‘%H’ ) TIMEONLY FROM k2.sessions
UNION ALL SELECT DATE_FORMAT( k3.sessions.starttime, ‘%H’ ) TIMEONLY FROM k3.sessions
) AS tophour
GROUP BY TIMEONLY
ORDER BY `count` DESC

 

Top 10 IPs at specific hour

SELECT ip,TIMEONLY,count(*) AS ‘count’
FROM (
SELECT DATE_FORMAT( k1.sessions.starttime, ‘%H’ ) TIMEONLY, k1.sessions.ip FROM k1.sessions
UNION ALL SELECT DATE_FORMAT( k2.sessions.starttime, ‘%H’ ) TIMEONLY, k2.sessions.ip FROM k2.sessions
UNION ALL SELECT DATE_FORMAT( k3.sessions.starttime, ‘%H’ ) TIMEONLY, k3.sessions.ip FROM k3.sessions
) AS beep where ip in (select ip from (
SELECT ip , count( * ) AS ‘count’
FROM (
SELECT k1.sessions.ip FROM k1.sessions
UNION ALL SELECT k2.sessions.ip FROM k2.sessions
UNION ALL SELECT k3.sessions.ip FROM k3.sessions
) AS beep
GROUP BY ip
ORDER BY `count` DESC
LIMIT 0 , 10   
    )as shan)
GROUP BY TIMEONLY,ip
ORDER BY `count` DESC
LIMIT 0 , 10

 

Number of Attacks in specific week

SELECT week( starttime ) AS ‘date’, count( * ) AS ‘count’
FROM (
SELECT k1.sessions.starttime
FROM k1.sessions
UNION ALL SELECT k2.sessions.starttime
FROM k2.sessions
UNION ALL SELECT k3.sessions.starttime
FROM k3.sessions
) AS beep
GROUP BY date
ORDER BY `date` ASC

 

IP Address with Highest Number of Sessions and Successful Logins

select * from(select ip,count(*) as ‘count’
from (
SELECT k1.sessions.*,k1.auth.session,k1.auth.success from k1.sessions,k1.auth where k1.sessions.id=k1.auth.session and k1.auth.success=1 UNION ALL SELECT k2.sessions.*,k2.auth.session,k2.auth.success from k2.sessions,k2.auth where k2.sessions.id=k2.auth.session and k2.auth.success=1
UNION ALL SELECT k3.sessions.*,k3.auth.session,k3.auth.success from k3.sessions,k3.auth where k3.sessions.id=k3.auth.session and k3.auth.success=1) as beep group by ip
ORDER BY `count`  DESC) as a,(SELECT ip , count( * ) AS ‘count2’
FROM (
SELECT k1.sessions.ip FROM k1.sessions
UNION ALL SELECT k2.sessions.ip FROM k2.sessions
UNION ALL SELECT k3.sessions.ip FROM k3.sessions
) AS beep
GROUP BY ip
ORDER BY `count2` DESC) as b where a.ip=b.ip
order by count desc,count2 desc

 

Average Duration for Top 10 IP

SELECT ip,avg(timediff( endtime, starttime )) as average
FROM (
SELECT *
FROM (
SELECT k1.sessions. * FROM k1.sessions
UNION ALL SELECT k2.sessions. * FROM k2.sessions
UNION ALL SELECT k3.sessions. * FROM k3.sessions
) AS beep
WHERE ip in (select ip from (SELECT ip , count( * ) AS ‘count’
FROM (
SELECT k1.sessions.ip FROM k1.sessions
UNION ALL SELECT k2.sessions.ip FROM k2.sessions
UNION ALL SELECT k3.sessions.ip FROM k3.sessions
) AS beep
GROUP BY ip
ORDER BY `count` DESC
LIMIT 0 , 10) as chaos)
) AS chaos2
group by ip

Leave a Reply

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