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}")