opsdb.py 19.55 KiB
from inventory_provider.db import db
geant_lambda_sql = """SELECT
c.absid AS id,
c.name as name,
LOWER(c.status) AS status,
p.short_descr as project
FROM vcircuitconns c
left join events p
on p.absid = c.PTR_project
WHERE
c.status != 'terminated'
AND c.service_type = 'geant lambda'"""
circuit_hierarchy_query = """SELECT
pc.name AS parent_circuit,
pc.absid AS parent_circuit_id,
pc.circuit_type AS parent_circuit_type,
LOWER(pc.status) AS parent_circuit_status,
pp.short_descr AS parent_project,
cc.name AS child_circuit,
cc.absid AS child_circuit_id,
cc.circuit_type AS child_circuit_type,
LOWER(cc.status) AS child_circuit_status,
cp.short_descr AS child_project,
cg.segment_group AS segment_group
FROM circuit_glue cg
INNER JOIN vcircuitconns pc ON pc.absid = cg.PTR_circuit
INNER JOIN vcircuitconns cc ON cc.absid = cg.PTR_component
LEFT JOIN events pp on pp.absid = pc.PTR_project
LEFT JOIN events cp on cp.absid = cc.PTR_project"""
retrieve_services_query = """SELECT *
FROM (SELECT
c.absid AS id,
c.name,
LOWER(c.status) AS status,
LOWER(c.circuit_type) AS circuit_type,
LOWER(c.service_type) AS service_type,
events.short_descr AS project,
pop_a.name as pop_name,
pop_a.abbreviation as pop_abbreviation,
pop_b.name as other_end_pop_name,
pop_b.abbreviation as other_end_pop_abbreviation,
e_a.name AS equipment,
e_b.name AS other_end_equipment,
cc.port_a AS port,
cc.port_b AS other_end_port,
cc.int_LU_a AS logical_unit,
cc.int_LU_b AS other_end_logical_unit,
LOWER(o.name) AS manufacturer,
LOWER(ec_a.card_id) AS card_id,
LOWER(ec_b.card_id) AS other_end_card_id,
LOWER(
IF(pp_a.interface_name IS NULL,
'', pp_a.interface_name)) AS interface_name,
LOWER(
IF(pp_b.interface_name IS NULL,
'', pp_b.interface_name)) AS other_end_interface_name
FROM circuit c
INNER JOIN circuit_connections cc
ON cc.circ_absid = c.absid
LEFT JOIN pop pop_a
ON pop_a.absid = cc.PTR_pop_a
LEFT JOIN pop pop_b
ON pop_b.absid = cc.PTR_pop_b
INNER JOIN equipment e_a
ON e_a.absid = cc.PTR_equip_a
LEFT JOIN equipment e_b
ON e_b.absid = cc.PTR_equip_b
LEFT JOIN events
ON events.absid = cc.PTR_project
INNER JOIN equipment_card ec_a
ON ec_a.absid = cc.PTR_card_a
LEFT JOIN equipment_card ec_b
ON ec_b.absid = cc.PTR_card_b
LEFT JOIN organisation o
ON o.absid = ec_a.manufacturer
LEFT JOIN port_plugin pp_a
ON pp_a.PTR_card = cc.PTR_card_a
AND pp_a.port = cc.port_a
LEFT JOIN port_plugin pp_b
ON pp_b.PTR_card = cc.PTR_card_b
AND pp_b.port = cc.port_b
WHERE c.status != 'terminated' AND is_circuit = 1
UNION
SELECT
c.absid AS id,
c.name,
LOWER(c.status) AS status,
LOWER(c.circuit_type) AS circuit_type,
LOWER(c.service_type) AS service_type,
events.short_descr AS project,
pop_b.name as pop_name,
pop_b.abbreviation as pop_abbreviation,
pop_a.name as other_end_pop_name,
pop_a.abbreviation as other_end_pop_abbreviation,
e_b.name AS equipment,
e_a.name AS other_end_equipment,
cc.port_b AS port,
cc.port_a AS other_end_port,
cc.int_LU_b AS logical_unit,
cc.int_LU_a AS other_end_logical_unit,
LOWER(o.name) AS manufacturer,
LOWER(ec_b.card_id) AS card_id,
LOWER(ec_a.card_id) AS other_end_card_id,
LOWER(
IF(pp_b.interface_name IS NULL,
'', pp_b.interface_name)) AS interface_name,
LOWER(
IF(pp_a.interface_name IS NULL,
'', pp_a.interface_name)) AS other_end_interface_name
FROM circuit c
INNER JOIN circuit_connections cc
ON cc.circ_absid = c.absid
LEFT JOIN pop pop_a
ON pop_a.absid = cc.PTR_pop_a
LEFT JOIN pop pop_b
ON pop_b.absid = cc.PTR_pop_b
LEFT JOIN equipment e_a
ON e_a.absid = cc.PTR_equip_a
INNER JOIN equipment e_b
ON e_b.absid = cc.PTR_equip_b
LEFT JOIN events
ON events.absid = cc.PTR_project
LEFT JOIN equipment_card ec_a
ON ec_a.absid = cc.PTR_card_a
INNER JOIN equipment_card ec_b
ON ec_b.absid = cc.PTR_card_b
LEFT JOIN organisation o
ON o.absid = ec_b.manufacturer
LEFT JOIN port_plugin pp_a
ON pp_a.PTR_card = cc.PTR_card_a
AND pp_a.port = cc.port_a
LEFT JOIN port_plugin pp_b
ON pp_b.PTR_card = cc.PTR_card_b
AND pp_b.port = cc.port_b
WHERE c.status != 'terminated' AND is_circuit = 1)
AS inner_query
WHERE
equipment IS NOT NULL
AND equipment != ''
AND circuit_type IN (
'path', 'service', 'l2circuit', 'link-aggr-group')
ORDER BY
name,
FIELD(status,
'spare',
'planned',
'ordered',
'installed',
'operational')"""
def _convert_to_dict(crs):
return [dict((crs.description[i][0], "" if value is None else value)
for i, value in enumerate(row)) for row in crs.fetchall()]
def _infinera_field_update(record):
equipment_parts = record["equipment"].rsplit("-", 1)
card_parts = record["card_id"].split("-", 1)
record["interface_name"] = ""
record["equipment"] = equipment_parts[0]
try:
record["interface_name"] = equipment_parts[1] + "-"
except IndexError:
pass # Nothing to see here
try:
record["interface_name"] += card_parts[1]
except IndexError:
record["interface_name"] += card_parts[0]
if record["port"] is not None and record["port"] != "":
record["interface_name"] += "-" + record["port"]
record["interface_name"] = record["interface_name"] \
.replace("--", "-").upper()
#
equipment_parts = record["other_end_equipment"].rsplit("-", 1)
card_parts = record["other_end_card_id"].split("-", 1)
record["other_end_interface_name"] = ""
record["other_end_equipment"] = equipment_parts[0]
try:
record["other_end_interface_name"] = equipment_parts[1] + "-"
except IndexError:
pass # Nothing to see here
try:
record["other_end_interface_name"] += card_parts[1]
except IndexError:
record["other_end_interface_name"] += card_parts[0]
if record["other_end_port"] is not None and record["other_end_port"] != "":
record["other_end_interface_name"] += "-" + record["other_end_port"]
record["other_end_interface_name"] = record["other_end_interface_name"] \
.replace("--", "-").upper()
return record
def _juniper_field_update(record):
if not record["interface_name"]:
record["interface_name"] = record["card_id"]
if record["port"] is not None and record["port"] != "":
separator = "/" if "-" in record["interface_name"] else ""
record["interface_name"] += separator + str(record["port"])
if record["logical_unit"] is not None and record["logical_unit"] != "":
record["interface_name"] += "." + str(record["logical_unit"])
if not record["other_end_interface_name"]:
record["other_end_interface_name"] = record["other_end_card_id"]
if record["other_end_port"] is not None \
and record["other_end_port"] != "":
separator = ""
if "-" in record["other_end_interface_name"]:
separator = "/"
record["other_end_interface_name"] += \
separator + str(record["other_end_port"])
if record["other_end_logical_unit"] is not None \
and record["other_end_logical_unit"] != "":
record["other_end_interface_name"] += \
"." + str(record["other_end_logical_unit"])
return record
def _coriant_field_update(record):
record["interface_name"] = record["card_id"]
if record["port"] is not None and record["port"] != "":
separator = "/" if "-" in record["interface_name"] else ""
record["interface_name"] += separator + str(record["port"])
return record
def _update_fields(r):
func = globals().get("_" + r["manufacturer"] + "_field_update")
return func(r) if func else r
def get_geant_lambdas(connection): # pragma: no cover
with db.cursor(connection) as crs:
crs.execute(geant_lambda_sql)
r = _convert_to_dict(crs)
return r
def get_circuits(connection):
with db.cursor(connection) as crs:
crs.execute(retrieve_services_query)
r = _convert_to_dict(crs)
r = list(map(_update_fields, r))
return r
def get_circuit_hierarchy(connection): # pragma: no cover
with db.cursor(connection) as crs:
crs.execute(circuit_hierarchy_query)
r = _convert_to_dict(crs)
return r
def lookup_pop_info(connection, equipment_name):
query = """
SELECT
e.name AS equipment_name,
p.name AS pop_name,
p.abbreviation AS pop_abbreviation,
p.city AS pop_city,
p.country AS pop_country,
g.longitude,
g.latitude,
e.status
FROM equipment e
INNER JOIN pop p ON p.absid = e.PTR_pop
LEFT JOIN geocoding g ON g.absid = p.PTR_geocoding
WHERE (e.status = 'Installed' OR e.status = 'Operational')
AND e.name = %s
ORDER BY FIELD(e.status, 'Operational', 'Installed')
"""
def _row2rsp(row):
return {
'equipment-name': row['equipment_name'],
'status': row['status'],
'pop': {
'name': row['pop_name'],
'city': row['pop_city'],
'country': row['pop_country'],
'abbreviation': row['pop_abbreviation'],
'longitude': row['longitude'],
'latitude': row['latitude']
}
}
with db.cursor(connection) as crs:
crs.execute(query, [equipment_name])
rows = _convert_to_dict(crs)
return list([_row2rsp(r) for r in rows])
def lookup_lg_routers(connection):
# this is literally the definition of the view vlg_routers
# (cf. https://jira.software.geant.org/projects/LGR/issues/LGR-73)
# but with the city added and filtering on names instead of absid's
# for readability
INTERNAL_POP_NAMES = {
'Cambridge OC',
'DANTE Lab',
'Amsterdam GEANT Office'
}
query = """
SELECT
e.name AS router_name,
p.name AS pop_name,
p.city AS pop_city,
p.abbreviation AS pop_abbreviation,
g.latitude AS pop_latitude,
g.longitude AS pop_longitude,
p.country_code AS pop_country_code,
g.country AS pop_country,
g.city AS pop_city
FROM
opsdb.equipment e
LEFT JOIN opsdb.pop p ON p.absid = e.PTR_pop
LEFT JOIN opsdb.geocoding g ON g.absid = p.PTR_geocoding
LEFT JOIN opsdb.organisation o ON o.absid = e.PTR_owner
WHERE
e.model LIKE 'mx%'
AND e.status = 'Operational'
AND o.name = 'DANTE / GEANT'
AND NOT (e.name REGEXP 'vpn-proxy|vrr|taas')
"""
def _row2rsp(row):
print(row)
return {
'equipment name': row['router_name'],
'type':
'INTERNAL'
if row['pop_name'] in INTERNAL_POP_NAMES
else 'CORE',
'pop': {
'name': row['pop_name'],
'city': row['pop_city'],
'country': row['pop_country'],
'country code': row['pop_country_code'],
'abbreviation': row['pop_abbreviation'],
'longitude': row['pop_longitude'],
'latitude': row['pop_latitude'],
}
}
with db.cursor(connection) as crs:
crs.execute(query)
rows = _convert_to_dict(crs)
return list([_row2rsp(r) for r in rows])
def lookup_coriant_path(connection, equipment_name, card_id, port_number):
base_query = """
SELECT
vcc.absid,
vcc.category,
vcc.circuit_type,
vcc.service_type,
vcc.peering_type,
vcc.status,
vcc.name,
eq_a.name as equipment_name_a,
eq_b.name as equipment_name_b,
eqc_a.card_id as card_id_a,
eqc_a.card_id as card_id_b,
pop_a.name as pop_name_a,
pop_b.name as pop_name_b,
pop_a.abbreviation as pop_abbreviation_a,
pop_b.abbreviation as pop_abbreviation_b,
pop_a.absid as pop_absid_a,
pop_b.absid as pop_absid_b,
pop_a.city as pop_city_a,
pop_b.city as pop_city_b,
pop_a.country as pop_country_a,
pop_b.country as pop_country_b,
g_a.longitude as pop_longitude_a,
g_a.latitude as pop_latitude_a,
g_b.longitude as pop_longitude_b,
g_b.latitude as pop_latitude_b,
vcc.port_a,
vcc.port_b
FROM vcircuitconns vcc
LEFT JOIN equipment eq_a ON eq_a.absid = vcc.PTR_equip_a
LEFT JOIN equipment eq_b ON eq_b.absid = vcc.PTR_equip_b
LEFT JOIN equipment_card eqc_a ON eqc_a.absid = vcc.PTR_card_a
LEFT JOIN equipment_card eqc_b ON eqc_b.absid = vcc.PTR_card_b
LEFT JOIN pop pop_a ON pop_a.absid = vcc.PTR_pop_a
LEFT JOIN pop pop_b ON pop_b.absid = vcc.PTR_pop_b
LEFT JOIN geocoding g_a ON g_a.absid = pop_a.PTR_geocoding
LEFT JOIN geocoding g_b ON g_b.absid = pop_b.PTR_geocoding
"""
def _fields2rsp(row):
def _pop(r, suffix):
return {
'equipment name': r['equipment_name' + suffix],
'card id': r['card_id' + suffix],
'port number': r['port' + suffix],
'pop': {
'name': r['pop_name' + suffix],
'city': r['pop_city' + suffix],
'country': r['pop_country' + suffix],
'abbreviation': r['pop_abbreviation' + suffix],
'longitude': r['pop_longitude' + suffix],
'latitude': r['pop_latitude' + suffix]
}
}
return {
'id': row['absid'],
'category': row['category'],
'circuit_type': row['circuit_type'],
'service_type': row['service_type'],
'peering_type': row['peering_type'],
'status': row['status'],
'name': row['name'],
'a': _pop(row, '_a'),
'b': _pop(row, '_b'),
}
circuit_query = base_query + """
WHERE vcc.status <> 'Terminated'
AND (
(eq_a.name = %(equipment_name)s
AND eqc_a.card_id = %(card_id)s
AND vcc.port_a = %(port_number)s )
OR
(eq_b.name = %(equipment_name)s
AND eqc_b.card_id = %(card_id)s
AND vcc.port_b = %(port_number)s ))
ORDER BY FIELD(vcc.circuit_type, 'path') DESC
"""
parent_query = base_query + """
JOIN circuit_glue
ON circuit_glue.PTR_circuit = vcc.absid
WHERE circuit_glue.PTR_component = %s
AND circuit_type = 'Path'
"""
# for_testing = """
# SELECT parent.absid, child.absid,
# parent.circuit_type as parent_circuit_type,
# child.circuit_type as child_circuit_type,
# eq_a.name as equipment_name_a,
# eq_b.name as equipment_name_b,
# eqc_a.card_id as card_id_a,
# eqc_a.card_id as card_id_b,
# pop_a.name as pop_name_a,
# pop_b.name as pop_name_b,
# parent.port_a as port_a,
# parent.port_b as port_b,
# child.port_a as child_port_a,
# child.port_b as child_port_b
#
# FROM vcircuitconns parent
# RIGHT JOIN circuit_glue
# ON circuit_glue.PTR_circuit = parent.absid
# JOIN vcircuitconns child
# ON circuit_glue.PTR_component = child.absid
#
# LEFT JOIN equipment eq_a ON eq_a.absid = parent.PTR_equip_a
# LEFT JOIN equipment eq_b ON eq_b.absid = parent.PTR_equip_b
# LEFT JOIN equipment_card eqc_a ON eqc_a.absid = parent.PTR_card_a
# LEFT JOIN equipment_card eqc_b ON eqc_b.absid = parent.PTR_card_b
# LEFT JOIN pop pop_a ON pop_a.absid = parent.PTR_pop_a
# LEFT JOIN pop pop_b ON pop_b.absid = parent.PTR_pop_b
#
# WHERE parent.circuit_type = 'Path'
# AND child.circuit_type <> 'Path'
# AND child.status = 'Operational'
# AND (eq_a.name like 'grv%' or eq_b.name like 'grv%')
# """
args = {
'equipment_name': equipment_name,
'card_id': card_id,
'port_number': port_number
}
with db.cursor(connection) as crs:
crs.execute(circuit_query, args)
r = _convert_to_dict(crs)
if not r:
return None
circuit = r[0]
if circuit['circuit_type'].lower() == 'path':
return _fields2rsp(circuit)
crs.execute(parent_query, [circuit['absid']])
r = _convert_to_dict(crs)
return _fields2rsp(r[0]) if r else None
def get_service_users(connection, service_ids):
def _sublists(l, n):
for x in range(0, len(l), n):
yield l[x:x + n]
# not sure how to use a tuple in a prepared statement,
# so this is just doing a dumb string replacement ...
query = (
'select c.absid, o.name'
' from organisation o'
' join circuit_orgs co on co.org_absid=o.absid'
' join circuit c on c.absid=co.circ_absid'
' where co.org_type=\'Circuit User\''
' and c.absid in (%s)')
with db.cursor(connection) as crs:
for chunk in _sublists(service_ids, 20):
crs.execute(query % ','.join([str(x) for x in chunk]))
for r in crs.fetchall():
yield {'service_id': r[0], 'user': r[1]}