def getAnonymizedDomainUsageOnDay(nodeid,date,timezone):
arr_date = date.split('-')
sdate = datetime.datetime(int(arr_date[0]), int(arr_date[1]), int(arr_date[2]), 0, 0, 0)
edate = datetime.datetime(int(arr_date[0]), int(arr_date[1]), int(arr_date[2]), 23, 59, 59)
delta = datetime.timedelta(hours=timezone)
sdate = sdate - delta
edate = edate - delta
start = sdate.strftime("%Y-%m-%d %H:%M:%S+00")
end = edate.strftime("%Y-%m-%d %H:%M:%S+00")
cmd = "select t.timestamp as d, t.domain, t.bytes as s from bismark_passive.bytes_per_domain_per_hour as t where t.node_id='%s' and t.timestamp between '%s' and '%s' and domain is null order by d asc,s desc"%(nodeid,start,end)
res = sql.run_data_cmd(cmd)
out = {}
# Deal with data from database first
for rec in res:
date = rec[0][0:len(rec[0]) - 3]
try:
out[date].append((rec[1],rec[2]))
except:
out[date] = []
out[date].append((rec[1],rec[2]))
return out
def getHouseMetaInfo(hid):
unittype = 'household'
tab = unitTables[unittype]
digest = get_digest(hid=hid)
cmd = "select id,address,details,photo from %s where digest = '%s'"\
%(tab,digest)
print cmd
res = sql.run_data_cmd(cmd)
try:
return (1,(res[0][0],res[0][1],res[0][2],res[0][3]))
except:
return (0,('ERROR: No match found'))
def getUserInfo(digest):
unittype = 'user'
tab = unitTables[unittype]
cmd = "select id,parentdigest from %s where digest = '%s'"%(tab,digest)
res = sql.run_data_cmd(cmd)
try:
if res[0] == 0:
return (0,"ERROR: No match found")
except:
return (1,[])
#if len(res) == 0 or
res = res[0]
return (1,[getHouseInfo(res[1])[1],res[0]])
def getHouseInfo(digest):
unittype = 'household'
tab = unitTables[unittype]
cmd = "select id from %s where digest = '%s'"%(tab,digest)
res = sql.run_data_cmd(cmd)
try:
if res[0] == 0:
return (0,"ERROR: No match found")
except:
return (1,[])
#if len(res) == 0 or res[0] == 0:
# return (0,"ERROR: No match found")
return (1,res[0][0])
def _getChildren(tab,pdigest):
cmd = "select id from %s where parentdigest = '%s'"\
%(tab,pdigest)
res = sql.run_data_cmd(cmd)
try:
if res[0] == 0:
return (0,('ERROR: No match found'))
except:
return (1,[])
children = []
for rec in res:
children.append(rec[0])
return (1,children)
def getUserMetaInfo(hid,uid):
unittype = 'user'
if uid == '':
uid = default_user
tab = unitTables[unittype]
digest = get_digest(hid=hid,uid=uid)
cmd = "select id,name,details,photo from %s where digest = '%s'"\
%(tab,digest)
res = sql.run_data_cmd(cmd)
try:
return (1,(res[0][0],res[0][1],res[0][2],res[0][3]))
except:
return (0,('ERROR: No match found'))
def get_id_from_table(table,did,ts):
if did in traceroutearr:
if ts in traceroutearr[did]:
return traceroutearr[did][ts]
if did not in traceroutearr:
traceroutearr[did] = {}
cmd = "SELECT encode(id,'escape') from %s where deviceid = '%s' \
and eventstamp = to_timestamp(%s)"%(table,did,ts)
print cmd
res = sql.run_data_cmd(cmd,conn=conn)
#print res,str(res[0][0])
traceroutearr[did][ts] = str(res[0][0])
return traceroutearr[did][ts]
def getBytesBetween(hid,start,end,timezone):
# start and end should be in datetime format
delta = datetime.timedelta(hours=timezone)
start = start - delta
start = start.strftime("%Y-%m-%d %H:%M:%S+00")
end = end - delta
end = end.strftime("%Y-%m-%d %H:%M:%S+00")
cmd = "select t.timestamp as d, t.bytes as s from bismark_passive.bytes_per_hour as t where t.node_id='%s' and t.timestamp between '%s' and '%s' order by d asc"%(hid,start,end)
res = sql.run_data_cmd(cmd)
return res
def get_id_from_table(table,did,srcip,dstip,ts):
tup = (srcip,dstip,ts)
if did in traceroutearr:
if tup in traceroutearr[did]:
return traceroutearr[did][tup]
if did not in traceroutearr:
traceroutearr[did] = {}
cmd = "SELECT encode(id,'escape') from %s where deviceid = '%s' \
and srcip = '%s' and dstip = '%s' and eventstamp = to_timestamp(%s)"%(table,did,srcip,dstip,ts)
print cmd
res = sql.run_data_cmd(cmd,conn=conn)
#print res,str(res[0][0])
traceroutearr[did][tup] = str(res[0][0])
return traceroutearr[did][tup]
def get_digest(hid=None,uid=None,did=None):
if hid == None:
return None
if uid == None and did == None:
pcmd = "'%s'"%(hid)
elif did == None:
pcmd = "'%s','%s'"%(uid,hid)
else:
if uid == None:
return None
pcmd = "'%s','%s','%s'"%(did,uid,hid)
cmd = 'select md5(get_textid(%s))'%(pcmd)
res = sql.run_data_cmd(cmd)
return res[0][0]
def getDeviceInfo(digest):
unittype = 'device'
tab = unitTables[unittype]
cmd = "select id,parentdigest from %s where digest = '%s'"%(tab,digest)
res = sql.run_data_cmd(cmd)
try:
if res[0] == 0:
return (0,"ERROR: No match found")
except:
return (1,[])
#if len(res) == 0 or res[0] == 0:
#return (0,"ERROR: No match found")
res = res[0]
pinfo = getUserInfo(res[1])[1]
pinfo.append(res[0])
return (1,pinfo)
def getDeviceUsageInterval(macs,start,end,timezone):
delta = datetime.timedelta(hours=timezone)
arr_sdate = start.split('-')
sdate = datetime.datetime(int(arr_sdate[0]), int(arr_sdate[1]), int(arr_sdate[2]), 0, 0, 0)
sdate = sdate - delta
start = sdate.strftime("%Y-%m-%d %H:%M:%S+00")
arr_edate = end.split('-')
edate = datetime.datetime(int(arr_edate[0]), int(arr_edate[1]), int(arr_edate[2]), 0, 0, 0)
edate = edate - delta
end = edate.strftime("%Y-%m-%d %H:%M:%S+00")
gmacs = get_group(macs,"'")
cmd = "select devices.name, date(t.timestamp) as d, sum(t.bytes) from bismark_passive.bytes_per_device_per_hour as t,devices where t.mac_address in %s and devices.macid[1]=t.mac_address and t.timestamp between '%s' and '%s' group by devices.name,t.mac_address,d order by t.mac_address,d asc"%(gmacs,start,end)
# cmd = "select t.mac_address, date(t.timestamp) as d, sum(t.bytes) from bismark_passive.bytes_per_device_per_hour as t where t.mac_address in %s and t.timestamp between '%s' and '%s' group by t.mac_address,d order by t.mac_address,d asc"%(gmacs,start,end)
res = sql.run_data_cmd(cmd)
out = {}
thedate = ''
value = 0
sd = datetime.datetime(int(arr_sdate[0]), int(arr_sdate[1]), int(arr_sdate[2]))
ed = datetime.datetime(int(arr_edate[0]), int(arr_edate[1]), int(arr_edate[2]))
# Deal with data from database first
for rec in res:
try:
out[rec[0]].append((rec[1],rec[2]))
except:
out[rec[0]] = []
out[rec[0]].append((rec[1],rec[2]))
# Fill in 0s for missing dates (where usage is actually 0).
for dev in out:
for n in range((ed-sd).days+1):
found = False
curr_date_d = sd+datetime.timedelta(n)
curr_date = (curr_date_d).timetuple()
for entry in out[dev]:
entry_date = time.strptime(entry[0],'%Y-%m-%d')
if curr_date == entry_date:
found = True
break
if found is False:
scurr_date_d = curr_date_d.strftime("%Y-%m-%d")
out[dev].append((scurr_date_d,0))
out[dev] = sorted(out[dev], key=lambda dates: dates[0])
return out
def getAllBytesOnDay(date,timezone):
# Get all node_id
cmd = "select distinct t.node_id from bismark_passive.bytes_per_hour as t"
res = sql.run_data_cmd(cmd)
# Get byte usage for each node_id
out = {}
for rec in res:
out[rec[0]] = []
total = 0;
bytes = getBytesOnDay(rec[0], date, timezone)
for byte in bytes:
total += byte[1]
out[rec[0]].append(byte)
if total == 0:
del out[rec[0]]
return out
def getDeviceUsageOnDay(macs,date,timezone):
gmacs = get_group(macs,"'")
arr_date = date.split('-')
sdate = datetime.datetime(int(arr_date[0]), int(arr_date[1]), int(arr_date[2]), 0, 0, 0)
edate = datetime.datetime(int(arr_date[0]), int(arr_date[1]), int(arr_date[2]), 23, 59, 59)
delta = datetime.timedelta(hours=timezone)
sdate = sdate - delta
edate = edate - delta
start = sdate.strftime("%Y-%m-%d %H:%M:%S+00")
end = edate.strftime("%Y-%m-%d %H:%M:%S+00")
cmd = "select devices.name, t.timestamp as d, t.bytes from bismark_passive.bytes_per_device_per_hour as t,devices where t.mac_address in %s and devices.macid[1]=t.mac_address and t.timestamp between '%s' and '%s' order by t.mac_address,d asc"%(gmacs,start,end)
#cmd = "select t.mac_address, t.timestamp as d, t.bytes from bismark_passive.bytes_per_device_per_hour as t where t.mac_address in %s and t.timestamp between '%s' and '%s' order by t.mac_address,d asc"%(gmacs, start, end)
res = sql.run_data_cmd(cmd)
out = {}
# Deal with data from database first
for rec in res:
try:
out[rec[0]].append((rec[1][0:len(rec[1]) - 3],rec[2]))
except:
out[rec[0]] = []
out[rec[0]].append((rec[1][0:len(rec[1]) - 3],rec[2]))
# Fill in 0s for missing hours (where usage is actually 0)
one_hour = datetime.timedelta(hours=1)
for dev in out:
idate = datetime.datetime(*(time.strptime(start, "%Y-%m-%d %H:%M:%S+00")[0:6]))
index = 0
for x in xrange(24):
sdate = idate.strftime("%Y-%m-%d %H:%M:%S")
try:
tdate = datetime.datetime(*(time.strptime(out[dev][index][0], "%Y-%m-%d %H:%M:%S")[0:6]))
if idate.hour == tdate.hour:
index = index + 1
else:
out[dev].append((sdate,0))
except:
out[dev].append((sdate,0))
idate = idate + one_hour
out[dev] = sorted(out[dev], key=lambda dates: dates[0])
return out
def _getChildrenAll(unittype,pdigest):
if unittype in ['user','device']:
det = 'u.name,u.details'
if unittype in ['household']:
det = 'u.address,u.details'
utab = unitTables[unittype]
ctab = capTables[unittype]
cmd = "select u.id,%s,c.capped,c.cap,c.usage from %s as u,%s as c \
where u.digest = c.digest and u.parentdigest = '%s'"\
%(det,utab,ctab,pdigest)
res = sql.run_data_cmd(cmd,prnt=0)
try:
if res[0] == 0:
return (0,('ERROR: No match found'))
except:
return (1,[])
children = []
for rec in res:
children.append(rec[0:])
return (1,children)
def getBytesOnDay(nodeid,date,timezone):
arr_date = date.split('-')
sdate = datetime.datetime(int(arr_date[0]), int(arr_date[1]), int(arr_date[2]), 0, 0, 0)
edate = datetime.datetime(int(arr_date[0]), int(arr_date[1]), int(arr_date[2]), 23, 59, 59)
delta = datetime.timedelta(hours=timezone)
sdate = sdate - delta
edate = edate - delta
start = sdate.strftime("%Y-%m-%d %H:%M:%S+00")
end = edate.strftime("%Y-%m-%d %H:%M:%S+00")
cmd = "select t.timestamp as d, t.bytes as s from bismark_passive.bytes_per_hour as t where t.node_id='%s' and t.timestamp between '%s' and '%s' order by d asc"%(nodeid,start,end)
res = sql.run_data_cmd(cmd)
out = []
# Deal with data from database first
for rec in res:
try:
out.append((rec[0][0:len(rec[0]) - 3],rec[1]))
except:
out = []
out.append((rec[0][0:len(rec[0]) - 3],rec[1]))
# Fill in 0s for missing hours (where usage is actually 0)
one_hour = datetime.timedelta(hours=1)
idate = datetime.datetime(*(time.strptime(start, "%Y-%m-%d %H:%M:%S+00")[0:6]))
index = 0
for x in xrange(24):
sdate = idate.strftime("%Y-%m-%d %H:%M:%S")
try:
tdate = datetime.datetime(*(time.strptime(out[index][0], "%Y-%m-%d %H:%M:%S")[0:6]))
if idate.hour == tdate.hour:
index = index + 1
else:
out.append((sdate,0))
except:
out.append((sdate,0))
idate = idate + one_hour
out = sorted(out, key=lambda dates: dates[0])
return out
def getDeviceMacs(hid,uid,devices):
macs = []
unittype = 'device'
tab = unitTables[unittype]
if uid == '':
uid = default_user
for did in devices:
digest = get_digest(hid=hid,uid=uid,did=did)
cmd = "select id,macid from %s where digest = '%s'"%(tab,digest)
res = sql.run_data_cmd(cmd)
print cmd,res
try:
if res[0] == 0:
return (0,'ERROR')
except:
if len(res) == 0:
return (1,[])
rec = res[0]
tmac = rec[1].replace('{','').replace('}','').split(',')
macs.extend(tmac)
return (1,macs)
def getDomainUsageInterval(nodeid,topn,start,end,timezone):
delta = datetime.timedelta(hours=timezone)
arr_sdate = start.split('-')
sdate = datetime.datetime(int(arr_sdate[0]), int(arr_sdate[1]), int(arr_sdate[2]), 0, 0, 0)
sdate = sdate - delta
start = sdate.strftime("%Y-%m-%d %H:%M:%S+00")
arr_edate = end.split('-')
edate = datetime.datetime(int(arr_edate[0]), int(arr_edate[1]), int(arr_edate[2]), 0, 0, 0)
edate = edate - delta
end = edate.strftime("%Y-%m-%d %H:%M:%S+00")
cmd = "select t.domain, sum(t.bytes) as s from bismark_passive.bytes_per_domain_per_hour as t where node_id = '%s' and t.timestamp between '%s' and '%s' group by t.domain order by s desc"%(nodeid,start,end)
res = sql.run_data_cmd(cmd)
doms = []
tot = 0
for rec in res:
tot += int(rec[1])
if rec[0] is not None:
doms.append((rec[0],rec[1]))
tdoms = doms[:topn]
# print tot
out = {"other":[]}
otot = 0
for domd in tdoms:
try:
out[domd[0]].append((domd[1],float(domd[1])/tot))
otot += domd[1]
except:
out[domd[0]] = []
out[domd[0]].append((domd[1],float(domd[1])/tot))
otot += domd[1]
if tot != 0:
out["other"].append((tot-otot,(tot-otot)/tot))
else:
out["other"].append((0,0))
return out
def getStartBillingDate(hid):
digest = get_digest(hid=hid)
cmd = "select startdt from household_caps_curr where digest='%s'"%(digest)
res = sql.run_data_cmd(cmd)
return res
