aGmsY56q

1import csv
2import datetime
3import sqlite3
4import os
5import re
6
7def date_key(row):
8	return datetime.datetime.strptime(row[0].strip(), "%Y-%m-%d %H:%M:%S")
9
10def read_data_csv(filename):
11	excel_data = []
12	for row in csv.reader(open(filename)):
13		excel_data.append(row)
14	table_headers = excel_data.pop(0) ### removing headers
15	excel_data.sort(key=date_key)
16	return excel_data
17
18
19
20
21def create_db_with_dict(data_keys,data_values):
22	data_key = data_keys.replace('-','_') ### Remove special charecters
23	print "="*20
24	print data_key+"    :"+str(len(data_values))+" Records"
25	conn = sqlite3.connect(data_key)
26	c = conn.cursor()
27	c.executescript("DROP TABLE IF EXISTS "+data_key+"; CREATE TABLE "+data_key+" (id INTEGER PRIMARY KEY AUTOINCREMENT,date_time TEXT, applianceName TEXT, tenantName TEXT, localAccCktId INTEGER, localAccCktName TEXT, remoteAccCktId INTEGER, remoteAccCktName TEXT, localSiteId INTEGER, localSiteName TEXT, remoteSiteId INTEGER, remoteSiteName TEXT, fwdClass TEXT, applianceId INTEGER, tenantId INTEGER, delay INTEGER, fwdDelayVar INTEGER, revDelayVar INTEGER, fwdLoss INTEGER, revLoss INTEGER, fwdLossRatio INTEGER, revLossRatio INTEGER, pduLossRatio INTEGER)")
28	conn.commit()
29	for row1 in data_values:
30		row = [w.replace('/', '-') for w in row1]
31		c.execute("INSERT INTO "+data_key+" (date_time, applianceName, tenantName, localAccCktId, localAccCktName, remoteAccCktId, remoteAccCktName, localSiteId, localSiteName, remoteSiteId, remoteSiteName, fwdClass, applianceId, tenantId, delay, fwdDelayVar, revDelayVar, fwdLoss, revLoss, fwdLossRatio, revLossRatio, pduLossRatio) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", row)
32		conn.commit()
33	conn.close()
34
35
36
37
38def get_new_aggr(db_name,time_in_min):
39	conn = sqlite3.connect(db_name)
40	c = conn.cursor()
41	last_id = 0	
42	c.execute("select max(id),min(date_time) from "+db_name+";")
43	total_datas = c.fetchall()
44	total_max_id = total_datas[0][0]
45	while last_id != total_max_id:
46		c.execute("select min(id), max(id) from "+db_name+" where date_time between (select min(date_time) from "+db_name+" where id > "+str(last_id)+") and (select datetime((select min(date_time) from "+db_name+" where id > "+str(last_id)+"), +'"+str(time_in_min)+" minutes'));")
47		id_datas = c.fetchall()
48		print id_datas
49		last_id = id_datas[0][1]
50		#total_max_id = id_datas[0][1]
51		print "Ratio     :: "+str(id_datas[0][0])+":"+str(id_datas[0][1])
52		#import pdb; pdb.set_trace()
53		c.execute("select date_time from "+db_name+" where id >= "+str(id_datas[0][0])+" and id <= "+str(id_datas[0][1])+";")
54		timestamp_list = []
55		for row in c:
56			timestamp_list.append(row)
57		c.execute("select avg(delay),avg(fwdDelayVar),avg(revDelayVar),avg(fwdLoss),avg(revLoss),avg(fwdLossRatio),avg(revLossRatio),avg(pduLossRatio) from "+db_name+" where id >= "+str(id_datas[0][0])+" and id <= "+str(id_datas[0][1])+";")
58		avg_datas = c.fetchall()
59		write_to_csv_data = []
60		all_dates = []
61		timestamp_list1 = [i[0] for i in timestamp_list]
62		coll_dates = '__'.join(str(x) for x in timestamp_list1)
63		tm = list(avg_datas[0])
64		tm.insert(0,coll_dates)
65		write_datas_to_csv('avg_analytics.csv',tm)
66		if total_max_id == last_id:
67			break
68	conn.close()
69	cleanup(db_name)
70
71
72
73
74def get_unique_vcpe(data_list):
75	app = []
76	#import pdb; pdb.set_trace()
77	for a in data_list:
78		mm = a[2]
79		if mm not in app:
80			app.append(mm)
81	app = [v for v in app if "controller" not in v ]
82	return app
83
84
85
86def get_data_per_cpe(cpe_name,data_list):
87	data_by_cpe = []
88	for items in data_list:
89		if cpe_name in items:
90			data_by_cpe.append(items)
91	# if len(data_by_cpe)%3 != 0:
92	# 	print "exception on records"
93	return data_by_cpe
94
95
96def write_headers_to_csv(filename):
97	headers = ["date","AVG-delay","AVG-fwdDelayVar","AVG-RevDelayVar","AVG-fwdLoss","AVG-revLoss","AVG-fwdLossRatio","AVG-revLossRatio","AVG-pduLossRatio"]
98	mp = open(filename,'a')
99	wr1 = csv.writer(mp, dialect='excel')
100	wr1.writerow(headers)
101	mp.close()
102
103
104
105def write_datas_to_csv(filename,datas):
106	print datas
107	#print dates
108	mp = open(filename,'a')
109	wr = csv.writer(mp, dialect='excel')
110	wr.writerow(datas)
111	mp.close()
112
113
114
115
116def get_closest_timestamps(times,times_list):
117	closet_times = datetime.datetime.strptime(times.strip(), "%Y-%m-%d %H:%M:%S")
118	closest_record = sorted(dates_list, key=lambda M: abs(closet_times - M))[0]
119	print closest_record
120	return closest_record
121
122
123
124def search_in_dict(cpe_type,record):
125	for k,v in cpe_type.iteritems:
126		datas_list = cpe_type[k]
127
128
129
130
131tm = read_data_csv('analytics-sla-timeseries-metrics.csv')
132#print tm
133tc = get_unique_vcpe(tm)
134print tc
135
136cpe_data_dict = {}
137
138for items in tc:
139 	li = get_data_per_cpe(items,tm)
140 	cpe_data_dict[items] = li
141
142dates_list = []
143for val in cpe_data_dict['Trbleshoot-vcpe1']:
144	dates_list.append(datetime.datetime.strptime(val[0].strip(), "%Y-%m-%d %H:%M:%S"))
145
146
147# closet_time = datetime.datetime(2016, 12, 19, 6, 39)
148# closest_record = sorted(dates_list, key=lambda M: abs(closet_time - M))[0]
149# print closest_record
150
151nr = get_closest_timestamps('2016-12-19 06:20:00',dates_list)
152#print nr
153for v in cpe_data_dict['Trbleshoot-vcpe1']:
154	print v[0]
155	import pdb; pdb.set_trace()
156	if nr in v[0]:
157		print "****"
158	 	print v
159	else:
160	 	pass