квотирование трафика

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
import psycopg2, psycopg2.extras, telnetlib
quote = 200
connection2 = "host=localhost user=nimnull dbname=billing"
conn = psycopg2.connect(connection2)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
addr_traf = dict()
#selecting traffic from monhtly summary table
cur.execute("select ipaddr, sum(total) as total from traf_daily \
JOIN billingv3 ON out = ipaddr \
JOIN users_states ON billingv3.usr_id = users_states.usr_id \
WHERE traf_daily.time_stamp > date_trunc('month', now()) \
AND \
users_states.new_state in (0,2,7) \
GROUP BY ipaddr \
ORDER BY total ")
for row in cur.fetchall():
addr_traf[row['ipaddr']] = int(row['total'])
#selectin' traf from daile & summarazin' it
cur.execute(" \
SELECT ipaddr, sum(bytes)/1024/1024 AS total \
FROM traf \
JOIN billingv3 ON out = ipaddr \
JOIN users_states ON billingv3.usr_id = users_states.usr_id \
WHERE traf.time_stamp::date = now()::date \
AND users_states.new_state IN (0,2,7) \
GROUP BY ipaddr \
HAVING sum(bytes)/1024/1024 > 1 \
ORDER BY total DESC")
for row in cur.fetchall():
if(addr_traf.has_key(row['ipaddr'])):
if(addr_traf.has_key(row['ipaddr'])):
addr_traf[row['ipaddr']] += int(row['total'])
else:
addr_traf[row['ipaddr']] = int(row['total'])
acl = open('/.1/tftp/no_out','w')
acl.write('!\nno access-l 90\n!\n')
#writin' down those with exceeded quote
for addr, traf_m in addr_traf.items():
if traf_m >= quote:
acl.write("access-l 90 perm h %s\n"%(addr,))
# print "%s - %d Mb"%(addr, traf_m)
acl.write('!\nend\n!\n')
acl.close()
CAT = "ххх.ххх.ххх.ххх"
tn = telnetlib.Telnet(CAT)
print tn.read_until("Username: ")
tn.write("******\n")
print tn.read_until("Password: ")
tn.write("******\n")
print tn.read_until("opp-gw0>")
tn.write("en\n")
print tn.read_until("Password: ")
tn.write("******\n")
print tn.read_until("opp-gw0#")
tn.write("copy tftp://ххх.ххх.ххх.ххх/no_out running-config\n")
print tn.read_until("Destination filename [running-config]? ")
tn.write("\n")
print tn.read_until("opp-gw0#")
tn.write("logout\n")
tn.close()