def v2_routers():
try:
mysql = FreifunkMySQL()
statsv2 = mysql.fetchall("""
SELECT time, CAST(SUM(clients) AS SIGNED) clients, CAST(SUM(online) AS SIGNED) online, CAST(SUM(offline) AS SIGNED) offline, CAST(SUM(unknown) AS SIGNED) unknown, CAST(SUM(orphaned) AS SIGNED) orphaned, CAST(SUM(rx) AS SIGNED) rx, CAST(SUM(tx) AS SIGNED) tx
FROM stats_hood
INNER JOIN hoods ON hoods.id = stats_hood.hood
LEFT JOIN hoodsv2 ON hoodsv2.name = hoods.name
WHERE time > 1531612800 AND ( hoodsv2.id IS NOT NULL OR hoods.name REGEXP '[vV]2$' )
GROUP BY time
""")
statsv1 = mysql.fetchall("""
SELECT time, CAST(SUM(clients) AS SIGNED) clients, CAST(SUM(online) AS SIGNED) online, CAST(SUM(offline) AS SIGNED) offline, CAST(SUM(unknown) AS SIGNED) unknown, CAST(SUM(orphaned) AS SIGNED) orphaned, CAST(SUM(rx) AS SIGNED) rx, CAST(SUM(tx) AS SIGNED) tx
FROM stats_hood
INNER JOIN hoods ON hoods.id = stats_hood.hood
WHERE time > 1531612800 AND ( hoods.id > 9999 AND hoods.id < 11000 )
GROUP BY time
""")
mysql.close()
statsv2 = mysql.utcawaretupleint(statsv2,"time")
statsv1 = mysql.utcawaretupleint(statsv1,"time")
return render_template("v2routers.html",statsv2 = statsv2,statsv1 = statsv1)
except Exception as e:
writelog(CONFIG["debug_dir"] + "/fail_v2.txt", str(e))
import traceback
writefulllog("Warning: Failed to display v2 page: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))
def get_routers_by_nickname(nickname):
mysql = FreifunkMySQL()
users = mysql.fetchall(
"""
SELECT id
FROM users
WHERE nickname = %s
LIMIT 1
""", (nickname, ))
if len(users) == 0:
mysql.close()
return "User not found"
nodelist_data = dict()
nodelist_data['nodes'] = list()
routers = mysql.fetchall(
"""
SELECT router.id, hostname, contact, nickname, firmware, mac, fe80_addr
FROM router
INNER JOIN users ON router.contact = users.email
INNER JOIN router_netif ON router.id = router_netif.router
WHERE nickname = %s AND netif = 'br-mesh'
ORDER BY hostname ASC
""", (nickname, ))
mysql.close()
for router in routers:
nodelist_data['nodes'].append({
'name': router['hostname'],
'oid': str(router['id']),
'mac': router['mac'],
'ipv6_fe80_addr': router['fe80_addr']
})
return jsonify(nodelist_data)
def alfred2():
try:
start_time = time.time()
mysql = FreifunkMySQL()
banned = mysql.fetchall("""
SELECT mac FROM banned
""", (), "mac")
hoodsv2 = mysql.fetchall("""
SELECT name FROM hoodsv2
""", (), "name")
statstime = utcnow()
netifdict = mysql.fetchdict("SELECT id, name FROM netifs", (), "name",
"id")
hoodsdict = mysql.fetchdict("SELECT id, name FROM hoods", (), "name",
"id")
r = make_response(json.dumps({}))
r.mimetype = 'application/json'
if request.method == 'POST':
try:
alfred_data = request.get_json()
except Exception as e:
writelog(
CONFIG["debug_dir"] + "/fail_alfred2.txt",
"{} - {}".format(request.environ['REMOTE_ADDR'],
'JSON parsing failed'))
writefulllog(
"Warning: Error converting ALFRED2 data to JSON:\n__%s" %
(request.get_data(True, True).replace("\n", "\n__")))
r.headers['X-API-STATUS'] = "JSON parsing failed"
return r
if alfred_data:
# load router status xml data
i = 1
for mac, xml in alfred_data.items():
import_nodewatcher_xml(mysql, mac, xml, banned, hoodsv2,
netifdict, hoodsdict, statstime)
if (i % 500 == 0):
mysql.commit()
i += 1
mysql.commit()
r.headers['X-API-STATUS'] = "ALFRED2 data imported"
mysql.close()
writelog(
CONFIG["debug_dir"] + "/apitime.txt",
"%s - %.3f seconds (alfred2)" %
(request.environ['REMOTE_ADDR'], time.time() - start_time))
return r
except Exception as e:
writelog(CONFIG["debug_dir"] + "/fail_alfred.txt",
"{} - {}".format(request.environ['REMOTE_ADDR'], str(e)))
writefulllog("Warning: Error while processing ALFRED2 data: %s\n__%s" %
(e, traceback.format_exc().replace("\n", "\n__")))
r.headers['X-API-STATUS'] = "ERROR processing ALFRED2 data"
return r
def no_position():
mysql = FreifunkMySQL()
router_data = mysql.fetchall("""
SELECT router.id, hostname, contact, nickname, firmware
FROM router
LEFT JOIN users ON router.contact = users.email
WHERE lat IS NULL OR lng IS NULL
""")
mysql.close()
#nodelist_data = dict()
nodelist_data = list()
for router in router_data:
nick = router['nickname']
if not nick:
nick = ""
nodelist_data.append(
{
'name': router['hostname'],
'href': 'https://monitoring.freifunk-franken.de/routers/' + str(router['id']),
'firmware': router['firmware'],
'contact': router['contact'],
'owner': nick
}
)
nodelist_data2 = sorted(nodelist_data, key=itemgetter('owner'), reverse=False)
nodes = dict()
nodes['nodes'] = list(nodelist_data2)
return jsonify(nodes)
def nodelist():
mysql = FreifunkMySQL()
router_data = mysql.fetchall(
"""
SELECT id, hostname, status, clients, last_contact, lat, lng
FROM router
""", ())
router_data = mysql.utcawaretuple(router_data, "last_contact")
mysql.close()
nodelist_data = {'version': '1.0.0'}
nodelist_data['nodes'] = list()
for router in router_data:
nodelist_data['nodes'].append({
'id':
str(router['id']),
'name':
router['hostname'],
'node_type':
'AccessPoint',
'href':
'https://monitoring.freifunk-franken.de/routers/' +
str(router['id']),
'status': {
'online': router['status'] == 'online',
'clients': router['clients'],
'lastcontact': router['last_contact'].isoformat()
}
})
if router['lat'] and router['lng']:
nodelist_data['nodes'][-1]['position'] = {
'lat': router['lat'],
'long': router['lng']
}
return jsonify(nodelist_data)
def router_list():
where, tuple, query_str = parse_router_list_search_query(request.args)
mysql = FreifunkMySQL()
routers = mysql.fetchall(
"""
SELECT router.id, hostname, status, hood, contact, nickname, hardware, router.created, sys_uptime, last_contact, clients, reset, blocked, v2
FROM router
LEFT JOIN users ON router.contact = users.email
LEFT JOIN (
SELECT router, blocked.mac AS blocked FROM router_netif
INNER JOIN blocked ON router_netif.mac = blocked.mac
WHERE netif = 'br-mesh'
) AS b
ON router.id = b.router
{}
ORDER BY hostname ASC
""".format(where), tuple)
mysql.close()
routers = mysql.utcawaretuple(routers, "created")
routers = mysql.utcawaretuple(routers, "last_contact")
return render_template("router_list.html",
query_str=query_str,
routers=routers,
numrouters=len(routers))
def wifianalall():
mysql = FreifunkMySQL()
router_data = mysql.fetchall("""
SELECT hostname, mac, netif
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
GROUP BY id, netif
""",())
mysql.close()
return wifianalhelper(router_data,"ALL hoods")
def wifianal(selecthood):
mysql = FreifunkMySQL()
router_data = mysql.fetchall("""
SELECT hostname, mac, netif
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
WHERE hood = %s
GROUP BY id, netif
""",(selecthood,))
mysql.close()
return wifianalhelper(router_data,"Hood: " + selecthood)
def user_list():
mysql = FreifunkMySQL()
users = mysql.fetchall("SELECT id, nickname, email, created, admin FROM users ORDER BY nickname COLLATE utf8_unicode_ci ASC")
user_routers = stattools.router_user_sum(mysql)
mysql.close()
users = mysql.utcawaretuple(users,"created")
return render_template("user_list.html",
user_routers = user_routers,
users = users,
users_count = len(users)
)
def global_hoodstatistics(selecthood):
mysql = FreifunkMySQL()
hoods = stattools.hoods(mysql)
stats = mysql.fetchall("SELECT * FROM stats_hood WHERE hood = %s",(selecthood,))
stats = mysql.utcawaretuple(stats,"time")
numnew = len(hoods)-18
if numnew < 1:
numnew = 1
newest_routers = mysql.fetchall("""
SELECT id, hostname, hood, created
FROM router
WHERE hardware <> 'Legacy' AND hood = %s
ORDER BY created DESC
LIMIT %s
""",(selecthood,numnew,))
newest_routers = mysql.utcawaretuple(newest_routers,"created")
clients = stattools.total_clients(mysql)
router_status = stattools.router_status(mysql)
router_models = stattools.router_models(mysql,selecthood)
router_firmwares = stattools.router_firmwares(mysql,selecthood)
hoods_sum = stattools.hoods_sum(mysql)
mysql.close()
return render_template("statistics.html",
selecthood = selecthood,
stats = stats,
clients = clients,
router_status = router_status,
router_models = router_models,
router_firmwares = router_firmwares,
hoods = hoods,
hoods_sum = hoods_sum,
newest_routers = newest_routers
)
def get_routers_by_nickname(nickname):
mysql = FreifunkMySQL()
users = mysql.fetchall(
"""
SELECT id
FROM users
WHERE nickname = %s
LIMIT 1
""", (nickname, ))
mysql.close()
if len(users) == 0:
return "User not found"
return jsonify(nodelist_helper("AND nickname = %s", (nickname, )))
def get_routers_by_keyxchange_id(keyxchange_id):
mysql = FreifunkMySQL()
hood = mysql.findone(
"""
SELECT name
FROM hoods
WHERE id = %s
LIMIT 1
""", (int(keyxchange_id), ))
if not hood:
mysql.close()
return "Hood not found"
nodelist_data = dict()
nodelist_data['nodes'] = list()
routers = mysql.fetchall(
"""
SELECT router.id, hostname, hardware, mac, fe80_addr, firmware, lat, lng, contact, position_comment, description
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
WHERE hood = %s AND netif = 'br-mesh'
ORDER BY hostname ASC
""", (hood["name"], ))
mysql.close()
for router in routers:
nodelist_data['nodes'].append({
'name':
router['hostname'],
'ipv6_fe80_addr':
router['fe80_addr'],
'href':
'https://monitoring.freifunk-franken.de/routers/' +
str(router['id']),
'firmware':
router['firmware'],
'hardware':
router['hardware'],
'contact':
router['contact'],
'description':
router['description']
})
nodelist_data['nodes'][-1]['position'] = {
'lat': router['lat'],
'long': router['lng']
}
if router['position_comment']:
nodelist_data['nodes'][-1]['position']['comment'] = router[
'position_comment']
return jsonify(nodelist_data)
def get_router_by_mac(mac):
mysql = FreifunkMySQL()
res_routers = mysql.fetchall("""
SELECT id
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
WHERE mac = %s
GROUP BY mac, id
""",(mac.lower(),))
mysql.close()
if len(res_routers) != 1:
return redirect(url_for("router_list", q="mac:%s" % mac))
else:
return redirect(url_for("router_info", dbid=res_routers[0]["id"]))
def router_list():
where, tuple, query_str = parse_router_list_search_query(request.args)
mysql = FreifunkMySQL()
routers = mysql.fetchall("""
SELECT router.id, hostname, status, hood, contact, nickname, hardware, router.created, sys_uptime, clients, reset
FROM router
LEFT JOIN users ON router.contact = users.email
{}
ORDER BY hostname ASC
""".format(where),tuple)
mysql.close()
routers = mysql.utcawaretuple(routers,"created")
return render_template("router_list.html", query_str=query_str, routers=routers, numrouters=len(routers))
def get_nearest_router():
if request.args.get("layer") == "none":
r = make_response(bson2json(None))
r.mimetype = 'application/json'
return r
lng = float(request.args.get("lng"))
lat = float(request.args.get("lat"))
where = ""
if request.args.get("layer") == "v1":
where = " AND h.id IS NOT NULL "
elif request.args.get("layer") == "v2":
where = " AND h.id IS NULL "
mysql = FreifunkMySQL()
router = mysql.findone("""
SELECT r.id, r.hostname, r.lat, r.lng, r.description, r.routing_protocol,
( acos( cos( radians(%s) )
* cos( radians( r.lat ) )
* cos( radians( r.lng ) - radians(%s) )
+ sin( radians(%s) ) * sin( radians( r.lat ) )
)
) AS distance
FROM
router AS r
LEFT JOIN hoods AS h ON r.hood = h.name
WHERE r.lat IS NOT NULL AND r.lng IS NOT NULL """ + where + """
ORDER BY
distance ASC
LIMIT 1
""",(lat,lng,lat,))
router["neighbours"] = mysql.fetchall("""
SELECT nb.mac, nb.netif, nb.quality, r.hostname, r.id
FROM router_neighbor AS nb
INNER JOIN (
SELECT router, mac FROM router_netif GROUP BY mac, router
) AS net ON nb.mac = net.mac
INNER JOIN router as r ON net.router = r.id
WHERE nb.router = %s""",(router["id"],))
mysql.close()
for n in router["neighbours"]:
n["color"] = neighbor_color(n["quality"],n["netif"],router["routing_protocol"])
r = make_response(bson2json(router))
r.mimetype = 'application/json'
return r
def load_netif_stats(dbid):
netif = request.args.get("netif","")
mysql = FreifunkMySQL()
netiffetch = mysql.fetchall("""
SELECT netifs.name AS netif, rx, tx, time
FROM router_stats_netif
INNER JOIN netifs ON router_stats_netif.netif = netifs.id
WHERE router = %s AND netifs.name = %s
""",(dbid,netif,))
mysql.close()
for ns in netiffetch:
ns["time"] = {"$date": int(mysql.utcawareint(ns["time"]).timestamp()*1000)}
r = make_response(json.dumps(netiffetch))
r.mimetype = 'application/json'
return r
def dnsentries():
mysql = FreifunkMySQL()
router_data = mysql.fetchall("""
SELECT hostname, mac, MIN(ipv6) AS fd43
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
INNER JOIN router_ipv6 ON router.id = router_ipv6.router AND router_netif.netif = router_ipv6.netif
WHERE LEFT(ipv6,4) = 'fd43'
GROUP BY hostname, mac
""",())
mysql.close()
s = ""
for router in router_data:
s += router["mac"].replace(":","") + ".fff.community. 300 IN AAAA " + router["fd43"] + " ; " + router["hostname"] + "\n"
return Response(s,mimetype='text/plain')
def load_neighbor_stats(dbid):
mysql = FreifunkMySQL()
neighfetch = mysql.fetchall("""
SELECT quality, mac, time FROM router_stats_neighbor WHERE router = %s
""",(dbid,))
mysql.close()
neighdata = {}
for ns in neighfetch:
ns["time"] = {"$date": int(mysql.utcawareint(ns["time"]).timestamp()*1000)}
if not ns["mac"] in neighdata:
neighdata[ns["mac"]] = []
neighdata[ns["mac"]].append(ns)
r = make_response(json.dumps(neighdata))
r.mimetype = 'application/json'
return r
def router_mac(mac):
mysql = FreifunkMySQL()
res_routers = mysql.fetchall("""
SELECT id
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
WHERE mac = %s
GROUP BY mac, id
""",(mac2int(mac),))
mysql.close()
if len(res_routers) != 1:
return redirect(url_for("router_list", q="mac:%s" % mac))
elif request.args.get('fffconfig', None) != None:
return redirect(url_for("router_info", dbid=res_routers[0]["id"], fffconfig=1))
elif request.args.get('json', None) != None:
return redirect(url_for("router_info", dbid=res_routers[0]["id"], json=1))
else:
return redirect(url_for("router_info", dbid=res_routers[0]["id"]))
def alfred():
try:
start_time = time.time()
mysql = FreifunkMySQL()
#set_alfred_data = {65: "hallo", 66: "welt"}
set_alfred_data = {}
r = make_response(json.dumps(set_alfred_data))
#import cProfile, pstats, io
#pr = cProfile.Profile()
#pr.enable()
banned = mysql.fetchall("""
SELECT mac FROM banned
""",(),"mac")
statstime = utcnow()
netifdict = mysql.fetchdict("SELECT id, name FROM netifs",(),"name","id")
if request.method == 'POST':
alfred_data = request.get_json()
if alfred_data:
# load router status xml data
i = 1
for mac, xml in alfred_data.get("64", {}).items():
import_nodewatcher_xml(mysql, mac, xml, banned, netifdict, statstime)
if (i%500 == 0):
mysql.commit()
i += 1
mysql.commit()
r.headers['X-API-STATUS'] = "ALFRED data imported"
mysql.close()
#pr.disable()
#s = io.StringIO()
#sortby = 'cumulative'
#ps = pstats.Stats(pr, stream=s).sort_stats(sortby)
#ps.print_stats()
#print(s.getvalue())
writelog(CONFIG["debug_dir"] + "/apitime.txt", "%s - %.3f seconds" % (request.environ['REMOTE_ADDR'],time.time() - start_time))
r.mimetype = 'application/json'
return r
except Exception as e:
writelog(CONFIG["debug_dir"] + "/fail_alfred.txt", "{} - {}".format(request.environ['REMOTE_ADDR'],str(e)))
import traceback
writefulllog("Warning: Error while processing ALFRED data: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))
def dnslist():
mysql = FreifunkMySQL()
router_data = mysql.fetchall(
"""
SELECT hostname, mac, MIN(ipv6) AS fd43
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
INNER JOIN router_ipv6 ON router.id = router_ipv6.router AND router_netif.netif = router_ipv6.netif
WHERE LEFT(HEX(ipv6),4) = 'fd43'
GROUP BY hostname, mac
""", ())
mysql.close()
s = ""
for router in router_data:
s += int2shortmac(router["mac"]) + "\t" + bintoipv6(
router["fd43"]) + "\n"
return Response(s, mimetype='text/plain')
def get_nearest_router():
lng = float(request.args.get("lng"))
lat = float(request.args.get("lat"))
mysql = FreifunkMySQL()
res_router = mysql.findone(
"""
SELECT id, hostname, lat, lng, description,
( acos( cos( radians(%s) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(%s) )
+ sin( radians(%s) ) * sin( radians( lat ) )
)
) AS distance
FROM
router
WHERE lat IS NOT NULL AND lng IS NOT NULL
ORDER BY
distance ASC
LIMIT 1
""", (
lat,
lng,
lat,
))
res_router["neighbours"] = mysql.fetchall(
"""
SELECT nb.mac, nb.quality, nb.net_if, r.hostname, r.id
FROM router_neighbor AS nb
INNER JOIN (
SELECT router, mac FROM router_netif GROUP BY mac, router
) AS net ON nb.mac = net.mac
INNER JOIN router as r ON net.router = r.id
WHERE nb.router = %s""", (res_router["id"], ))
mysql.close()
r = make_response(bson2json(res_router))
r.mimetype = 'application/json'
return r
def global_gwstatistics(selectgw):
mysql = FreifunkMySQL()
stats = mysql.fetchall("SELECT * FROM stats_gw WHERE mac = %s",(mac2int(selectgw),))
selectgw = shortmac2mac(selectgw)
return helper_statistics(mysql,stats,None,selectgw)
def global_hoodstatistics(selecthood):
selecthood = int(selecthood)
mysql = FreifunkMySQL()
stats = mysql.fetchall("SELECT * FROM stats_hood WHERE hood = %s",(selecthood,))
return helper_statistics(mysql,stats,selecthood,None)
def global_statistics():
mysql = FreifunkMySQL()
stats = mysql.fetchall("SELECT * FROM stats_global")
return helper_statistics(mysql,stats,None,None)
def user_info(nickname):
mysql = FreifunkMySQL()
user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",(nickname,))
user["created"] = mysql.utcaware(user["created"])
if not user:
mysql.close()
return "User not found"
if request.method == 'POST':
if request.form.get("action") == "changepw":
if is_authorized(user["nickname"], session):
if request.form["password"] != request.form["password_rep"]:
flash("<b>Passwords did not match!</b>", "danger")
elif request.form["password"] == "":
flash("<b>Password must not be empty!</b>", "danger")
else:
set_user_password(mysql, user["nickname"], request.form["password"])
flash("<b>Password changed!</b>", "success")
else:
flash("<b>You are not authorized to perform this action!</b>", "danger")
elif request.form.get("action") == "changemail":
if is_authorized(user["nickname"], session):
if request.form["email"] != request.form["email_rep"]:
flash("<b>E-Mail addresses do not match!</b>", "danger")
elif not "@" in request.form["email"]:
flash("<b>Invalid E-Mail addresse!</b>", "danger")
else:
try:
set_user_email(mysql, user["nickname"], request.form["email"])
flash("<b>E-Mail changed!</b>", "success")
if not session.get('admin'):
password = base64.b32encode(os.urandom(10)).decode()
set_user_password(mysql, user["nickname"], password)
send_email(
recipient = request.form['email'],
subject = "Password for %s" % user['nickname'],
content = "Hello %s,\n\n" % user["nickname"] +
"You changed your email address on https://monitoring.freifunk-franken.de/\n" +
"To verify your new email address your password was changed to %s\n" % password +
"... and sent to your new address. Please log in and change it.\n\n" +
"Regards,\nFreifunk Franken Monitoring System"
)
mysql.close()
return logout()
else:
# force db data reload
user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",(nickname,))
user["created"] = mysql.utcaware(user["created"])
except AccountWithEmailExists:
flash("<b>There is already an account with this E-Mail Address!</b>", "danger")
else:
flash("<b>You are not authorized to perform this action!</b>", "danger")
elif request.form.get("action") == "changeadmin":
if session.get('admin'):
set_user_admin(mysql, nickname, request.form.get("admin") == "true")
# force db data reload
user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",(nickname,))
user["created"] = mysql.utcaware(user["created"])
else:
flash("<b>You are not authorized to perform this action!</b>", "danger")
elif request.form.get("action") == "changeabuse":
if session.get('admin'):
set_user_abuse(mysql, nickname, request.form.get("abuse") == "true")
# force db data reload
user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",(nickname,))
user["created"] = mysql.utcaware(user["created"])
else:
flash("<b>You are not authorized to perform this action!</b>", "danger")
elif request.form.get("action") == "deleteaccount":
if is_authorized(user["nickname"], session):
mysql.execute("DELETE FROM users WHERE nickname = %s LIMIT 1",(nickname,))
mysql.commit()
flash("<b>User <i>%s</i> deleted!</b>" % nickname, "success")
mysql.close()
if user["nickname"] == session.get("user"):
session.pop('user', None)
return redirect(url_for("user_list"))
else:
flash("<b>You are not authorized to perform this action!</b>", "danger")
routers = mysql.fetchall("""
SELECT router.id, hostname, status, hoods.id AS hoodid, hoods.name AS hood, firmware, hardware, created, sys_uptime, clients, router.lat, router.lng, reset, blocked, v2, local
FROM router
INNER JOIN hoods ON router.hood = hoods.id
LEFT JOIN (
SELECT router, blocked.mac AS blocked FROM router_netif
INNER JOIN blocked ON router_netif.mac = blocked.mac
WHERE netif = 'br-mesh'
) AS b
ON router.id = b.router
WHERE contact = %s
ORDER BY hostname ASC
""",(user["email"],))
mysql.close()
routers = mysql.utcawaretuple(routers,"created")
return render_template("user.html",
user=user,
routers=routers,
routers_count=len(routers),
authuser = is_authorized(user["nickname"], session),
authadmin = session.get('admin')
)
def router_info(dbid):
try:
mysql = FreifunkMySQL()
router = mysql.findone("""
SELECT router.*, hoods.id AS hoodid, hoods.name AS hoodname FROM router
INNER JOIN hoods ON router.hood = hoods.id
WHERE router.id = %s LIMIT 1
""",(dbid,))
mac = None
if router:
if request.args.get('fffconfig', None) != None:
mysql.close()
s = "\nconfig fff 'system'\n"
s += " option hostname '{}'\n".format(router["hostname"])
s += " option description '{}'\n".format(router["description"])
s += " option latitude '{}'\n".format(router["lat"] if router["lat"] else "")
s += " option longitude '{}'\n".format(router["lng"] if router["lng"] else "")
s += " option position_comment '{}'\n".format(router["position_comment"])
s += " option contact '{}'\n".format(router["contact"])
return Response(s,mimetype='text/plain')
router = mysql.utcaware(router,["created","last_contact"])
router["user"] = mysql.findone("SELECT nickname FROM users WHERE email = %s",(router["contact"],),"nickname")
router["netifs"] = mysql.fetchall("""SELECT * FROM router_netif WHERE router = %s""",(dbid,))
netifs = []
for n in router["netifs"]:
n["ipv6_addrs"] = mysql.fetchall("""SELECT ipv6 FROM router_ipv6 WHERE router = %s AND netif = %s""",(dbid,n["netif"],),"ipv6")
if n["netif"]=="br-mesh":
mac = n["mac"]
netifs.append(n["netif"])
router["neighbours"] = mysql.fetchall("""
SELECT nb.mac, nb.netif, nb.quality, r.hostname, r.id
FROM router_neighbor AS nb
LEFT JOIN (
SELECT router, mac FROM router_netif GROUP BY mac, router
) AS net ON nb.mac = net.mac
LEFT JOIN router as r ON net.router = r.id
WHERE nb.router = %s
ORDER BY nb.quality DESC
""",(dbid,))
# FIX SQL: only one from router_netif
router["gws"] = mysql.fetchall("""
SELECT router_gw.mac AS mac, quality, router_gw.netif AS netif, gw_class, selected, gw.name AS gw, n1.netif AS gwif, n2.netif AS batif, n2.mac AS batmac
FROM router_gw
LEFT JOIN (
gw_netif AS n1
INNER JOIN gw ON n1.gw = gw.id
LEFT JOIN gw_netif AS n2 ON n1.mac = n2.vpnmac AND n1.gw = n2.gw
) ON router_gw.mac = n1.mac
WHERE router = %s
""",(dbid,))
for gw in router["gws"]:
gw["label"] = gw_name(gw)
gw["batX"] = gw_bat(gw)
router["events"] = mysql.fetchall("""SELECT * FROM router_events WHERE router = %s""",(dbid,))
router["events"] = mysql.utcawaretuple(router["events"],"time")
## Create json with all data except stats
if request.args.get('json', None) != None:
mysql.close()
return Response(bson2json(router, sort_keys=True, indent=4), mimetype='application/json')
cwan = "blue"
cclient = "orange"
cbatman = "#29c329"
cvpn = "red"
chidden = "gray"
## Label netifs AFTER json if clause
for n in router["netifs"]:
netif = n["netif"];
desc = None
color = None
if netif == 'br-mesh':
desc = "Bridge"
elif netif.endswith('.1'):
desc = "Clients via Ethernet"
color = cclient
elif netif.endswith('.2'):
desc = "WAN"
color = cwan
elif netif.endswith('.3'):
desc = "Mesh via Ethernet"
color = cbatman
elif netif == "w2ap":
desc = "Clients @ 2.4 GHz"
color = cclient
elif netif == "w2mesh" or netif == "w2ibss":
desc = "Mesh @ 2.4 GHz"
color = cbatman
elif netif == "w2configap":
desc = "Config @ 2.4 GHz"
color = chidden
elif netif == "w5ap":
desc = "Clients @ 5 GHz"
color = cclient
elif netif == "w5mesh" or netif == "w5ibss":
desc = "Mesh @ 5 GHz"
color = cbatman
elif netif == "w5configap":
desc = "Config @ 5 GHz"
color = chidden
elif netif == "fffVPN":
desc = "Fastd VPN Tunnel"
color = cvpn
elif netif.startswith("l2tp"):
desc = "L2TP VPN Tunnel"
color = cvpn
elif netif.startswith("bat"):
desc = "Batman Interface"
elif netif.startswith("eth") and any(item.startswith("{}.".format(netif)) for item in netifs):
desc = "Switch"
elif netif == "eth1":
# already known from above: no switch; no one-port, as there must be eth0
if not "eth0" in netifs or any(item.startswith("eth0.") for item in netifs):
desc = "WAN"
color = cwan
else:
# Second port of Nanostation M2
desc = "Ethernet Multi-Port"
elif netif == "eth0":
if any(item.startswith("eth1.") for item in netifs):
# already known from above: no switch
desc = "WAN"
color = cwan
else:
# First port of Nanostation M2 or ONE-Port
desc = "Ethernet Multi-Port"
n["description"] = desc
n["color"] = color
## Set color for neighbors AFTER json if clause
for n in router["neighbours"]:
n["color"] = neighbor_color(n["quality"],n["netif"],router["routing_protocol"])
router["stats"] = mysql.fetchall("""SELECT * FROM router_stats WHERE router = %s""",(dbid,))
for s in router["stats"]:
s["time"] = mysql.utcawareint(s["time"])
threshold_neighstats = (utcnow() - datetime.timedelta(hours=24)).timestamp()
neighfetch = mysql.fetchall("""
SELECT quality, mac, time FROM router_stats_neighbor WHERE router = %s AND time > %s
""",(dbid,threshold_neighstats,))
neighdata = {}
for ns in neighfetch:
ns["time"] = {"$date": int(mysql.utcawareint(ns["time"]).timestamp()*1000)}
if not ns["mac"] in neighdata:
neighdata[ns["mac"]] = []
neighdata[ns["mac"]].append(ns)
neighident = mysql.fetchall("""
SELECT snb.mac, r.hostname, n.netif
FROM router_stats_neighbor AS snb
INNER JOIN router_netif AS n ON snb.mac = n.mac
INNER JOIN router AS r ON n.router = r.id
WHERE snb.router = %s AND n.netif <> 'w2ap' AND n.netif <> 'w5ap'
GROUP BY snb.mac, r.hostname, n.netif
""",(dbid,))
neighlabel = {}
for ni in neighident:
label = ni["hostname"]
# add network interface when there are multiple links to same node
for ni2 in neighident:
if label == ni2["hostname"] and ni["mac"] != ni2["mac"]:
# This shows the NEIGHBOR'S interface name
label += "@" + ni["netif"]
append = " (old)"
for nnn in router["neighbours"]:
if nnn["mac"] == ni["mac"]:
append = ""
neighlabel[ni["mac"]] = label + append
gwfetch = mysql.fetchall("""
SELECT quality, mac, time FROM router_stats_gw WHERE router = %s
""",(dbid,))
for ns in gwfetch:
ns["time"] = mysql.utcawareint(ns["time"])
if request.method == 'POST':
if request.form.get("act") == "delete":
# a router may not have a owner, but admin users still can delete it
if is_authorized(router["user"], session):
delete_router(mysql,dbid)
flash("<b>Router <i>%s</i> deleted!</b>" % router["hostname"], "success")
mysql.close()
return redirect(url_for("index"))
else:
flash("<b>You are not authorized to perform this action!</b>", "danger")
elif request.form.get("act") == "ban":
if session.get('admin'):
if mac:
ban_router(mysql,dbid)
delete_router(mysql,dbid)
flash("<b>Router <i>%s</i> banned!</b>" % router["hostname"], "success")
mysql.close()
return redirect(url_for("index"))
else:
flash("<b>Router has no br-mesh and thus cannot be banned!</b>", "danger")
else:
flash("<b>You are not authorized to perform this action!</b>", "danger")
elif request.form.get("act") == "changeblocked" and mac:
if session.get('admin'):
if request.form.get("blocked") == "true":
added = mysql.utcnow()
mysql.execute("INSERT INTO blocked (mac, added) VALUES (%s, %s)",(mac,added,))
mysql.execute("""
INSERT INTO router_events (router, time, type, comment)
VALUES (%s, %s, %s, %s)
""",(dbid,mysql.utcnow(),"admin","Marked as blocked",))
mysql.commit()
else:
mysql.execute("DELETE FROM blocked WHERE mac = %s",(mac,))
mysql.execute("""
INSERT INTO router_events (router, time, type, comment)
VALUES (%s, %s, %s, %s)
""",(dbid,mysql.utcnow(),"admin","Removed blocked status",))
mysql.commit()
router["events"] = mysql.fetchall("""SELECT * FROM router_events WHERE router = %s""",(dbid,))
router["events"] = mysql.utcawaretuple(router["events"],"time")
else:
flash("<b>You are not authorized to perform this action!</b>", "danger")
elif request.form.get("act") == "report":
abusemails = mysql.fetchall("SELECT email FROM users WHERE abuse = 1")
for a in abusemails:
send_email(
recipient = a["email"],
subject = "Monitoring: Router %s reported" % router["hostname"],
content = "Hello Admin,\n\n" +
"The router with hostname %s has been reported as abusive by a user.\n" % router["hostname"] +
"Please take care:\n" +
"%s\n\n" % url_for("router_info", dbid=dbid, _external=True) +
"Regards,\nFreifunk Franken Monitoring System"
)
flash("<b>Router reported to administrators!</b>", "success")
else:
mysql.close()
return "Router not found"
router["blocked"] = mysql.findone("""
SELECT blocked.mac
FROM router_netif AS n
LEFT JOIN blocked ON n.mac = blocked.mac
WHERE n.router = %s AND n.netif = 'br-mesh'
""",(dbid,),"mac")
mysql.close()
return render_template("router.html",
router = router,
mac = mac,
tileurls = tileurls,
neighstats = neighdata,
neighlabel = neighlabel,
gwstats = gwfetch,
authuser = is_authorized(router["user"], session),
authadmin = session.get('admin')
)
except Exception as e:
writelog(CONFIG["debug_dir"] + "/fail_router.txt", str(e))
import traceback
writefulllog("Warning: Failed to display router details page: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))
def get_nearest_router():
lng = float(request.args.get("lng"))
lat = float(request.args.get("lat"))
wherelist = []
if request.args.get("v1") == "on":
wherelist.append("(v2 = FALSE AND local = FALSE)")
if request.args.get("v2") == "on":
wherelist.append("(v2 = TRUE AND local = FALSE)")
if request.args.get("local") == "on":
wherelist.append("local = TRUE")
if wherelist:
where = " AND ( " + ' OR '.join(wherelist) + " ) "
else:
r = make_response(bson2json(None))
r.mimetype = 'application/json'
return r
mysql = FreifunkMySQL()
router = mysql.findone(
"""
SELECT r.id, r.hostname, r.lat, r.lng, r.description, r.routing_protocol,
( acos( cos( radians(%s) )
* cos( radians( r.lat ) )
* cos( radians( r.lng ) - radians(%s) )
+ sin( radians(%s) ) * sin( radians( r.lat ) )
)
) AS distance
FROM
router AS r
WHERE r.lat IS NOT NULL AND r.lng IS NOT NULL """ + where + """
ORDER BY
distance ASC
LIMIT 1
""", (
lat,
lng,
lat,
))
if not router:
r = make_response(bson2json(None))
r.mimetype = 'application/json'
return r
router["neighbours"] = mysql.fetchall(
"""
SELECT nb.mac, nb.netif, nb.quality, r.hostname, r.id
FROM router_neighbor AS nb
INNER JOIN (
SELECT router, mac FROM router_netif GROUP BY mac, router
) AS net ON nb.mac = net.mac
INNER JOIN router as r ON net.router = r.id
WHERE nb.router = %s""", (router["id"], ))
mysql.close()
for n in router["neighbours"]:
n["color"] = neighbor_color(n["quality"], n["netif"],
router["routing_protocol"])
r = make_response(bson2json(router))
r.mimetype = 'application/json'
return r
def router_info(dbid):
try:
mysql = FreifunkMySQL()
router = mysql.findone("""SELECT * FROM router WHERE id = %s LIMIT 1""",(dbid,))
if router:
if request.args.get('fffconfig', None) != None:
mysql.close()
s = "\nconfig fff 'system'\n"
s += " option hostname '{}'\n".format(router["hostname"])
s += " option description '{}'\n".format(router["description"])
s += " option latitude '{}'\n".format(router["lat"] if router["lat"] else "")
s += " option longitude '{}'\n".format(router["lng"] if router["lng"] else "")
s += " option position_comment '{}'\n".format(router["position_comment"])
s += " option contact '{}'\n".format(router["contact"])
return Response(s,mimetype='text/plain')
router = mysql.utcaware(router,["created","last_contact"])
router["user"] = mysql.findone("SELECT nickname FROM users WHERE email = %s",(router["contact"],),"nickname")
router["netifs"] = mysql.fetchall("""SELECT * FROM router_netif WHERE router = %s""",(dbid,))
for n in router["netifs"]:
n["ipv6_addrs"] = mysql.fetchall("""SELECT ipv6 FROM router_ipv6 WHERE router = %s AND netif = %s""",(dbid,n["netif"],),"ipv6")
router["neighbours"] = mysql.fetchall("""
SELECT nb.mac, nb.quality, nb.net_if, r.hostname, r.id
FROM router_neighbor AS nb
LEFT JOIN (
SELECT router, mac FROM router_netif GROUP BY mac, router
) AS net ON nb.mac = net.mac
LEFT JOIN router as r ON net.router = r.id
WHERE nb.router = %s
ORDER BY nb.quality DESC
""",(dbid,))
# FIX SQL: only one from router_netif
router["events"] = mysql.fetchall("""SELECT * FROM router_events WHERE router = %s""",(dbid,))
router["events"] = mysql.utcawaretuple(router["events"],"time")
router["stats"] = mysql.fetchall("""SELECT * FROM router_stats WHERE router = %s""",(dbid,))
for s in router["stats"]:
s["time"] = mysql.utcaware(s["time"])
netiffetch = mysql.fetchall("""
SELECT netif, rx, tx, time FROM router_stats_netif WHERE router = %s
""",(dbid,))
for ns in netiffetch:
ns["time"] = mysql.utcaware(ns["time"])
neighfetch = mysql.fetchall("""
SELECT quality, mac, time FROM router_stats_neighbor WHERE router = %s
""",(dbid,))
for ns in neighfetch:
ns["time"] = mysql.utcaware(ns["time"])
if request.method == 'POST':
if request.form.get("act") == "delete":
user = None
# a router may not have a owner, but admin users still can delete it
if ("user" in router):
user = router["user"]
if is_authorized(user, session):
#if True:
delete_router(mysql,dbid)
flash("<b>Router <i>%s</i> deleted!</b>" % router["hostname"], "success")
mysql.close()
return redirect(url_for("index"))
else:
flash("<b>You are not authorized to perform this action!</b>", "danger")
mysql.close()
else:
mysql.close()
return "Router not found"
if request.args.get('json', None) != None:
del router["stats"]
#FIXME: Only as admin
return Response(bson2json(router, sort_keys=True, indent=4), mimetype='application/json')
else:
return render_template("router.html", router=router, tileurls=tileurls, netifstats=netiffetch, neighstats=neighfetch)
except Exception as e:
writelog(CONFIG["debug_dir"] + "/fail_router.txt", str(e))
def nodelist_helper(where="", data=()):
# Suppresses routers without br-mesh
mysql = FreifunkMySQL()
router_data = mysql.fetchall(
"""
SELECT router.id, hostname, status, hoods.id AS hoodid, hoods.name AS hood, contact, nickname, hardware, firmware, clients, lat, lng, last_contact, mac, sys_loadavg, fe80_addr
FROM router
INNER JOIN hoods ON router.hood = hoods.id
INNER JOIN router_netif ON router.id = router_netif.router
LEFT JOIN users ON router.contact = users.email
WHERE netif = 'br-mesh' {}
ORDER BY hostname ASC
""".format(where), data)
router_data = mysql.utcawaretuple(router_data, "last_contact")
router_net = mysql.fetchall("""
SELECT id, netif, COUNT(router) AS count
FROM router
INNER JOIN router_netif ON router.id = router_netif.router
GROUP BY id, netif
""")
mysql.close()
net_dict = {}
for rs in router_net:
if not rs["id"] in net_dict:
net_dict[rs["id"]] = []
net_dict[rs["id"]].append(rs["netif"])
nodelist_data = {'version': '1.1.0'}
nodelist_data['nodes'] = list()
for router in router_data:
fastd = 0
l2tp = 0
if router["id"] in net_dict:
for netif in net_dict[router["id"]]:
if netif == 'fffVPN':
fastd += 1
elif netif.startswith('l2tp'):
l2tp += 1
#elif netif['netif'] == 'br-mesh' and 'mac' in netif:
# mac = netif["mac"]
if not router['mac']:
continue
nodelist_data['nodes'].append({
'id':
str(router['id']),
'name':
router['hostname'],
'mac':
int2mac(router['mac']),
'hoodid':
router['hoodid'],
'hood':
router['hood'],
'status':
router['status'],
'user':
router['nickname'],
'hardware':
router['hardware'],
'firmware':
router['firmware'],
'loadavg':
router['sys_loadavg'],
'href':
'https://monitoring.freifunk-franken.de/mac/' +
int2shortmac(router['mac']),
'clients':
router['clients'],
'lastcontact':
router['last_contact'].isoformat(),
'fe80_addr':
bintoipv6(router['fe80_addr']),
'uplink': {
'fastd': fastd,
'l2tp': l2tp
}
})
nodelist_data['nodes'][-1]['position'] = {
'lat': router['lat'],
'lng': router['lng']
}
return nodelist_data
