SHARE
TWEET

Untitled

a guest Apr 19th, 2019 106 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. import csv
  2. from datetime import datetime
  3. import psycopg2
  4.  
  5.  
  6.  
  7. #path to csv_file, make sure you save the file as a .csv
  8. csv_file = ''
  9.  
  10. #scrutinizer ip address
  11. scrutinizer_ip = ''
  12.  
  13. #remote user for postgres
  14. scrutinizer_user = 'scrutremote'
  15.  
  16. #password, this will be whatever the password is to login to webui as admin.
  17. scrutinizer_password = ''
  18.  
  19. #function used to convert the date in the CSV into Epoch.
  20. def convert_to_epoch(date):  
  21.     p = '%m/%d/%Y %H:%M'
  22.     epoch = datetime(1970, 1, 1)
  23.     if date.count(":") == 1:
  24.         return date.split(":")[0]
  25.     else:
  26.         date = ":".join(date.split(":", 2)[:2])
  27.         return int(((datetime.strptime(date, p) - epoch).total_seconds()))
  28.  
  29.  
  30.  
  31. try:
  32.     conn = psycopg2.connect("dbname='plixer' user={} host={} password={}".format(scrutinizer_user,scrutinizer_ip, scrutinizer_password))
  33.     print('connected to database, updating')
  34. except:
  35.     print("I am unable to connect to the database")
  36.  
  37.  
  38. cur = conn.cursor()
  39.  
  40. #open up the CSV and iterate through it.
  41.  
  42. record_counter = 0
  43.  
  44. with open(csv_file, newline='') as csvfile:
  45.     reader = csv.DictReader(csvfile)
  46.     for row in reader:
  47.         record_counter +=1
  48.         #find the IP Address from the spreadsheet    
  49.         ip_address = row['framedipaddress']
  50.  
  51.         #find the device_id from the spreadsheet    
  52.         device_id = row['imsi']
  53.  
  54.         #find the start time from the spreadsheet
  55.         first_time_stamp = convert_to_epoch(row['acctstarttime'])
  56.  
  57.         #find the last login time from the spreadsheet
  58.         last_time_stamp = convert_to_epoch(row['acctupdatetime'])  
  59.  
  60.         #execute the SQL query. We update the last_login to be the most recent here, this is only in the case if there are duplicates.
  61.         cur.execute("""INSERT INTO plixer.summary_authentication_ip
  62.         (ipaddress, username, domain, machine_name, datasource, first_seen, last_login, last_logoff)
  63.          VALUES
  64.          (inet_a2b('{}'), '{}','','','dave_newbern_data',{},{},'0') ON CONFLICT ON CONSTRAINT summary_authentication_ip_pkey DO UPDATE SET last_login = {} ; """.format(ip_address,device_id, first_time_stamp, last_time_stamp, last_time_stamp )  )
  65.      
  66.          
  67.  
  68. cur.execute("""SELECT COUNT(*) FROM plixer.summary_authentication_ip WHERE datasource='dave_newbern_data'""")
  69. record_count = cur.fetchall()
  70. record_count = record_count[0][0]
  71. print('There were {} rows in the spreadsheet, the table now holds {} unique users. This means we updated the last login for {} users'.format(record_counter, record_count, (record_counter - record_count)))
  72. conn.commit()
  73.  
  74. conn.close()
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top