# Sending the Output of the Honeypot to an SQLite3 Database

- [Sending the Output of the Honeypot to an SQLite3 Database](#sending-the-output-of-the-honeypot-to-an-sqlite3-database)
  - [Prerequisites](#prerequisites)
  - [Installation](#installation)
  - [SQLite3 Database Creation](#sqlite3-database-creation)
  - [Honeypot Configuration](#honeypot-configuration)
  - [Restart the honeypot](#restart-the-honeypot)

## Prerequisites

- Working honeypot installation
- SQLite3 (Can be downloaded from the [official site](https://sqlite.org/download.html))

## Installation

When writing to an SQLite3 database, the honeypot uses the free databases
provided by MaxMind for the purposes of geoloacting the IP addresses. Start by
 downloading the database update program for your particular kind of Windows
from [GitHub](https://github.com/maxmind/geoipupdate/releases) and put it in a
directory listed in the `PATH` variable of the environment.

Create an account at the [MaxMind web
site](https://support.maxmind.com/knowledge-base/articles/create-a-maxmind-account),
log in, go to "My Account" and then to "Manage license keys". Write down the
account ID, generate a license key, and copy it.

Go to the directory `data`, where the gelolocation databases will reside:

```powershell
PS C:\> cd \adbhoneypot-workdir\data
```

Create in this directory a file named `geoip.cfg` with the following contents:

```geoip.cfg
AccountID <ACCOUNT>
LicenseKey <KEY>
EditionIDs GeoLite2-City GeoLite2-ASN
DatabaseDirectory C:\adbhoneypot-workdir\data
LockFile C:\adbhoneypot-workdir\data\.geoipupdate.lock
```

Change the paths in the options `DatabaseDirectory` and `LockFile` if you
have opted to use paths different from the ones suggested by the
honeypot installation documentation. Make sure you replace `<ACCOUNT>`
and `<KEY>` with the account and license key obtained from MaxMind.

Download the latest version of the Maxmind geolocation databases:

```powershell
PS C:\adbhoneypot-workdir\data> geoipupdate -f geoip.cfg
```

To have the database updated automatically (it is updated on MaxMind's site
every second Tuesday of each month, so download it every second Wednesday),
run the script `geoipupdtask.ps1` in the working directory:

```powershell
PS C:\adbhoneypot\data> ..\geoipupdtask.ps1 
```

It expects that the program `geoipupdate.exe` resides in one of the directories
listed in the `PATH` variable of the environment, that the configuration file
for it is named `geoip.cfg` and resides in the current directory, and that the
updating task is to be run at 00:00. Also, it creates an updating task named
`GeoIPUpdate`, which resides in the task folder `\` and has the description
`GeoIP database updater`. You can change any of these parameters via
command-line options to the script:

```powershell
PS C:\adbhoneypot\data> ..\geoipupdtask.ps1 -TaskName "My GeoIP Database Updater" -TaskPath "\MyTasks" -TaskDescription "Updates the GeoIP database" -RunTime "03:00:00" -geoipupdate "C:\Program File\geoipupdate\geoipupdate.exe" -geoipconfig "C:\adbhoneypot-workdir\data\geoip.cfg"
```

If you already have the MaxMind geolocation databases installed and updated on
your machine in some other place, use their respective paths in the
`[output_sqlite]` section of the file `honeypot.cfg`, as mentioned below.

Finally, return to the working directory:

```powershell
PS C:\adbhoneypot-workdir\data> cd ..
```

## SQLite3 Database Creation

First, create a database named `adbhoneypot.db`, residing in the `data` directory
and based on the schema `C:\adbhoneypot-workdir\docs\sqlite3\sqlite3.sql`:

```powershell
PS C:\adbhoneypot-workdir> sqlite3 C:\adbhoneypot-workdir\data\adbhoneypot.db < C:\adbhoneypot-workdir\docs\sqlite3\sqlite3.sql
```

If you have opted on keeping the database elsewhere, use its proper path
instead of `C:\adbhoneypot-workdir\data\adbhoneypot.db`.

## Honeypot Configuration

Add the following entries to the file `C:\adbhoneypot-workdir\etc\honeypot.cfg`

```honeypot.cfg
[output_sqlite]
enabled = true
debug = false
db_file = data/adbhoneypot.db
# Whether to store geolocation data in the database
geoip = true
# Location of the databases used for geolocation
geoip_citydb = data/GeoLite2-City.mmdb
geoip_asndb = data/GeoLite2-ASN.mmdb
```

Make sure the options `geoip_citydb` and `geoip_asndb` point to the correct
paths of the two MaxMind geolocation databases. Also, if you prefer to keep
the SQLite3 database elsewhere, make sure that you specify its correct path
with the `db_file` option.

## Restart the honeypot

```powershell
PS C:\adbhoneypot-workdir> C:\adbhoneypot-env\scripts\activate.ps1
(adbhoneypot-env) PS C:\adbhoneypot-workdir> adbhoneypot restart
```
