def insert_data(rows, count):
"""
Attempt to insert the first 'count' items in 'rows' into the database table `samapper`.`Syslog`.
Exits script on critical failure.
Args:
rows: The iterable containing dictionaries to insert
(dictionaries must all have the same keys, matching column names)
count: The number of items from rows to insert
Returns:
None
"""
try:
truncated_rows = rows[:count]
# >>> values = [{"name": "foo", "email": "*****@*****.**"}, {"name": "bar", "email": "*****@*****.**"}]
# >>> db.multiple_insert('person', values=values, _test=True)
common.db.multiple_insert('Syslog', values=truncated_rows)
except Exception as e:
# see http://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html for codes
if e[0] == 1049: # Unknown database 'samapper'
dbaccess.create_database()
insert_data(rows, count)
elif e[0] == 1045: # Access Denied for '%s'@'%s' (using password: (YES|NO))
print(e[1])
print("Check your username / password? (dbconfig_local.py)")
sys.exit(1)
else:
print("Critical failure.")
print(e.message)
sys.exit(2)
def collect_stats(self):
self.stats = []
dbworks = dbaccess.test_database()
if dbworks == 1049: # database not found
dbaccess.create_database()
elif dbworks == 1045: # invalid username/password
self.stats.append(("Access Denied. Check username/password?", "Error 1045"))
return
rows = common.db.query("SELECT COUNT(*) AS 'cnt' FROM Syslog;")
self.stats.append(("Number of rows imported from the Syslog:", str(rows[0]['cnt'])))
rows = common.db.query(
"SELECT DestinationIP AS 'Address', COUNT(*) AS 'Connections' FROM Syslog GROUP BY Address;")
destIPs = len(rows)
self.stats.append(("Unique destination IP addresses:", str(destIPs)))
rows = common.db.query("SELECT SourceIP AS 'Address', COUNT(*) AS 'Connections' FROM Syslog GROUP BY Address;")
self.stats.append(("Unique source IP addresses:", str(len(rows))))
rows = common.db.query("SELECT DestinationPort AS 'Port', COUNT(*) AS 'Connections' FROM Syslog GROUP BY Port;")
lrows = rows.list()
self.stats.append(("Unique destination ports:", str(len(lrows))))
sys_lrows = [i for i in lrows if i['Port'] < 1024]
self.stats.append(("Unique system ports (0..1023):", str(len(sys_lrows))))
usr_lrows = [i for i in lrows if 1024 <= i['Port'] < 49152]
self.stats.append(("Unique user ports (1024..49151):", str(len(usr_lrows))))
prv_lrows = [i for i in lrows if 49152 <= i['Port'] < 65536]
self.stats.append(("Unique private ports (49152..65535):", str(len(prv_lrows))))
rows = common.db.query(
"SELECT DestinationIP AS 'Address', \
COUNT(DISTINCT DestinationPort) AS 'Ports', COUNT(*) AS 'Connections' \
FROM Syslog GROUP BY Address ORDER BY Ports DESC, Connections DESC LIMIT 100;")
if len(rows) > 0:
lrows = rows.list()
self.stats.append(("Max ports for one destination: ", str(lrows[0]['Ports'])))
count = 0
while count < len(lrows) and lrows[count]['Ports'] > 10:
count += 1
if count != len(lrows):
self.stats.append(("Percent of destinations with fewer than 10 ports: ", "{0:0.3f}%"
.format((destIPs - count) * 100 / float(destIPs))))
rows = common.db.query("SELECT COUNT(*) FROM Syslog GROUP BY SourceIP, DestinationIP, DestinationPort;")
self.stats.append(("Total Number of distinct connections (node -> node:port) stored:", str(len(rows))))
rows = common.db.query(
"SELECT COUNT(*) FROM Syslog GROUP BY SourceIP, DestinationIP, DestinationPort HAVING COUNT(*) > 100;")
self.stats.append(("Number of distinct connections occurring more than 100 times:", str(len(rows))))
# common.db.query(query)
# query = "DELETE FROM Links16 WHERE source8=dest8 && source16=dest16;"
# common.db.query(query)
def preprocess_log():
clean_tables()
import_nodes()
# grid-based positioning is being handled within import_nodes() now.
# related functions are retained in case of non-grid layouts in the future.
# position_nodes()
import_links()
print("Pre-processing completed successfully.")
# If running as a script, begin by executing main.
if __name__ == "__main__":
access = dbaccess.test_database()
if access == 1049:
dbaccess.create_database()
elif access == 1045:
print(
"Database access denied. Check you username / password? (dbconfig_local.py)"
)
else:
preprocess_log()
# time python preprocess.py >/dev/null 2>/dev/null
# is about half of
# time python preprocess.py