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 router_info(dbid):
try:
router = db.routers.find_one({"_id": ObjectId(dbid)})
assert router
if request.method == 'POST':
if request.form.get("act") == "netmon_resync":
r = db.routers.update_one({"_id": ObjectId(dbid)}, {"$unset": {"netmon_id": 1}})
assert r.matched_count > 0
flash("<b>Netmon Sync triggered!</b>", "success")
return redirect(url_for("router_info", dbid=dbid))
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) and ("nickname" in router["user"]):
user = router["user"]["nickname"]
if is_authorized(user, session):
db.routers.delete_one({"_id": ObjectId(dbid)})
flash("<b>Router <i>%s</i> deleted!</b>" % router["hostname"], "success")
return redirect(url_for("index"))
else:
flash("<b>You are not authorized to perform this action!</b>", "danger")
except (bson.errors.InvalidId, AssertionError):
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)
def get_nearest_router():
res_router = db.routers.find_one(
{"position": {"$near": {"$geometry": {
"type": "Point",
"coordinates": [float(request.args.get("lng")), float(request.args.get("lat"))]
}}}},
{
"hostname": 1,
"neighbours": 1,
"position": 1,
}
)
r = make_response(bson2json(res_router))
r.mimetype = 'application/json'
return r
def router_info(dbid):
try:
if request.method == 'POST':
if request.form.get("act") == "netmon_resync":
r = db.routers.update_one({"_id": ObjectId(dbid)}, {"$unset": {"netmon_id": 1}})
assert r.matched_count > 0
flash("<b>Netmon Sync triggered!</b>", "success")
return redirect(url_for("router_info", dbid=dbid))
router = db.routers.find_one({"_id": ObjectId(dbid)})
assert router
except (bson.errors.InvalidId, AssertionError):
return "Router not found"
if request.args.get('json', None) != None:
del router["stats"]
return Response(bson2json(router, sort_keys=True, indent=4), mimetype='application/json')
else:
return render_template("router.html", router=router, tileurls=tileurls)
def router_info(dbid):
try:
if request.method == 'POST':
if request.form.get("act") == "netmon_resync":
r = db.routers.update_one({"_id": ObjectId(dbid)},
{"$unset": {
"netmon_id": 1
}})
assert r.matched_count > 0
flash("<b>Netmon Sync triggered!</b>", "success")
return redirect(url_for("router_info", dbid=dbid))
router = db.routers.find_one({"_id": ObjectId(dbid)})
assert router
except (bson.errors.InvalidId, AssertionError):
return "Router not found"
if request.args.get('json', None) != None:
del router["stats"]
return Response(bson2json(router, sort_keys=True, indent=4),
mimetype='application/json')
else:
return render_template("router.html", router=router, tileurls=tileurls)
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 get_nearest_router():
res_router = db.routers.find_one(
{
"position": {
"$near": {
"$geometry": {
"type":
"Point",
"coordinates": [
float(request.args.get("lng")),
float(request.args.get("lat"))
]
}
}
}
}, {
"hostname": 1,
"neighbours": 1,
"position": 1,
})
r = make_response(bson2json(res_router))
r.mimetype = 'application/json'
return r
def router_info(dbid):
try:
router = db.routers.find_one({"_id": ObjectId(dbid)})
assert router
if request.method == 'POST':
if request.form.get("act") == "netmon_resync":
r = db.routers.update_one({"_id": ObjectId(dbid)},
{"$unset": {
"netmon_id": 1
}})
assert r.matched_count > 0
flash("<b>Netmon Sync triggered!</b>", "success")
return redirect(url_for("router_info", dbid=dbid))
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) and ("nickname" in router["user"]):
user = router["user"]["nickname"]
if is_authorized(user, session):
db.routers.delete_one({"_id": ObjectId(dbid)})
flash(
"<b>Router <i>%s</i> deleted!</b>" %
router["hostname"], "success")
return redirect(url_for("index"))
else:
flash(
"<b>You are not authorized to perform this action!</b>",
"danger")
except (bson.errors.InvalidId, AssertionError):
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)
def bson_to_json(bsn):
return bson2json(bsn)
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 bson_to_json(bsn): return bson2json(bsn)
