Querying the Database

Use SQL queries to interrogate the ThreatPipes database.

threatpipes.db

All ThreatPipes data is stored in a SQLite database (threatpipes.db).

You can query the database directly to analyse your data.

$ cd THREATPIPES_HOME_DIR/db
$ sqlite3 threatpipes.db

The schema is quite simple and can be viewed in $THREATPIPES_HOME_DIR/bin/db.py

Example queries

Total number of scans in the ThreatPipes database

$ sqlite> select count(*) from tbl_scan_instance;

Obtain the scan instance ID for a particular scan

sqlite> select guid from tbl_scan_instance where seed_target = 'threatpipes.com';

Number of results per data type for scan instance ID

sqlite> select count(*), type from tbl_scan_results where scan_instance_id = '6adf80bb-ddbb-48f6-b3de-a5827f32af84' group by type;
5|AFFILIATE_INTERNET_NAME
2|AFFILIATE_IPADDR
1|CO_HOSTED_SITE
1|DOMAIN_NAME
1|DOMAIN_REGISTRAR
1|DOMAIN_WHOIS
1|GEOINFO
28|HTTP_CODE
48|HUMAN_NAME
49|INTERNET_NAME
2|IP_ADDRESS
49|LINKED_URL_EXTERNAL
144|LINKED_URL_INTERNAL
2|PROVIDER_DNS
1|PROVIDER_MAIL
4|RAW_DNS_RECORDS
1|RAW_FILE_META_DATA
1|ROOT
14|SEARCH_ENGINE_WEB_CONTENT
1|SOFTWARE_USED
16|TARGET_WEB_CONTENT
2|TCP_PORT_OPEN
1|TCP_PORT_OPEN_BANNER
1|URL_FORM
10|URL_JAVASCRIPT
6|URL_STATIC
21|URL_WEB_FRAMEWORK
28|WEBSERVER_BANNER
28|WEBSERVER_HTTPHEADERS