"Simple program to extract information from Dionaea's sqlite3 logfile". It is required to input the sqllog.sqlite while creating a new object with this class.
In order to save place on me blog i pasted the source code on pastie.org. This way i will keep this blog looking like an actual blog.
require 'sqlite3'
# Reads Dionaea sqlite3 log file
# Queries are stolen from http://carnivore.it/2009/11/06
module MyTools
class Dionaea
def initialize(log)
@log = log
end
# BAM!
def execute_query(sql_query)
db = SQLite3::Database.new(@log)
db.execute(sql_query)
end
# Sort by most downloaded files
def most_downloaded
query = %q{
SELECT
COUNT(download_md5_hash),
download_md5_hash,
download_url
FROM
downloads
GROUP BY
download_md5_hash
ORDER BY
COUNT(download_md5_hash)
DESC
}
execute_query(query)
end
# Sort by "most used download location"
def most_used_dl
query = %q{
SELECT
COUNT(*),
download_url
FROM
downloads
GROUP BY
download_url
ORDER BY
COUNT(*)
DESC;
}
execute_query(query)
end
# Sort by most aggressive attackers
def aggressive_attackers
query = %q{
SELECT
count(*),
download_md5_hash,
remote_host,
download_url
FROM
connections
NATURAL JOIN
downloads
GROUP BY
download_md5_hash,remote_host
ORDER BY
COUNT(*)
DESC
}
execute_query(query)
end
# Shows last 24 hours activity
def last_24hours
query = %q{
SELECT
ROUND((connection_timestamp%(3600*24))/3600) AS hour,
count(*)
FROM
connections
WHERE
connection_parent IS NULL
GROUP BY
ROUND((connection_timestamp%(3600*24))/3600);
}
execute_query(query)
end
# Sort by md5s retrieved
def get_md5s
query = %q{
SELECT
download_md5_hash,
download_url,
remote_host
FROM
connections
NATURAL JOIN
downloads
GROUP BY
download_md5_hash,remote_host
ORDER BY
download_url
DESC
}
md5s = []
execute_query(query).each do |result|
md5s << result.slice(0)
end
return md5s
end
# Sort by MD5, download url and attackers IP
def get_jiggy_with_it
query = %q{
SELECT
count(*),
download_md5_hash,
download_url,
remote_host
FROM
connections
NATURAL JOIN
downloads
GROUP BY
download_md5_hash,remote_host
ORDER BY
download_url
DESC
}
execute_query(query)
end
end
end
