· 9 years ago · Dec 20, 2016, 02:26 PM
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# def iterate_aggregated_data(aggr_list):
175# for items in aggr_list:
176# time = items[0]
177# local_site_name = items[2]
178# remote_site_name = items[3]
179# aggregated_data = items[4:]
180# check_in_analytics_api_dict(local_site_name,remote_site_name)
181# return(local_site_name)
182
183
184# def check_in_analytics_api_dict(local_site_name,remote_site_name,time):
185
186
187
188tm = read_data_csv('analytics-sla-timeseries-metrics.csv')
189#print tm
190# tc = get_unique_vcpe(tm)
191# print tc
192
193vcpe_local_site = get_unique_vcpe_local_site(tm)
194vcpe_remote_site = get_unique_vcpe_remote_site(tm)
195
196print vcpe_local_site
197print vcpe_remote_site
198
199
200vcpe_data_dict_local_site = {}
201vcpe_data_dict_remote_site = {}
202
203for items in vcpe_local_site:
204 li = get_data_per_cpe_wrt_remote_site(items,tm)
205 vcpe_data_dict_local_site[items] = li
206
207for items in vcpe_remote_site:
208 li = get_data_per_cpe_wrt_local_site(items,tm)
209 vcpe_data_dict_remote_site[items] = li
210
211
212#import pdb; pdb.set_trace()
213
214aggr_list_data = read_data_csv_aggregated('avg_analytics.csv')
215
216get_last_times = get_last_date_time(aggr_list_data)
217print get_last_times
218
219#import pdb; pdb.set_trace()
220
221
222
223# for items in get_last_times:
224# time = items[0]
225# local_site_name = items[2]
226# remote_site_name = items[3]
227# aggregated_data = items[4:]
228
229# print "Local Site Name :",local_site_name
230# print "Keys Local Site :",vcpe_data_dict_local_site.keys()
231# print "Remote Site Name :",remote_site_name
232# #print "Keys Local Site :",vcpe_data_dict_local_site.keys()
233# try:
234# sla_local_site_list = vcpe_data_dict_local_site[local_site_name]
235# print "Success @@@@@@@@@@@@@@"
236# print "Using Local Site Keys :",local_site_name
237
238# for entries in sla_local_site_list:
239# if remote_site_name in entries:
240# #import pdb; pdb.set_trace()
241# #print "Hurray !!!!!!!"
242# #print remote_site_name
243# print entries
244
245# else:
246# print "cannot find the the remote site"
247# pass
248# #import pdb; pdb.set_trace()
249# #print sla_dict_local_site
250# except KeyError as e:
251# print "Bypassing"
252# pass
253# #print sla_dict_local_site
254
255def check_remote_dicts(any_site_name,datas_to_check):
256 print datas_to_check
257 for entries in datas_to_check:
258 if any_site_name in datas_to_check:
259 print entries
260
261
262
263for items in get_last_times:
264 time = items[0]
265 local_site_name = items[2]
266 remote_site_name = items[3]
267 aggregated_data = items[4:]
268
269 print "Local Site Name :",local_site_name
270 print "Keys Local Site :",vcpe_data_dict_local_site.keys()
271 print "Remote Site Name :",remote_site_name
272 #print "Keys Local Site :",vcpe_data_dict_local_site.keys()
273 if local_site_name in vcpe_data_dict_local_site.keys():
274 print "hurray!!!!!!!!!!!!"
275 datas_to = vcpe_data_dict_local_site[local_site_name]
276 check_remote_dicts(remote_site_name,datas_to)
277
278
279
280
281
282
283# cpe_data_dict = {}
284
285# for items in tc:
286# li = get_data_per_cpe(items,tm)
287# cpe_data_dict[items] = li
288
289
290
291
292# dates_list = []
293# for val in cpe_data_dict['Trbleshoot-vcpe1']:
294# dates_list.append(datetime.datetime.strptime(val[0].strip(), "%Y-%m-%d %H:%M:%S"))
295
296
297# # closet_time = datetime.datetime(2016, 12, 19, 6, 39)
298# # closest_record = sorted(dates_list, key=lambda M: abs(closet_time - M))[0]
299# # print closest_record
300
301# nr = get_closest_timestamps('2016-12-19 06:20:00',dates_list)
302# #print nr
303# for v in cpe_data_dict['Trbleshoot-vcpe1']:
304# print v[0]
305# import pdb; pdb.set_trace()
306# if nr in v[0]:
307# print "****"
308# print v
309# else:
310# pass