def get_ts_ci_not_in_zabbix(source, logger): try: ZabbixConfig = get_zabbix_conf() zapi = zabbix_conn(source, ZabbixConfig) hosts = zapi.host.get( output=["hostid", "host", "hostip", "name"], sortfield=["name"], selectInterfaces=["interfaceid", "hostid", "ip", "port"], selectInventory=['hardware', 'location']) ci_list_all = {} for host in hosts: if type(host['inventory']) != list and len( {'hardware', 'location'} & set(host['inventory'].keys())) == 2: if host['inventory']['hardware'] not in ('UNKNOWN', ''): host_hardware = host['inventory']['hardware'].split(';') host_location = host['inventory']['location'].split(';') for i in range(len(host_hardware)): if host_hardware[i].strip() not in ci_list_all and len(host_location[i]) <= 128 \ and ( host_location[i].strip().startswith('ТС') or host_location[i].strip().startswith('АС')): ci_list_all[host_hardware[i].strip( )] = host_location[i].strip() zbx_groups_dataframe = get_host_group_mapping_list_sql( ZabbixConfig[source]['group_table']) zbx_groups_dataframe.isnull().values.any() df_groups = zbx_groups_dataframe['CIs'].values.tolist() TS_CI_List = [] for host in hosts: hostInventory = host['inventory'] arrayLen = len(hostInventory) if arrayLen > 0: if 'hardware' in hostInventory.keys(): hostTSIDS = hostInventory["hardware"].split(";") for hostTSID in hostTSIDS: host_group_mapping_row = [ 1 for item in df_groups if hostTSID in item.split(',') ] if len(host_group_mapping_row ) == 0 and not checkInList( TS_CI_List, hostTSID): TS_CI_List.append(hostTSID) result = {} for TS_CI in TS_CI_List: if TS_CI.strip() in ci_list_all: result[TS_CI.strip()] = ci_list_all[TS_CI] return result except BaseException as e: logger.error(f'Error: {str(e)}') return {}
def remove_deleted_hosts(source): logger = get_logger('config') ZabbixConfig = get_zabbix_conf() zapi = zabbix_conn(source, ZabbixConfig) excluded_groupids = [group['groupid'] for group in zapi.hostgroup.get(output=['groupid', 'name'], filter={'name': excluded_groups})] all_hosts = zapi.host.get(output=['hostid', 'inventory_mode'], selectInventory=['serialno_a', 'url_c'], selectInterfaces=['ip', 'useip']) # exceptions processing host_with_disabled_inventory = [] host_with_empty_inventory = [] host_with_empty_url_c = [] host_with_empty_serialno_a = [] host_with_UNKNOWN_fields_inventory = [] host_for_work = [] for host in all_hosts: if type(host["inventory"]) == list: host_with_empty_inventory.append(host['hostid']) continue if host["inventory_mode"] == "-1": host_with_disabled_inventory.append(host['hostid']) continue if host["inventory"]["url_c"] == "": host_with_empty_url_c.append(host['hostid']) continue if host["inventory"]["serialno_a"] == "": host_with_empty_serialno_a.append(host['hostid']) continue if (host["inventory"]["serialno_a"] == "UNKNOWN" or host["inventory"]["url_c"] == "UNKNOWN"): host_with_UNKNOWN_fields_inventory.append(host['hostid']) continue host_for_work.append(host) hosts_with_ip_localhost = [] hosts_not_active_with_weekly_latest_data = [] hosts_not_active_without_weekly_latest_data = [] local_timestamp = time.time() for host in host_for_work: for interface in host['interfaces']: if (interface['useip'] == '1' and interface['ip'] == '127.0.0.1'): hosts_with_ip_localhost.append(host['hostid']) if host['inventory']['url_c'] == 'Deleted': if check_if_latest_data_more_than_week_old([host['hostid']],local_timestamp,zapi): hosts_not_active_with_weekly_latest_data.append(host['hostid']) else: hosts_not_active_without_weekly_latest_data.append(host['hostid']) hostids_from_excluded_groups = filter_hostids_by_group(hosts_not_active_without_weekly_latest_data, excluded_groupids,zapi) HostidsForRemove = list(set(hosts_not_active_without_weekly_latest_data) - set(hostids_from_excluded_groups)) softly_remove_hosts(HostidsForRemove,zapi) logger.info(f"Completed remove_deleted_hosts on source: {source}")
def link_correct_template_to_hosts_by_hostgroups(source): logger = get_logger('config') ZabbixConfig = get_zabbix_conf() zapi = zabbix_conn(source, ZabbixConfig) # define file handler for debug log mylogger = logging.getLogger('detailed') mylogger.setLevel(logging.DEBUG) frh = RotatingFileHandler( '/var/log/selfportal/autofill_templates_by_group.log', maxBytes=10485760, backupCount=10) frh.setLevel(logging.DEBUG) formatter = logging.Formatter( '%(asctime)s - %(name)s - %(levelname)s - %(message)s') frh.setFormatter(formatter) mylogger.addHandler(frh) # get group-template dictionary Group_table_mapping = get_host_group_mapping_list_sql( 'Hostgroup_Template_table') for element in range(len(Group_table_mapping)): groupname = Group_table_mapping.iloc[element]['HostGroupName'] templatenames = Group_table_mapping.iloc[element][ 'TemplateName'].split(",") OS_type = Group_table_mapping.iloc[element]['OS_type'].lower() if (templatenames == '') or (OS_type == ''): continue else: for template in templatenames: # get groupids and templateids target_groupid = [ x['groupid'] for x in zapi.hostgroup.get(output=['groupid', 'name'], filter={'name': [groupname]}) ] target_templateids = [ x['templateid'] for x in zapi.template.get(output=['templateid', 'host'], filter={'host': template}) ] if target_templateids == []: logger.info(f'Template {template} in {source} not found!') continue # get current all hosts in target group and filter by OS current_all_in_group = [ x['hostid'] for x in zapi.host.get(output=['hostid', 'name'], groupids=target_groupid) ] if OS_type != "app": current_all_in_group_filtered_by_OS = filter_hostids_by_group( current_all_in_group, dict_of_os_groups[OS_type], zapi) else: current_all_in_group_filtered_by_OS = current_all_in_group # filter excluded group current_all_in_group_filtered_by_OS_with_excluded = filter_hostids_by_group( current_all_in_group_filtered_by_OS, excluded_group, zapi) current_all_in_group_filtered_by_OS_without_excluded = list( set(current_all_in_group_filtered_by_OS) - set(current_all_in_group_filtered_by_OS_with_excluded)) # get current templated hosts current_all_templated_and_filtered_in_group = filter_hostids_by_template( current_all_in_group_filtered_by_OS_without_excluded, target_templateids, zapi) # calculate positive gap (set current all in group - set current templated from group) and link it positive_gap = list( set(current_all_in_group_filtered_by_OS_without_excluded) - set(current_all_templated_and_filtered_in_group)) if positive_gap != []: softly_link_template(positive_gap, target_templateids, zapi, source, mylogger) logger.info( f'Link hosts for {source} in group {groupname} with templatenames: {templatenames}' )
def update_hosts_in_groups(source): logger = get_logger('config') ZabbixConfig = get_zabbix_conf() zapi = zabbix_conn(source, ZabbixConfig) hostgroups = zapi.hostgroup.get( output=["groupid", "name"], selectHosts=["hostid", "name", "description"], sortfield="groupid") allZabbixHosts = zapi.host.get(output=[ "hostid", "host", "name", "flags", "description", "inventory_mode" ], sortfield=["name"], selectInventory=True) zbx_groups_dataframe = get_host_group_mapping_list_sql( ZabbixConfig[source]['group_table']) zbx_groups_dataframe.isnull().values.any() jobProcessCounter = 0 groupsUpdated = 0 groupsNotUpdated = 0 groupsNotInMapping = 0 for hostGroup in hostgroups: hostgroupId = hostGroup["groupid"] host_group_hosts = hostGroup['hosts'] hosts_in_group_static_ids = [] for host in host_group_hosts: if host['description'].find("_static_") != -1: hosts_in_group_static_ids.append({'hostid': host['hostid']}) hostGroupExistsInMapping = 0 jobProcessCounter = jobProcessCounter + 1 zabbixHostsInGroup = [] zabbixHostsInGroup.extend(hosts_in_group_static_ids) df_result = zbx_groups_dataframe[zbx_groups_dataframe['GROUP_ID'] == int(hostgroupId)].values.tolist() if len(df_result) > 0: group = df_result[0] if len(group) == 0: continue if len(group[2]) == 0: continue hostGroupExistsInMapping = 1 for zabbixHost in allZabbixHosts: if zabbixHost['flags'] == "4": continue if zabbixHost['description'].find("_static_") != -1: continue zabbixHostInventory = zabbixHost['inventory'] if type(zabbixHostInventory) == list: continue if zabbixHost['inventory_mode'] == '-1': continue if len(zabbixHostInventory) > 0 and zabbixHostInventory['hardware'] != "" and \ len(set(group[2].split(',')) & set(zabbixHostInventory['hardware'].split(';'))) > 0: if not any(d['hostid'] == zabbixHost['hostid'] for d in zabbixHostsInGroup): zabbixHostsInGroup.append( {'hostid': zabbixHost['hostid']}) if hostGroupExistsInMapping: zapi.hostgroup.massupdate(hosts=zabbixHostsInGroup, groups=[hostgroupId]) if len(host_group_hosts) != len(zabbixHostsInGroup): groupsUpdated = groupsUpdated + 1 else: groupsNotUpdated = groupsNotUpdated + 1 else: groupsNotInMapping = groupsNotInMapping + 1 logger.info( f'Updated hosts in groups for {source} groupsUpdated:{groupsUpdated}, groupsNotUpdated:{groupsNotUpdated}, groupsNotInMapping:{groupsNotInMapping}' )
def update_host_inventory_sm(source): logger = get_logger('config') engine = db_connect() with engine.connect() as conn: res = conn.execute( f"select * from configs.databases where name='ORACLESM'") result = res.fetchall() hpsm_config = result[0] os.environ['NLS_LANG'] = 'American_America.AL32UTF8' con = cx_Oracle.connect('{}/{}@{}:{}/{}'.format(hpsm_config[4], hpsm_config[5], hpsm_config[1], hpsm_config[2], hpsm_config[3])) cur = con.cursor() cur.execute(ci_query_all_servers) ci_list_all_servers_temp = cur.fetchall() ci_list_all_servers = {} for el in ci_list_all_servers_temp: if parse_ip(el[2]) is not None: if parse_ip(el[2]) in ci_list_all_servers: ci_list_all_servers[parse_ip(el[2])].append(el) else: ci_list_all_servers[parse_ip(el[2])] = [el] del ci_list_all_servers_temp cur.execute(ci_query_all_services) ci_list_all_services = cur.fetchall() ci_list_all_services_temp = dict() for el in ci_list_all_services: if el[0] is not None: if el[0] in ci_list_all_services_temp: ci_list_all_services_temp[el[0]].append(el) else: ci_list_all_services_temp[el[0]] = [el] ci_list_all_services = ci_list_all_services_temp del ci_list_all_services_temp cur.execute(ci_relations_query_all) ci_relations_list_all = cur.fetchall() CIRelationsList_new = dict() for CIID, RelatedCIIDs in ci_relations_list_all: if RelatedCIIDs is not None: if RelatedCIIDs in CIRelationsList_new: CIRelationsList_new[RelatedCIIDs].append(CIID) else: CIRelationsList_new[RelatedCIIDs] = [CIID] ci_relations_list_all = CIRelationsList_new del CIRelationsList_new cur.execute(contact_query_all) sm_contact_list_all_temp = cur.fetchall() sm_contact_list_all = {} for el in sm_contact_list_all_temp: if el[0] is not None: if el[0] in sm_contact_list_all: sm_contact_list_all[el[0]].append(el) else: sm_contact_list_all[el[0]] = [el] del sm_contact_list_all_temp cur.execute(ci_ir_group_email) ir_group_email_temp = cur.fetchall() ir_group_email = {} for el in ir_group_email_temp: if el[0] is not None: ir_group_email[el[0]] = el[1] del ir_group_email_temp cur.close() con.close() zapi = zabbix_conn(source) allZabbixHosts = zapi.host.get( output=["hostid", "host", "hostip", "name", "inventory_mode"], sortfield=["name"], selectInterfaces=["interfaceid", "hostid", "ip", "dns"], selectInventory=True) for current_zabbix_host in allZabbixHosts: #print(datetime.datetime.now(),current_zabbix_host['host']) try: process_zabbix_host(current_zabbix_host, zapi, ci_list_all_servers, ci_list_all_services, ci_relations_list_all, sm_contact_list_all, ir_group_email) except BaseException as e: pass logger.info(f"Completed update inventory on source: {source}")
def generate_sm_report(): zapi_prom = zabbix_conn('Zabbix Prom') zapi_testprom = zabbix_conn('Zabbix Test') zapi_test = ZabbixAPI('http://10.116.112.14/zabbix', user='******', password='******') zapi_win = zabbix_conn('Zabbix Prom Win') engine = db_connect() with engine.connect() as conn: res = conn.execute( f"select * from configs.databases where name='ORACLESM'") result = res.fetchall() hpsm_config = result[0] os.environ['NLS_LANG'] = 'American_America.AL32UTF8' con = cx_Oracle.connect('{}/{}@{}:{}/{}'.format( hpsm_config[4], hpsm_config[5], hpsm_config[1], hpsm_config[2], hpsm_config[3])) # @UndefinedVariable cur = con.cursor() ci_query_all_servers = ''' select k.LOGICAL_NAME, k.TPS_NAME, k.DNS_ALIAS_LIST, LISTAGG(k.IP_ADDRESS_LIST, '; ') WITHIN GROUP (ORDER BY k.LOGICAL_NAME) as IP_ALIAS_LIST, k.SUBTYPE, k.TYPE, k.TPS_DNS_NAME, k.TPS_PLATFORM, k.OPERATING_SYSTEM, k.HPC_STATUS, k.ENVIRONMENT, k.SB_ADMIN_GROUP2_NAME from (select t.LOGICAL_NAME, t.TPS_NAME, LISTAGG(t.DNS_ALIAS, '; ') WITHIN GROUP (ORDER BY t.LOGICAL_NAME) as DNS_ALIAS_LIST, t.IP_ADDRESS_LIST, t.SUBTYPE, t.TYPE, t.TPS_DNS_NAME, t.TPS_PLATFORM, t.OPERATING_SYSTEM, t.HPC_STATUS, t.ENVIRONMENT, t.SB_ADMIN_GROUP2_NAME from ( select distinct a.LOGICAL_NAME, a.TPS_NAME, d.DNS_ALIAS, (case a.SUBTYPE when 'Виртуальный' then b.IP_ADDRESSES when 'Логический' then a.IP_ADDRESS else b.IP_ADDRESSES end) as IP_ADDRESS_LIST, a.SUBTYPE, a.TYPE, a.TPS_DNS_NAME, a.TPS_PLATFORM, a.OPERATING_SYSTEM, a.HPC_STATUS, a.ENVIRONMENT, a.SB_ADMIN_GROUP2_NAME from smprimary.device2m1 a, smprimary.device2a2 b, smprimary.device2a5 c, smprimary.device2a4 d WHERE a.LOGICAL_NAME = b.LOGICAL_NAME and a.LOGICAL_NAME = c.LOGICAL_NAME and a.LOGICAL_NAME = d.LOGICAL_NAME and a.TYPE IN ('server') and a.HPC_STATUS = 'Эксплуатируется' ) t GROUP BY t.LOGICAL_NAME, t.TPS_NAME, t.IP_ADDRESS_LIST, t.SUBTYPE, t.TYPE, t.TPS_DNS_NAME, t.TPS_PLATFORM, t.OPERATING_SYSTEM, t.HPC_STATUS, t.ENVIRONMENT, t.SB_ADMIN_GROUP2_NAME ) k GROUP BY k.LOGICAL_NAME, k.TPS_NAME, k.DNS_ALIAS_LIST, k.SUBTYPE, k.TYPE, k.TPS_DNS_NAME, k.TPS_PLATFORM, k.OPERATING_SYSTEM, k.HPC_STATUS, k.ENVIRONMENT, k.SB_ADMIN_GROUP2_NAME ''' cur.execute(ci_query_all_servers) ci_list_all_servers = cur.fetchall() ci_list_all_servers.sort(key=lambda tup: tup[0]) cur.close() con.close() allZabbixHosts_prom = zapi_prom.host.get( output=["hostid", "host", "hostip", "name"], sortfield=["name"], selectInterfaces=["interfaceid", "hostid", "ip", "dns"], selectInventory=True) allZabbixHosts_test = zapi_test.host.get( output=["hostid", "host", "hostip", "name"], sortfield=["name"], selectInterfaces=["interfaceid", "hostid", "ip", "dns"], selectInventory=True) allZabbixHosts_testprom = zapi_testprom.host.get( output=["hostid", "host", "hostip", "name"], sortfield=["name"], selectInterfaces=["interfaceid", "hostid", "ip", "dns"], selectInventory=True) allZabbixHosts_win = zapi_win.host.get( output=["hostid", "host", "hostip", "name"], sortfield=["name"], selectInterfaces=["interfaceid", "hostid", "ip", "dns"], selectInventory=True) allZabbixHosts_prom = zabbix_ip_array(allZabbixHosts_prom) allZabbixHosts_test = zabbix_ip_array(allZabbixHosts_test) allZabbixHosts_testprom = zabbix_ip_array(allZabbixHosts_testprom) allZabbixHosts_win = zabbix_ip_array(allZabbixHosts_win) report_data = [] for [ LOGICAL_NAME, TITLE, DNS_NAME, IP_ADDRESSES, SUBTYPE, TYPE, TPS_DNS_NAME, TPS_PLATFORM, OPERATING_SYSTEM, HPC_STATUS, ENVIRONMENT, SB_ADMIN_GROUP2_NAME ] in ci_list_all_servers: zabbix_host_located_prom = find_host_in_zabbix(allZabbixHosts_prom, IP_ADDRESSES, SUBTYPE) zabbix_host_located_test = find_host_in_zabbix(allZabbixHosts_test, IP_ADDRESSES, SUBTYPE) zabbix_host_located_testprom = find_host_in_zabbix( allZabbixHosts_testprom, IP_ADDRESSES, SUBTYPE) zabbix_host_located_win = find_host_in_zabbix(allZabbixHosts_win, IP_ADDRESSES, SUBTYPE) host_location_result = [] if zabbix_host_located_prom: host_location_result.append("PROM") if zabbix_host_located_test: host_location_result.append("TEST") if zabbix_host_located_testprom: host_location_result.append("TESTPROM") if zabbix_host_located_win: host_location_result.append("WIN") if not host_location_result: host_location_result.append("NO") report_data.append( (prepare(LOGICAL_NAME), prepare(TITLE), prepare(DNS_NAME), prepare(IP_ADDRESSES), prepare(TPS_DNS_NAME), prepare(OPERATING_SYSTEM), prepare(ENVIRONMENT), prepare(SB_ADMIN_GROUP2_NAME), ','.join(host_location_result))) write_data_to_db(report_data)
def create_host_groups_by_ts(ts_list, source, logger): ZabbixConfig = get_zabbix_conf() zapi = zabbix_conn(source, ZabbixConfig) hostGroups = zapi.hostgroup.get(output=["groupid", "name"], sortfield=["name"]) hostGroups = {el['name']: el for el in hostGroups} grp_table_name = ZabbixConfig[source]['group_table'] zbx_groups_dataframe = get_host_group_mapping_list_sql(grp_table_name) zbx_groups_dataframe.isnull().values.any() tsList = ts_list for ts in tsList: newGroupName = tsList[ts] newGroupCI = ts #host_group_mapping_row = [1 for item in zbx_groups_dataframe if newGroupCI in item.split(',')] host_group_mapping_row = zbx_groups_dataframe[ zbx_groups_dataframe['CIs'].str.contains(newGroupCI)] if len(host_group_mapping_row) != 0: continue groupExists = -1 if newGroupName in hostGroups: groupExists = int(hostGroups[newGroupName]['groupid']) else: newGroupId = zapi.hostgroup.create(name=newGroupName) time.sleep(1) if groupExists != -1: if len(zbx_groups_dataframe[zbx_groups_dataframe['GROUP_ID'] == groupExists]) > 0: grp_table = generate_table(grp_table_name) engine = db_connect() with engine.connect() as conn: conn.execute(grp_table.update().values( CIs=grp_table.c.CIs + ',' + newGroupCI).where(grp_table.c.groupid == groupExists)) conn.execute("commit") logger.info( f"Updated row on source: {source}, row:{groupExists},{newGroupName} added CI: {newGroupCI}" ) else: newGroupId = {'groupids': [groupExists]} groupExists = -1 if groupExists == -1: try: grp_table = generate_table(grp_table_name) engine = db_connect() with engine.connect() as conn: conn.execute(grp_table.insert().values( groupid=newGroupId['groupids'][0], groupname=newGroupName, CIs=newGroupCI)) conn.execute("commit") logger.info( f"Added row on source: {source}, row:{newGroupId['groupids'][0]},{newGroupName},{newGroupCI}" ) except BaseException as e: logger.error(f'Error: {str(e)}') logger.info(f"Completed update mapping on source: {source}")