KvasirSecurity/Kvasir — pull request #39 by grutz (2013-10-10 15:06:03+00:00)
Title: Adds dedupe to private directory

Duplicates happen. They totally suck. This script will remove dupes but only for PostgreSQL (it's what we use, man!). Other DBs my be supported but are totally untested.

For example, to remove any t_host_os_refs duplicates leaving the lowest id active:

$ ./web2py.py -S kvasir -M -R
applications/kvasir/private/dedupe.py -A -f f_hosts_id -f f_os_id -f f_certainty -d t_host_os_refs
web2py Web Framework
Created by Massimo Di Pierro, Copyright 2007-2013
Version 2.7.2-stable+timestamp.2013.10.09.11.56.54
Database drivers available: SQLite(sqlite3), MySQL(pymysql),
PostgreSQL(psycopg2), PostgreSQL(pg8000), MongoDB(pymongo),
IMAP(imaplib)

Kvasir De-Dupe Table Entries
============================

[*] Database t_host_os_refs valid
[*] Validating field: f_hosts_id -- OK
[*] Validating field: f_os_id -- OK
[*] Validating field: f_certainty -- OK
[-] 4220 records currently in the database
[*] Dry run selected, would have executed:

DELETE FROM t_host_os_refs
WHERE id IN (SELECT id
FROM (SELECT id,
row_number() over (partition BY f_hosts_id, f_os_id, f_certainty ORDER BY id) AS rnum
FROM t_host_os_refs) t
WHERE t.rnum > 1);

LOG: could not receive data from client: Connection reset by peer