def get_config_data(did, app_id):
"""For a device and application id, get the current data collected. This is going to be very
ugly as we add new device and app types here."""
dbc = local_db()
if app_id == 1799: # SilverPeak Orchestrator
sql = """select
max(case when object = 19752 then data end) hostname,
max(case when object = 19755 then data end) ip,
max(case when object = 19763 then data end) model,
max(case when object = 19756 then data end) serial,
max(case when object = 19753 then data end) version
from dynamic_app_data_%s.dev_config_%s""" % (app_id, did)
if app_id == 1793: # SilverPeak EdgeConnect
sql = """select
max(case when object = 19696 then data end) hostname,
max(case when object = 19698 then data end) ip,
max(case when object = 19699 then data end) model,
max(case when object = 19701 then data end) serial,
max(case when object = 19711 then data end) version
from dynamic_app_data_%s.dev_config_%s """ % (app_id, did)
dbc.execute(sql)
results = dbc.fetchall()
return results
def get_org_devices(org):
"""Function to get the device ID list for an SL Org."""
sql = """select id from master_dev.legend_device
where roa_id = %s and length(ip) > 1 """ % org
dbc = local_db()
dbc.execute(sql)
results = dbc.fetchall()
return results
def event_from_case(case):
"""For an input case number, get the event id(s) active."""
dbc = local_db()
sql = """select id from master_events.events_active where
ext_ticket_ref = '%s' """ % case
dbc.execute(sql)
results = dbc.fetchall()
return results
def get_device_grp_devs(dgid):
"""Get the devices that belong to a device group."""
sql = """SELECT did FROM master_dev.device_groups_to_devices
where dgid = %s and did not in (select id from master_dev.legend_device
where roa_id = 1827)""" % dgid
dbc = local_db()
dbc.execute(sql)
results = dbc.fetchall()
return results
def rba_stage_add(**kwargs):
"""Add new data to the staging table for RBA correlation process."""
dbc = local_db()
sql = "insert into {tablename} ({columns}) values {values};" .format(
tablename = "fidelus.rba_case_staging",
columns = ', '.join(kwargs.keys()),
values = tuple(kwargs.values())
)
dbc.execute(sql)
def cmdb_tables():
"""Return list of CMDB CI tables in use."""
dbc = local_db()
sql = """select cmdb_table from fidelus.snow_london_cmdb where sys_id is not null group by cmdb_table"""
dbc.execute(sql)
results = dbc.fetchall()
dbc.close()
out = []
for i in results:
out.append(i[0])
return out
def get_active_event_case(event_id):
# get active events for this device where a case exists
# this list of events will be correlated together to prevent additional cases
dbc = local_db()
sql = """select ext_ticket_ref, force_ticket_uri from master_events.events_active
where id = %s and (ext_ticket_ref like 'CS%%') limit 1 """ % event_id
dbc.execute(sql)
results = dbc.fetchone()
if results:
result_dict = {}
result_dict["case_number"] = results[0]
result_dict["case_uri"] = results[1]
return result_dict
else:
return None
def devices_by_table(table):
"""Return SL device ids based on a cmdb table."""
dbc = local_db()
sql = """select mdev.id
from master_dev.legend_device mdev
inner join fidelus.snow_london_cmdb snow on mdev.class_type = snow.class_type
inner join master_custom.ent_device_custom mcustom on mdev.id = mcustom.ent_id
where snow.cmdb_table = '%s'""" % table
dbc.execute(sql)
results = dbc.fetchall()
dbc.close()
logging.info("SL query: %s\n" % sql)
out = []
for i in results:
out.append(i[0])
return out
def get_did_details(did):
""" function to get SL1 device and asset details from the DB """
sql = """select mdev.id, mdev.device, mdev.ip, mdev.create_date, mdev.class_type,
fslc.class, fslc.descript, fslc.snow_prod_model, fslc.sys_id product_sys_id,
fslc.cmdb_table, mbo.crm_id, mbo.billing_id, mbla.model, mbla.serial, mbac.hostname,
mc.servicenow_sysid
from master_dev.legend_device mdev
inner join master_biz.legend_asset mbla on mdev.id = mbla.did
inner join master_biz.organizations mbo on mdev.roa_id = mbo.roa_id
inner join master_biz.asset_configuration mbac on mbla.id = mbac.iid
inner join fidelus.snow_london_cmdb fslc on mdev.class_type = fslc.class_type
inner join master_custom.ent_device_custom mc on mdev.id = mc.ent_id
where mdev.id = %s""" % did
dbc = local_db()
dbc.execute(sql)
results = dbc.fetchall()
return results
note_list.append(json.dumps(_content, indent=4, sort_keys=True))
ci_sys_id = str(_content["result"]["sys_id"])
return ci_sys_id
# ------------------------------------------------------------------------------
# main runtime
# ------------------------------------------------------------------------------
EM7_RESULT = ""
did = EM7_VALUES["%x"]
note_list = []
# connect to EM7 DB
dbc = local_db()
# prepare a dict to hold the device and asset data for the CI
device_dict = {}
device = get_did_details(did)
note_list.append("Got details for device: %s" % did)
if device is not None:
device_dict["device_id"] = device[0][0]
device_dict["device_name"] = device[0][1]
device_dict["ip"] = device[0][2]
device_dict["create_date"] = device[0][3]
device_dict["class_type"] = device[0][4]
device_dict["dev_class"] = device[0][5]
device_dict["descript"] = device[0][6]
elif data is not None:
# Rearrange
# First extract the individual dictionaries
n = data['Name']
# These are the lists that will be plugged into result_handler['oid_name']
name = []
windows_services = '|'
for key in n.keys():
# generate the list anyway, to show all the services.
name.append((key, n[key]))
if white_list and n[key] in white_list:
windows_services = windows_services + n[key] + '|'
elif not white_list:
windows_services = windows_services + n[key] + '|'
# return the transformed data
result_handler['Name'] = name
#print "windows_services:"
#pprint.pprint(windows_services)
custom_attribute = {'c-windows_services': windows_services}
api = localAPI(local_db())
result = api.post("/api/device/%s" % self.did, custom_attribute)
except Exception, err:
self.internal_alerts.append((COLLECT_ERROR, "%s [%s] for device %s [%s] IP: %s Error: %s" % (app_name, self.app_id, self.name, self.did, self.ip, err)))
