8wVFs51h

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	#import pdb; pdb.set_trace()
17	return excel_data
18
19
20
21def read_data_csv_aggregated(filename):
22	excel_data = []
23	for row in csv.reader(open(filename)):
24		excel_data.append(row)
25	table_headers = excel_data.pop(0) ### removing headers
26	#excel_data.sort(key=date_key)
27	#import pdb; pdb.set_trace()
28	return excel_data
29
30def get_last_date_time(data_list):
31	count = 0
32	for items in data_list:
33		my_date_time_coll = items[0].split("__")
34		last_date_time = my_date_time_coll[-1]
35		data_list[count][0] = last_date_time
36		count += 1
37	return data_list
38	
39
40
41
42
43
44def create_db_with_dict(data_keys,data_values):
45	data_key = data_keys.replace('-','_') ### Remove special charecters
46	print "="*20
47	print data_key+"    :"+str(len(data_values))+" Records"
48	conn = sqlite3.connect(data_key)
49	c = conn.cursor()
50	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)")
51	conn.commit()
52	for row1 in data_values:
53		row = [w.replace('/', '-') for w in row1]
54		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)
55		conn.commit()
56	conn.close()
57
58
59
60
61def get_new_aggr(db_name,time_in_min):
62	conn = sqlite3.connect(db_name)
63	c = conn.cursor()
64	last_id = 0	
65	c.execute("select max(id),min(date_time) from "+db_name+";")
66	total_datas = c.fetchall()
67	total_max_id = total_datas[0][0]
68	while last_id != total_max_id:
69		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'));")
70		id_datas = c.fetchall()
71		print id_datas
72		last_id = id_datas[0][1]
73		#total_max_id = id_datas[0][1]
74		print "Ratio     :: "+str(id_datas[0][0])+":"+str(id_datas[0][1])
75		#import pdb; pdb.set_trace()
76		c.execute("select date_time from "+db_name+" where id >= "+str(id_datas[0][0])+" and id <= "+str(id_datas[0][1])+";")
77		timestamp_list = []
78		for row in c:
79			timestamp_list.append(row)
80		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])+";")
81		avg_datas = c.fetchall()
82		write_to_csv_data = []
83		all_dates = []
84		timestamp_list1 = [i[0] for i in timestamp_list]
85		coll_dates = '__'.join(str(x) for x in timestamp_list1)
86		tm = list(avg_datas[0])
87		tm.insert(0,coll_dates)
88		write_datas_to_csv('avg_analytics.csv',tm)
89		if total_max_id == last_id:
90			break
91	conn.close()
92	cleanup(db_name)
93
94
95
96
97def get_unique_vcpe_local_site(data_list):
98	app = []
99	#import pdb; pdb.set_trace()
100	for a in data_list:
101		mm = a[1]
102		if mm not in app:
103			app.append(mm)
104	app = [v for v in app if "controller" not in v ]
105	return app
106
107def get_unique_vcpe_remote_site(data_list):
108	app = []
109	#import pdb; pdb.set_trace()
110	for a in data_list:
111		mm = a[2]
112		if mm not in app:
113			app.append(mm)
114	app = [v for v in app if "controller" not in v ]
115	return app
116
117
118def get_data_per_cpe_wrt_remote_site(cpe_name,data_list):
119	data_by_cpe = []
120	for items in data_list:
121		#import pdb; pdb.set_trace()
122		if cpe_name in items[2]:
123			data_by_cpe.append(items)
124	# if len(data_by_cpe)%3 != 0:
125	# 	print "exception on records"
126	return data_by_cpe
127
128
129def get_data_per_cpe_wrt_local_site(cpe_name,data_list):
130	data_by_cpe = []
131	for items in data_list:
132		#import pdb; pdb.set_trace()
133		if cpe_name in items[1]:
134			data_by_cpe.append(items)
135	# if len(data_by_cpe)%3 != 0:
136	# 	print "exception on records"
137	return data_by_cpe
138
139
140
141def write_headers_to_csv(filename):
142	headers = ["date","AVG-delay","AVG-fwdDelayVar","AVG-RevDelayVar","AVG-fwdLoss","AVG-revLoss","AVG-fwdLossRatio","AVG-revLossRatio","AVG-pduLossRatio"]
143	mp = open(filename,'a')
144	wr1 = csv.writer(mp, dialect='excel')
145	wr1.writerow(headers)
146	mp.close()
147
148
149
150def write_datas_to_csv(filename,datas):
151	print datas
152	#print dates
153	mp = open(filename,'a')
154	wr = csv.writer(mp, dialect='excel')
155	wr.writerow(datas)
156	mp.close()
157
158
159
160
161def get_closest_timestamps(times,times_list):
162	closet_times = datetime.datetime.strptime(times.strip(), "%Y-%m-%d %H:%M:%S")
163	closest_record = sorted(dates_list, key=lambda M: abs(closet_times - M))[0]
164	print closest_record
165	return closest_record
166
167
168
169def search_in_dict(cpe_type,record):
170	for k,v in cpe_type.iteritems:
171		datas_list = cpe_type[k]
172
173
174
175
176tm = read_data_csv('analytics-sla-timeseries-metrics.csv')
177#print tm
178# tc = get_unique_vcpe(tm)
179# print tc
180
181
182vcpe_local_site = get_unique_vcpe_local_site(tm)
183vcpe_remote_site = get_unique_vcpe_remote_site(tm)
184
185print vcpe_local_site
186print vcpe_remote_site
187
188
189
190# vcpe_local_site_dict = {}
191# vcpe_remote_site_dict = {}
192
193vcpe_data_dict_local_site = {}
194vcpe_data_dict_remote_site = {}
195
196for items in vcpe_local_site:
197	li = get_data_per_cpe_wrt_remote_site(items,tm)
198	vcpe_data_dict_local_site[items] = li
199
200for items in vcpe_remote_site:
201	li = get_data_per_cpe_wrt_local_site(items,tm)
202	vcpe_data_dict_remote_site[items] = li
203
204
205#import pdb; pdb.set_trace()
206
207aggr_list_data = read_data_csv_aggregated('avg_analytics.csv')
208#import pdb; pdb.set_trace()
209
210get_last_times = get_last_date_time(aggr_list_data)
211print get_last_times
212
213
214# cpe_data_dict = {}
215
216# for items in tc:
217#  	li = get_data_per_cpe(items,tm)
218#  	cpe_data_dict[items] = li
219
220
221
222
223# dates_list = []
224# for val in cpe_data_dict['Trbleshoot-vcpe1']:
225# 	dates_list.append(datetime.datetime.strptime(val[0].strip(), "%Y-%m-%d %H:%M:%S"))
226
227
228# # closet_time = datetime.datetime(2016, 12, 19, 6, 39)
229# # closest_record = sorted(dates_list, key=lambda M: abs(closet_time - M))[0]
230# # print closest_record
231
232# nr = get_closest_timestamps('2016-12-19 06:20:00',dates_list)
233# #print nr
234# for v in cpe_data_dict['Trbleshoot-vcpe1']:
235# 	print v[0]
236# 	import pdb; pdb.set_trace()
237# 	if nr in v[0]:
238# 		print "****"
239# 	 	print v
240# 	else:
241# 	 	pass