--------------------
-- TLS SIMULATORS --
--------------------

-- Find top spammers
SELECT count(remote_host) as count, remote_host  FROM tls_connection GROUP BY remote_host ORDER BY count DESC ;

-- Find top spammers in a date range
SELECT count(remote_host) as count, remote_host  FROM tls_connection WHERE '2016-04-16 00:00:00.000+00' < date AND date < '2016-11-10 00:00:00.000+00' GROUP BY remote_host ORDER BY count DESC ;

-- Delete - WARNING ! CAN TAKE A LARGE AMOUT OF TIME (~ 1h every 20.000 connections)
DELETE FROM tls_connection WHERE remote_host = 'example.com' ;

-- Delete first 1000 connections
DELETE FROM tls_connection WHERE id IN (SELECT id FROM tls_connection WHERE remote_host = 'example.com' LIMIT 1000) ;

-- Delete first 1000 connections in a date range
DELETE FROM tls_connection WHERE
  id IN (SELECT id FROM tls_connection WHERE remote_host = 'example.com' AND '2016-04-16 00:00:00.000+00' < date AND date < '2016-11-10 00:00:00.000+00' LIMIT 1000) ;

-- Delete first 1000 connections and excluding tls_test_instance referenced connections.
DELETE FROM tls_connection WHERE
  id IN (SELECT id FROM tls_connection WHERE
    id IN (SELECT id FROM tls_connection WHERE
      remote_host = '122.166.205.98' AND '2016-04-16 00:00:00.000+00' < date AND date < '2016-11-10 00:00:00.000+00')
    AND id NOT IN (SELECT connection_id FROM tls_test_instance WHERE
      sut_host = '122.166.205.98' AND connection_id IS NOT NULL)
    LIMIT 1000
  );

-- If deletion is blocked by foreign keys : find blocker elements :
SELECT count(connection_id) FROM tls_connection_tls_connection_data_item WHERE connection_id
IN (SELECT id FROM tls_connection WHERE remote_host = 'example.com') ;

-- If deletion is blocked by foreign keys : delete blocker elements :
DELETE FROM tls_connection_tls_connection_data_item WHERE connection_id IN (SELECT id FROM tls_connection WHERE
remote_host = 'example.com') ;


SELECT count(id) FROM tls_connection WHERE
  id IN (SELECT id FROM tls_connection WHERE remote_host = '122.166.205.98' AND '2016-04-16 00:00:00.000+00' < date AND date < '2016-11-10 00:00:00.000+00')
  AND id NOT IN (SELECT connection_id FROM tls_test_instance WHERE sut_host = '122.166.205.98' AND connection_id IS NOT NULL)
  AND id NOT IN (SELECT connection_id FROM tls_connection_tls_connection_data_item WHERE connection_id IS NOT NULL) ;


----------------------
-- SYSLOG COLLECTOR --
----------------------

-- Find top spammers
SELECT count(sender_ip) as count, sender_ip FROM syslog_message GROUP BY sender_ip ORDER BY count DESC ;

-- Delete
DELETE FROM syslog_message WHERE sender_ip = '1.2.3.4' ;