Getting Started

Step One

Ensure you have generated an sqlite.db using Fleetfolio Pentest or that you already own an sqlite.db.
Write SQL views and queries in .sql files and supporting queries in a Markdown file.
Ingest these SQL views and queries into your sqlite.db and view the Web UI using Surveilr.

First Project

Create a directory for your project:

mkdir project-tem

Copy or place your sqlite.db file into this directory (for example: tem.sqlite.db).
Create a sqlpage.json file inside a sqlpage folder:

mkdir sqlpage && cd sqlpage

cat <<EOF > sqlpage.json
{
  "database_url": "sqlite://tem.sqlite.db?mode=rwc",
  "allow_exec": true,
  "port": 9227
}
EOF

Write your SQL views in a file with a .sql extension (for example: stateless.sql).
Write SQL queries that call these views inside a Markdown file (for example: Spryfile.md).

Project Structure

Your directory should look like this:

project-tem/
├─ Spryfile.md
├─ stateless.sql
├─ tem.sqlite.db
├─ sqlpage/
   └─ sqlpage.json

Manual Steps

Ingest SQL Views from stateless.sql into sqlite.db

#!/usr/bin/env bash
cat stateless.sql | sqlite3 tem.sqlite.db

Ingest SQL Query from Spryfile.md into sqlite.db

#!/usr/bin/env bash
spry sp spc --md Spryfile.md  --package --conf sqlpage/sqlpage.json | sqlite3 tem.sqlite.db

View the sqlite.db Locally Using Surveilr

#!/usr/bin/env bash
surveilr web-ui -d tem.sqlite.db

Access the Web UI

Use the following command to start the local Web UI and open it in your browser:

#!/usr/bin/env bash
echo "Visit http://localhost:9227"

Automated Step

Copy paste the manual commands inside Spryfile.md as an executable block
Then verify by running these are executables by:

spry rb ls Spryfile.md

if you see the tasks listed, you are good to go.
Finally run the entire Spryfile by:

spry rb run Spryfile.md --verbose rich

Run this from the root of your project directory.

Then visit localhost

How is this guide?

Quick Start