Create and populate a "locations" table with the IP's location informations of a list generated by Cowrie.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

93 lines
3.3 KiB

import mysql.connector, requests, sys, time
import geoip2.database
from netaddr import *
from datetime import datetime
from argparse import ArgumentParser
parser = ArgumentParser()
parser.add_argument("-a", dest="host", help="mysql host", default="localhost")
parser.add_argument("-u", dest="user", help="mysql user", default="cowrie")
parser.add_argument("-p", dest="port", help="mysql port", default="3306")
parser.add_argument("-d", dest="database", help="mysql database", default="cowrie")
parser.add_argument("-P", dest="password", help="mysql password", default="cowrie")
parser.add_argument("-b", dest="backend", help="geoip backend (geoip or ip-api)", default="geoip")
parser.add_argument("-g", dest="geoipath", help="geoip database path", default="/var/lib/GeoIP")
parser.add_argument("-r", dest="regen", help="drop and recreate locations table", action='store_true')
parser.add_argument("-i", dest="inter", help="ask for confirmation before commit", action='store_true')
args = parser.parse_args()
mydb = mysql.connector.connect(
host =,
port = args.port,
user = args.user,
password = args.password,
database = args.database
mycursor = mydb.cursor()
if args.regen:
mycursor.execute("DROP TABLE locations")
mycursor.execute("SHOW TABLES LIKE 'locations'")
if "locations" in mycursor or args.regen:
mycursor.execute("CREATE TABLE locations (`ip` VARCHAR(15) NOT NULL, `lat` FLOAT(8) NOT NULL, `lon` FLOAT(8) NOT NULL, `isp` VARCHAR(255), `asn` INT(2), `country` VARCHAR(56), `cc` VARCHAR(2), `city` VARCHAR(56), `timestamp` timestamp NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4")
mycursor.execute("SELECT * FROM locations ORDER BY timestamp")
i = len(mycursor.fetchall())
mycursor.execute("SELECT * FROM sessions ORDER BY starttime")
sessions = mycursor.fetchall()
sessions_len = len(sessions)
city_reader = geoip2.database.Reader(args.geoipath + '/GeoLite2-City.mmdb')
asn_reader = geoip2.database.Reader(args.geoipath + '/GeoLite2-ASN.mmdb')
while i < sessions_len:
if IPAddress(sessions[i][4]).is_private():
print(f"{sessions[i][4]} is a private IP")
i += 1
city_res =[i][4])
asn_res = asn_reader.asn(sessions[i][4])
print(f"No data about {sessions[i][4]}")
i += 1
lat = city_res.location.latitude
lon = city_res.location.longitude
country =
cc =
city =
isp = asn_res.autonomous_system_organization
asn = asn_res.autonomous_system_number
print(f"No data about {sessions[i][4]}")
i += 1
print(cc, sessions[i][4])
timestamp = sessions[i][1]
print (sessions[i][1])
myinsert = "INSERT INTO `locations` (`ip`, `lat`, `lon`,`isp`, `asn`, `country`, `cc`, `city`, `timestamp`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
myvalues = (sessions[i][4], lat, lon, isp, asn, country, cc, city, timestamp)
mycursor.execute(myinsert, myvalues)
i += 1
if args.inter:
reply = str(input('Commit changes to database ? (y/n): '))
if reply[0] == 'y':
print(mycursor.rowcount, "record inserted.\n")