def connection_statistics_report(connection_statistics_df, max_title):
"""Function to create report table out of connection_statistics_df DataFrame"""
translate_dct = dct_from_columns(
'customer_report',
max_title,
'Статистика_подключения_устройств_перевод_eng',
'Статистика_подключения_устройств_перевод_ru',
init_file='san_automation_info.xlsx')
connection_statistics_report_df = connection_statistics_df.copy()
# translate notes
columns = [
column for column in connection_statistics_df.columns
if 'note' in column and connection_statistics_df[column].notna().any()
]
columns.append('Fabric_name')
connection_statistics_report_df = translate_values(
connection_statistics_report_df,
translate_dct,
translate_columns=columns)
# translate column names
connection_statistics_report_df.rename(columns=translate_dct, inplace=True)
# drop empty columns
connection_statistics_report_df.dropna(axis=1, how='all', inplace=True)
return connection_statistics_report_df
def sensor_report(sensor_aggregated_df, data_names, report_columns_usage_dct,
max_title):
"""Function to create report Datafrmae from sensor_aggregated_df
(slice and reorder columns, translate values in columns)"""
# loading values to translate
translate_dct = dct_from_columns('customer_report',
max_title,
'Датчики_перевод_eng',
'Датчики_перевод_ru',
init_file='san_automation_info.xlsx')
sensor_report_df = translate_values(
sensor_aggregated_df,
translate_dct=translate_dct,
translate_columns=['Type', 'Status', 'Vlaue', 'Unit'])
# translate_values(sensor_aggregated_df, translate_dct, max_title)
sensor_report_df, = dataframe_segmentation(sensor_aggregated_df,
data_names[1:],
report_columns_usage_dct,
max_title)
return sensor_report_df
# def translate_values(translated_df, translate_dct, max_title):
# """Function to translate values in corresponding columns"""
# # columns which values need to be translated
# translate_columns = ['Type', 'Status', 'Vlaue', 'Unit']
# # translate values in column if column in DataFrame
# for column in translate_columns:
# if column in translated_df.columns:
# translated_df[column] = translated_df[column].replace(to_replace=translate_dct)
# return translated_df
def isl_statistics_report(isl_statistics_df, report_columns_usage_dct, max_title):
"""Function to create report table out of isl_statistics_df DataFrame"""
# isl_statistics_df_report_df = pd.DataFrame('Фабрика', 'Подсеть', 'Имя шасси', 'Имя коммутатора')
isl_statistics_df_report_df = pd.DataFrame()
if not isl_statistics_df.empty:
chassis_column_usage = report_columns_usage_dct.get('chassis_info_usage')
translate_dct = dct_from_columns('customer_report', max_title, 'Статистика_ISL_перевод_eng',
'Статистика_ISL_перевод_ru', init_file = 'san_automation_info.xlsx')
isl_statistics_df_report_df = isl_statistics_df.copy()
# identify columns to drop and drop columns
drop_columns = ['switchWwn', 'Connected_switchWwn', 'sort_column_1', 'sort_column_2']
if not chassis_column_usage:
drop_columns.append('chassis_name')
drop_columns = [column for column in drop_columns if column in isl_statistics_df.columns]
isl_statistics_df_report_df.drop(columns=drop_columns, inplace=True)
# translate values in columns
translate_columns = [column for column in isl_statistics_df.columns if 'note' in column and isl_statistics_df[column].notna().any()]
translate_columns.extend(['Fabric_name', 'Trunking_lic_both_switches'])
isl_statistics_df_report_df = translate_values(isl_statistics_df_report_df, translate_dct, translate_columns)
# translate column names
isl_statistics_df_report_df.rename(columns=translate_dct, inplace=True)
# drop empty columns
isl_statistics_df_report_df.dropna(axis=1, how='all', inplace=True)
return isl_statistics_df_report_df
def statistics_report(statistics_df, data_name, translate_dct, max_title):
"""Function to convert zonemember_statistics_df, alias_statistics_df and cfg_statistics_df
to report DataFrames (traslate column names and All)"""
# create statitics report DataFrame
statistics_report_df = statistics_df.copy()
if data_name == 'Статистика_зон':
possible_allna_columns = [
'zone_duplicated', 'Target_Initiator_note',
'Effective_cfg_usage_note'
]
statistics_report_df = drop_all_na(statistics_report_df,
possible_allna_columns)
# drop 'Wwnn_to_Wwnp_number_unpacked' column if all values are zero
statistics_report_df = drop_all_identical(
statistics_report_df,
columns_values={'Wwnn_to_Wwnp_number_unpacked': 0},
dropna=True)
# rename values in columns
if data_name == 'Статистика_зон':
translate_columns = [
'Fabric_name', 'Fabric_device_status', 'Target_Initiator_note',
'Target_model_note', 'Effective_cfg_usage_note', 'Pair_zone_note',
'All_devices_multiple_fabric_label_connection',
'Zone_and_Pairzone_names_related', 'Zone_name_device_names_related'
]
else:
translate_columns = ['Fabric_name']
statistics_report_df = \
translate_values(statistics_report_df, translate_dct, translate_columns)
# column titles used to create dictionary to traslate column names
statistic_columns_lst = [data_name + '_eng', data_name + '_ru']
# dictionary used to translate column names
statistic_columns_dct = dct_from_columns('customer_report', max_title, *statistic_columns_lst, \
init_file = 'san_automation_info.xlsx')
# translate columns in fabric_statistics_report and statistics_subtotal_df DataFrames
statistics_report_df.rename(columns=statistic_columns_dct, inplace=True)
return statistics_report_df
def zoning_report_main(zoning_aggregated_df, alias_aggregated_df,
portshow_zoned_aggregated_df, zonemember_statistics_df,
alias_statistics_df, effective_cfg_statistics_df,
data_names, report_columns_usage_dct, max_title):
"""Main function to create zoning report tables"""
# report tables
# loading values to translate
translate_dct = dct_from_columns('customer_report',
max_title,
'Зонирование_перевод_eng',
'Зонирование_перевод_ru',
init_file='san_automation_info.xlsx')
zoning_report_df = create_report(zoning_aggregated_df, data_names[6:7],
translate_dct, report_columns_usage_dct,
max_title)
alias_report_df = create_report(alias_aggregated_df, data_names[7:8],
translate_dct, report_columns_usage_dct,
max_title)
zoning_compare_report_df = compare_zone_config(zoning_report_df)
unzoned_device_report_df, no_alias_device_report_df = unzoned_device_report(
portshow_zoned_aggregated_df, data_names[9:11],
report_columns_usage_dct, max_title)
zoning_absent_device_report_df = absent_device(zoning_aggregated_df,
data_names[11],
translate_dct,
report_columns_usage_dct,
max_title)
# zonemember_statistics_report_df = zonemember_statistics_report(zonemember_statistics_df, translate_dct, max_title) # TO_REMOVE
zonemember_statistics_report_df = statistics_report(
zonemember_statistics_df, data_names[12], translate_dct, max_title)
alias_statistics_report_df = statistics_report(alias_statistics_df,
data_names[13],
translate_dct, max_title)
effective_cfg_statistics_report_df = statistics_report(
effective_cfg_statistics_df, data_names[14], translate_dct, max_title)
# effective_cfg_statistics_report_df = cfg_statistics_report(effective_cfg_statistics_df, translate_dct, report_columns_usage_dct, max_title) # TO_REMOVE
return zoning_report_df, alias_report_df, zoning_compare_report_df, unzoned_device_report_df, \
no_alias_device_report_df, zoning_absent_device_report_df, zonemember_statistics_report_df, \
alias_statistics_report_df, effective_cfg_statistics_report_df
def statistics_report(statistics_df, chassis_column_usage, max_title):
"""Function to create report table out of statistics_df DataFrame"""
# create statitics report DataFrame
statistics_report_df = statistics_df.copy()
# drop columns 'switch_index', 'switchWwn', 'N:E_int', 'N:E_bw_int'
statistics_report_df.drop(
columns=['switch_index', 'switchWwn', 'N:E_int', 'N:E_bw_int'],
inplace=True)
# drop Not Licensed ports column if there are no any
statistics_report_df = drop_all_identical(
statistics_report_df, columns_values={'Not_licensed': 0}, dropna=True)
# drop Licensed column if all ports are licensed
statistics_report_df = drop_equal_columns(statistics_report_df,
columns_pairs=[
('Total_ports_number',
'Licensed')
])
# drop column 'chassis_name' if it is not required
if not chassis_column_usage:
statistics_report_df.drop(columns=['chassis_name'], inplace=True)
# column titles used to create dictionary to traslate column names
statistic_columns_lst = ['Статистика_фабрики_eng', 'Статистика_фабрики_ru']
# dictionary used to translate column names
statistic_columns_names_dct = dct_from_columns('customer_report', max_title, *statistic_columns_lst, \
init_file = 'san_automation_info.xlsx')
# translate columns in fabric_statistics_report and statistics_subtotal_df DataFrames
statistics_report_df.rename(columns=statistic_columns_names_dct,
inplace=True)
# get summary statistics for each fabric
statistics_total_report_df = statistics_total(statistics_df,
statistic_columns_names_dct)
# add summary fabric level statistics to switch level statistics
statistics_report_df = pd.concat(
[statistics_report_df, statistics_total_report_df], ignore_index=True)
statistics_report_df.sort_values(
by=['Фабрика', 'Подсеть', 'Имя коммутатора'], inplace=True)
return statistics_report_df
def dataframe_segmentation(dataframe_to_segment_df, dataframes_to_create_lst,
report_columns_usage_dct, max_title):
"""Function to split aggregated table to required DataFrames
As parameters function get DataFrame to be partitioned and
list of allocated DataFrames names. Returns list of segmented DataFrames
"""
# sheet name with customer report columns
customer_report_columns_sheet = 'customer_report'
# construct columns titles from data_names to use in dct_from_columns function
if isinstance(dataframes_to_create_lst, str):
dataframes_to_create_lst = [dataframes_to_create_lst]
tables_names_lst = [[
data_name.rstrip('_report') + '_eng',
data_name.rstrip('_report') + '_ru'
] for data_name in dataframes_to_create_lst]
chassis_column_usage = report_columns_usage_dct['chassis_info_usage']
fabric_name_usage = report_columns_usage_dct['fabric_name_usage']
group_name_usage = report_columns_usage_dct.get('group_name_usage')
# dictionary used to rename DataFrame english columns names to russian
data_columns_names_dct = {}
# for each data element from data_names list import english and russian columns title
# data_name is key and two lists with columns names are values for data_columns_names_dct
for dataframe_name, eng_ru_columns in zip(dataframes_to_create_lst,
tables_names_lst):
data_columns_names_dct[dataframe_name] = \
dct_from_columns(customer_report_columns_sheet, max_title, *eng_ru_columns, init_file = 'san_automation_info.xlsx')
# construct english columns titles from tables_names_lst to use in columns_import function
tables_names_eng_lst = [
table_name_lst[0] for table_name_lst in tables_names_lst
]
# dictionary to extract required columns from aggregated DataFrame
data_columns_names_eng_dct = {}
# for each data element from data_names list import english columns title to slice main DataFrame
for dataframe_name, df_eng_column in zip(dataframes_to_create_lst,
tables_names_eng_lst):
# dataframe_name is key and list with columns names is value for data_columns_names_eng_dct
data_columns_names_eng_dct[dataframe_name] = \
columns_import(customer_report_columns_sheet, max_title, df_eng_column, init_file = 'san_automation_info.xlsx', display_status=False)
# if no need to use chassis information in tables
if not chassis_column_usage:
if 'chassis_name' in data_columns_names_eng_dct[dataframe_name]:
data_columns_names_eng_dct[dataframe_name].remove(
'chassis_name')
if 'chassis_wwn' in data_columns_names_eng_dct[dataframe_name]:
data_columns_names_eng_dct[dataframe_name].remove(
'chassis_wwn')
# if there is only one Fabric no need to use Fabric name
if not fabric_name_usage:
for column in dataframe_to_segment_df.columns:
if 'Fabric_name' in column and column in data_columns_names_eng_dct[
dataframe_name]:
data_columns_names_eng_dct[dataframe_name].remove(column)
# if device names correction applied then no need to use alias group name column
if not group_name_usage:
if 'Group_Name' in data_columns_names_eng_dct[dataframe_name]:
data_columns_names_eng_dct[dataframe_name].remove('Group_Name')
# list with partitioned DataFrames
segmented_dataframes_lst = []
for dataframe_name in dataframes_to_create_lst:
# df_columns_names_eng_lst = data_columns_names_eng_dct[dataframe_name]
columns = dataframe_to_segment_df.columns.to_list()
df_columns_names_eng_lst = [
column for column in data_columns_names_eng_dct[dataframe_name]
if column in columns
]
# get required columns from aggregated DataFrame
# sliced_dataframe = dataframe_to_segment_df[data_columns_names_eng_dct[dataframe_name]].copy() # remove
sliced_dataframe = dataframe_to_segment_df.reindex(
columns=df_columns_names_eng_lst).copy()
# translate columns to russian
sliced_dataframe.rename(columns=data_columns_names_dct[dataframe_name],
inplace=True)
# add partitioned DataFrame to list
segmented_dataframes_lst.append(sliced_dataframe)
return segmented_dataframes_lst
def statistics_report(statistics_df, data_name, translate_dct, max_title):
"""Function to convert zonemember_statistics_df, alias_statistics_df and cfg_statistics_df
to report DataFrames (traslate column names and All)"""
# create statitics report DataFrame
statistics_report_df = statistics_df.copy()
if data_name == 'Статистика_зон':
possible_allna_columns = [
'zone_duplicated', 'Target_Initiator_note',
'Effective_cfg_usage_note'
]
for column in possible_allna_columns:
if statistics_df[column].isna().all():
statistics_df.drop(columns=[column], inplace=True)
# drop 'Wwnn_to_Wwnp_number_unpacked' column if all values are zero
if (statistics_df['Wwnn_to_Wwnp_number_unpacked'].dropna() == 0).all():
statistics_df.drop(columns=['Wwnn_to_Wwnp_number_unpacked'],
inplace=True)
# rename values in columns
if data_name == 'Статистика_зон':
translate_columns = [
'Fabric_name', 'Fabric_device_status', 'Target_Initiator_note',
'Target_model_note', 'Effective_cfg_usage_note'
]
else:
translate_columns = ['Fabric_name']
statistics_report_df = \
translate_values(statistics_report_df, translate_dct, translate_columns)
# column titles used to create dictionary to traslate column names
statistic_columns_lst = [data_name + '_eng', data_name + '_ru']
# dictionary used to translate column names
statistic_columns_dct = dct_from_columns('customer_report', max_title, *statistic_columns_lst, \
init_file = 'san_automation_info.xlsx')
# translate columns in fabric_statistics_report and statistics_subtotal_df DataFrames
statistics_report_df.rename(columns=statistic_columns_dct, inplace=True)
return statistics_report_df
# TO_REMOVE created statistics_report function
# def zonemember_statistics_report(zonemember_statistics_df, translate_dct, max_title):
# """Function to create report table out of statistics_df DataFrame"""
# # create statitics report DataFrame
# zonemember_statistics_report_df = zonemember_statistics_df.copy()
# possible_allna_columns = ['zone_duplicated', 'Target_Initiator_note', 'Effective_cfg_usage_note']
# for column in possible_allna_columns:
# if zonemember_statistics_report_df[column].isna().all():
# zonemember_statistics_report_df.drop(columns=[column], inplace=True)
# # drop 'Wwnn_to_Wwnp_number_unpacked' column if all values are zero
# if (zonemember_statistics_report_df['Wwnn_to_Wwnp_number_unpacked'].dropna() == 0).all():
# zonemember_statistics_report_df.drop(columns=['Wwnn_to_Wwnp_number_unpacked'], inplace=True)
# # rename values in columns
# translate_columns = ['Fabric_name', 'Fabric_device_status', 'Target_Initiator_note', 'Target_model_note', 'Effective_cfg_usage_note']
# zonemember_statistics_report_df = translate_values(zonemember_statistics_report_df, translate_dct, translate_columns)
# # column titles used to create dictionary to traslate column names
# statistic_columns_lst = ['Статистика_зон_eng', 'Статистика_зон_ru']
# # dictionary used to translate column names
# statistic_columns_dct = dct_from_columns('customer_report', max_title, *statistic_columns_lst, \
# init_file = 'san_automation_info.xlsx')
# # translate columns in fabric_statistics_report and statistics_subtotal_df DataFrames
# zonemember_statistics_report_df.rename(columns = statistic_columns_dct, inplace = True)
# # statistics_report_df.sort_values(by=['Фабрика', 'Подсеть', 'Имя коммутатора'], inplace=True)
# return zonemember_statistics_report_df
# TO_REMOVE created statistics_report function
# def cfg_statistics_report(effective_cfg_statistics_df, translate_dct, report_columns_usage_dct, max_title):
# # create statitics report DataFrame
# effective_cfg_statistics_report_df = effective_cfg_statistics_df.copy()
# # # drop column 'chassis_name' if it is not required
# # if not fabric_name_usage:
# # zonemember_statistics_report_df.drop(columns = ['Fabric_name'], inplace=True)
# # rename values in columns
# translate_columns = ['Fabric_name']
# effective_cfg_statistics_report_df = \
# translate_values(effective_cfg_statistics_report_df, translate_dct, translate_columns)
# # column titles used to create dictionary to traslate column names
# statistic_columns_lst = ['Статистика_конфигурации_eng', 'Статистика_конфигурации_ru']
# # dictionary used to translate column names
# statistic_columns_dct = dct_from_columns('customer_report', max_title, *statistic_columns_lst, \
# init_file = 'san_automation_info.xlsx')
# # translate columns in fabric_statistics_report and statistics_subtotal_df DataFrames
# effective_cfg_statistics_report_df.rename(columns = statistic_columns_dct, inplace = True)
# return effective_cfg_statistics_report_df
# get report entry values from report file
customer_name, project_folder, ssave_folder, blade_folder, synergy_folder, local_3par_folder = report_entry_values(
start_max_title)
max_title = find_max_title(ssave_folder)
print('\n\n')
info = f'ASSESSMENT FOR SAN SERVICE. CUSTOMER {customer_name}.'
print(info.center(max_title + 80, '.'))
print('\n')
# dictionary with report steps as keys. each keys has two values
# first value shows if it is required to export extracted data to excel table
# second value shows if it is required to initiate force data extraction if data have been already extracted
report_steps_dct = dct_from_columns('service_tables', max_title, \
'keys', 'export_to_excel', 'force_extract', 'report_type', 'step_info', 'description', \
init_file = 'report_info.xlsx', display_status=False)
def main():
parsed_lst = config_preparation(customer_name, project_folder,
ssave_folder, max_title)
chassis_params_fabric_lst, chassis_params_df, maps_params_df = chassis_maps_params(
parsed_lst)
switch_params_lst, switch_params_df, switchshow_ports_df = switches_params(
chassis_params_fabric_lst)
fabricshow_df, ag_principal_df = switches_in_fabric(switch_params_lst)
portshow_df, sfpshow_df, portcfgshow_df = ports_info_group(
chassis_params_fabric_lst, switch_params_lst)
fdmi_df, nsshow_df, nscamshow_df = connected_devices(switch_params_lst)