Skip to content

Switch from SQAlchemy to Influx

Martin Prudek requested to merge influx into master

Closes #5 (closed) and #11 (closed) as all the minipot types shares same influx measurements

Implements #12 (closed)

Should be ready to to deploy (as all MR must be) although I expect few issues yet to come. I will also surely rebase all the commits in more compact way - I left it this way now so you can follow my mind flow.

A also prepared sql-influx migration show below.

SQL migration

Passwords

Used for staticstics:

  • most common passwords
  • most common passwords trends
  • most common usernames
  • most common combinations
  • most common logins for a password

The following command must be applied for all minipot tables:

  • minipot_telnet
  • minipot_http
  • minipot_smtp
  • minipot_ftp
influx query 'import "sql"
sql.from(driverName: "postgres", dataSourceName: "postgresql://username:password@host:port/sentinel", query:"
    SELECT
      password,
      username,
      country,
      ts 
    FROM minipot_ftp
    WHERE 
      action = '\''login'\''
      AND
      password IS NOT NULL
      AND
      password <> '\'\''
      AND
      password <> '\'\n\''
")
  |> map(fn:(r) => ({ r with _time: time(v: r.ts*1000000000) }))
  |> set (key: "_measurement", value: "password_count")
  |> set (key: "_value", value: "1") 
  |> toInt()
  |> set(key: "source", value: "minipot_ftp")
  |> rename(columns: {password: "_field"}) 
  |> to(bucket: "sentinel-base",timeColumn: "_time", tagColumns: ["country", "username", "source"])'

All incidents

Used for statistics:

  • most evil countries
  • map overview
  • number of unique attackers trend
  • top active countries by number of unique attackers trend
  • most active attackers
  • activity graph of an attacker

The following command must be applied for all minipot tables:

  • minipot_telnet
  • minipot_http
  • minipot_smtp
  • minipot_ftp
influx query 'import "sql"
sql.from(driverName: "postgres", dataSourceName: "postgresql://username:password@host:port/sentinel", query:"
    SELECT
      ip,
      country,
      ts
    FROM minipot_telnet
")
  |> map(fn:(r) => ({ r with _time: time(v: r.ts*1000000000) }))
  |> set (key: "_measurement", value: "incident_count")
  |> set (key: "_value", value: "1")
  |> toInt()
  |> set(key: "_field", value: "minipot_telnet")
  |> rename(columns: {ip: "src_addr"})
  |> to(bucket: "sentinel-base",timeColumn: "_time", tagColumns: ["country", "src_addr"])'

Data must be extracted also from fwlogs!

Edited by Martin Prudek

Merge request reports