-
Robert Latta authoredRobert Latta authored
opsdb.py 8.69 KiB
from inventory_provider.db import db
equipment_location_query = """SELECT
e.absid,
e.name AS equipment_name,
p.name AS pop_name,
p.abbreviation AS pop_abbreviation,
p.site_id AS pop_site_id,
p.country,
g.longitude,
g.latitude
FROM
equipment e
INNER JOIN pop p
ON p.absid = e.PTR_pop
INNER JOIN geocoding g
ON g.absid = p.PTR_geocoding
WHERE
e.status != 'terminated'
AND e.status != 'disposed'
ORDER BY
FIELD(e.status,
'spare',
'planned',
'ordered',
'installed',
'operational')"""
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,
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
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,
e.name AS equipment,
e_b.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.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
INNER JOIN equipment e
ON e.absid = cc.PTR_equip_b
LEFT JOIN equipment e_b
ON e_b.absid = cc.PTR_equip_a
LEFT JOIN events
ON events.absid = cc.PTR_project
INNER JOIN equipment_card ec
ON ec.absid = cc.PTR_card_b
LEFT JOIN equipment_card ec_b
ON ec_b.absid = cc.PTR_card_a
LEFT JOIN organisation o
ON o.absid = ec.manufacturer
LEFT JOIN port_plugin pp
ON pp.PTR_card = cc.PTR_card_b AND pp.port = cc.port_b
LEFT JOIN port_plugin pp_b
ON pp_b.PTR_card = cc.PTR_card_a
AND pp_b.port = cc.port_a
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_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):
with db.cursor(connection) as crs:
crs.execute(circuit_hierarchy_query)
r = _convert_to_dict(crs)
return r
def get_equipment_location_data(connection):
with db.cursor(connection) as crs:
crs.execute(equipment_location_query)
r = _convert_to_dict(crs)
return r