def insert_session_into_table(session_threshold):
mysql_conn = DBUtil.get_mysql_conn()
user_list = []
cur = mysql_conn.cursor()
cur.execute("SELECT * FROM users")
rows = cur.fetchall()
for row in rows:
user = User(c_ip=row[0], cs_user_agent=row[1])
user_list.append(user)
print "users:" + str(len(user_list))
session_list = []
user_count = 1
for user in user_list:
cur.execute("SELECT * FROM LOG WHERE c_ip=%s and cs_user_agent=%s ORDER BY log_time ASC", (user.c_ip, user.cs_user_agent))
rows = cur.fetchall()
session = Session(user.c_ip, user.cs_user_agent, session_threshold)
if user_count % 10 == 0:
print user_count
user_count = user_count + 1
for row in rows:
event = Event(date=None, time=None, s_sitename=row[1], s_computername=row[2], s_ip=row[3], cs_method=row[4], cs_uri_stem=row[5], cs_uri_query=row[6], s_port=row[7],
cs_username=row[8], c_ip=row[9], cs_version=row[10], cs_user_agent=row[11], cs_cookie=row[12], cs_referer=row[13], cs_host=row[14], sc_status=row[15], sc_substatus=row[16],
sc_win32_status=row[17], sc_bytes=row[18], cs_bytes=row[19], time_taken_ms=row[20], datetime_obj=row[0])
if session.is_in_this_session(event):
session.add_event(event)
else:
session_list.append(session)
# start new session
session = Session(user.c_ip, user.cs_user_agent, session_threshold)
session.add_event(event)
print "sessions: " + str(len(session_list))
session_id = 1
for session in session_list:
for event in session.event_list:
try:
cur.execute("INSERT INTO Sessions (session_id, log_time, s_sitename, s_computername, s_ip, cs_method, cs_uri_stem, cs_uri_query, s_port, cs_username, c_ip, cs_version, cs_user_agent, cs_cookie, cs_referer, cs_host, sc_status, sc_substatus, sc_win32_status, sc_bytes, cs_bytes, time_taken_ms) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
(session_id, event.log_time, event.s_sitename, event.s_computername, event.s_ip, event.cs_method, event.cs_uri_stem, event.cs_uri_query, event.s_port, event.cs_username, event.c_ip, event.cs_version, event.cs_user_agent, event.cs_cookie, event.cs_referer, event.cs_host, event.sc_status, event.sc_substatus, event.sc_win32_status, event.sc_bytes, event.cs_bytes, event.time_taken_ms))
mysql_conn.commit()
except Exception,e:
info = sys.exc_info()
print info[0],":",info[1]
print e
print traceback.format_exc()
mysql_conn.rollback()
session_id = session_id + 1
