opsdb.py 14.81 KiB
from inventory_provider.db import db
geant_lambda_sql = """SELECT
absid AS id,
name,
LOWER(status) AS status
FROM circuit
WHERE
status != 'terminated'
AND service_type = 'geant lambda'"""
circuit_hierarchy_query = """SELECT
pc.name AS parent_circuit,
pc.absid AS parent_circuit_id,
LOWER(pc.status) AS parent_circuit_status,
cc.name AS child_circuit,
cc.absid AS child_circuit_id,
LOWER(cc.status) AS child_circuit_status,
cg.segment_group AS segment_group
FROM circuit_glue cg
INNER JOIN circuit pc ON pc.absid = cg.PTR_circuit
INNER JOIN circuit cc ON cc.absid = cg.PTR_component"""
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.name as pop_name,
pop.abbreviation as pop_abbreviation,
pop_b.name as other_end_pop_name,
pop_b.abbreviation as other_end_pop_abbreviation,
e.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.card_id) AS card_id,
LOWER(ec_b.card_id) AS other_end_card_id,
LOWER(
IF(pp.interface_name IS NULL,
'', pp.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
ON pop.absid = cc.PTR_pop_a
LEFT JOIN pop pop_b
ON pop_b.absid = cc.PTR_pop_b
INNER JOIN equipment e
ON e.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
ON ec.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.manufacturer
LEFT JOIN port_plugin pp
ON pp.PTR_card = cc.PTR_card_a AND pp.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.name as pop_name,
pop.abbreviation as pop_abbreviation,
pop_b.name as other_end_pop_name,
pop_b.abbreviation as other_end_pop_abbreviation,
e.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.card_id) AS card_id,
LOWER(ec_b.card_id) AS other_end_card_id,
LOWER(
IF(pp.interface_name IS NULL,
'', pp.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
ON pop.absid = cc.PTR_pop_a
LEFT JOIN pop pop_b
ON pop_b.absid = cc.PTR_pop_b
INNER JOIN equipment e
ON e.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
ON ec.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.manufacturer
LEFT JOIN port_plugin pp
ON pp.PTR_card = cc.PTR_card_a AND pp.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 circuit_type IN ('path', 'service', 'l2circuit')
ORDER BY
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()
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"])
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_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 {
'name': r['equipment_name' + suffix],
'card id': r['card_id' + suffix],
'port number': r['port' + suffix],
'pop': {
'absid': r['pop_absid' + suffix],
'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 {
'absid': 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