BI для оценки полезности BI: огранка логов в АЛРОСА

Для начала собираем информацию по дашбордам через api "admin/api/dashboards" и формируем dataframe с ID дашбордов.

query_headers = {
        "X-API-VERSION": "3.7",
        "Content-Type": 'application/json',
        "Authorization": "Bearer " + token['access_token']
}
dashboards_requests = requests.get(url=ССЫЛКА НА СТЕНД/ + admin/api/dashboards ,headers=query_headers).text
dashboards = json.loads(dashboards_requests)
for one_dash in dashboards:
    dash_name = one_dash["Name"]
    dash_id = one_dash["_id"]
    try:
        if type(one_dash["Roles"]) != dict:
            if one_dash["Roles"]:
                for one_role in one_dash["Roles"]:
                    dash_role_df_dict['Наименование дашборда'].append(dash_name)
                    dash_role_df_dict['Id дашборда'].append(dash_id)
                    dash_role_df_dict['Роль'].append(one_role['Name'])
            else:
                dash_role_df_dict['Наименование дашборда'].append(dash_name)
                dash_role_df_dict['Id дашборда'].append(dash_id)
                dash_role_df_dict['Роль'].append(None)
        else:
            dash_role_df_dict['Наименование дашборда'].append(dash_name)
            dash_role_df_dict['Id дашборда'].append(dash_id)
            dash_role_df_dict['Роль'].append(one_role['Roles']['Name'])
dash_role_df = pd.DataFrame(dash_role_df_dict)

Собираем информацию о пользователях Visiology через api "admin/api/users", на выходе получаем массив dataframe:

query_headers = {
        'content-type': 'application/x-www-form-urlencoded; charset=UTF-8',
        "authorization": "Bearer " + token['access_token']
}
visiology_users_requests = requests.post(url= ССЫЛКА НА СТЕНД/ + admin/api/users ,headers=query_headers, data=payload).text
visiology_users = json.loads(visiology_users_requests)
for one_user in visiology_users['data']:
    try:
        if 'UserName' in one_user:
            user_name_login = one_user['UserName']
            if user_name_login in (None, ''):
                user_name_login =None
        else:
            user_name_login = None

        if 'GivenName' in one_user:
            user_name_name = one_user['GivenName']
            if user_name_name in (None, ''):
                user_name_name = None
        else:
            user_name_name = None

        if 'FamilyName' in one_user:
            user_name_family = one_user['FamilyName']
            if user_name_family in (None, ''):
                user_name_family = None
        else:
            user_name_family = None

        if 'MiddleName' in one_user:
            user_name_otchestvo = one_user['MiddleName']
            if user_name_otchestvo in (None, ''):
                user_name_otchestvo = None
        else:
            user_name_otchestvo = None  

        if 'Email' in one_user:
            user_name_email = one_user['Email']
            if user_name_email in (None, ''):
                user_name_email = None
        else:
            user_name_email = None

        if 'IsInfrastructure' in one_user:
            user_name_tehnich_uchetka = one_user['IsInfrastructure']
            if user_name_tehnich_uchetka in (None, ''):
                user_name_tehnich_uchetka = False
        else:
            user_name_tehnich_uchetka = False

        if 'Created' in one_user:
            user_name_created = one_user['Created']
            if user_name_created in (None, ''):
                user_name_created = '1900-01-01'
            else:
                user_name_created = user_name_created[:10]
        else:
            user_name_created = '1900-01-01'

        if 'Created' in one_user:
            user_name_last_login = one_user['LastLogin']
            if user_name_last_login in (None, ''):
                user_name_last_login = '1900-01-01'
            else:
                user_name_last_login = user_name_last_login[:10]
        else:
            user_name_last_login = '1900-01-01'

        visiology_users_df_dict['Имя пользователя'].append(user_name_login)
        visiology_users_df_dict['Фамилия'].append(user_name_family)
        visiology_users_df_dict['Имя'].append(user_name_name)
        visiology_users_df_dict['Отчество'].append(user_name_otchestvo)
        visiology_users_df_dict['Email'].append(user_name_email)
        visiology_users_df_dict['Системный пользователь'].append(user_name_tehnich_uchetka)
        visiology_users_df_dict['Дата регистрации'].append(datetime.datetime.fromisoformat(user_name_created))
        visiology_users_df_dict['Дата последнего входа'].append(datetime.datetime.fromisoformat(user_name_last_login))
visiology_users_df = pd.DataFrame(visiology_users_df_dict)
[Имя пользователя], [Фамилия], [Имя], [Отчество], [Email]

Из api "/admin/api/userInfo" получаем ID пользователя и join-им с предыдущим dataframe по имени пользователя.

usernames = visiology_users_df['Имя пользователя'].unique()
for username in usernames:
    headers = {
        "Content-Type": 'application/json',
        "Authorization": "Bearer " + token['access_token']
    }
    payload = {
        "UserName": username
    }
    req = requests.post(main_url+'/admin/api/userInfo',headers=headers, data=json.dumps(payload))
    visi_user_sub_df_dict['sub'].append(json.loads(req.text)['_id'])
    visi_user_sub_df_dict['Имя пользователя'].append(username)
visi_user_sub_df = pd.DataFrame(visi_user_sub_df_dict)

visiology_users_df_to_output = pd.merge(visiology_users_df, visi_user_sub_df, how='left', left_on='Имя пользователя', right_on='Имя пользователя')

И в итоге получаем dataframe:

[Имя пользователя], [Фамилия], [Имя], [Отчество], [Email], [sub]*

Sub – id пользователя

Используя dataframe из пункта 1, проходимся по нему циклом и делаем запрос на "loki/api/v1/query_range", где смотрим количество входов на каждый дашборд

# Время по GMT
now = datetime.date.today() 
now = datetime.datetime(now.year,now.month,now.day)
yesterday = now - datetime.timedelta(days=1)# за вчера смотрим  
date_start = int(yesterday.strftime('%s'))
date_end = int((yesterday + datetime.timedelta(hours=24)).strftime('%s'))
for dash_guid_one in dashs_guids:
    if dash_guid_one not in (None, '0', np.nan, 'nan', 'None'):
        time.sleep(14)
        print(f'Дашборд - {dash_guid_one}')
        time_for_dash = time.time()
        params =  {
            'query': '{stream="stdout"} |= "'+dash_guid_one+'" |= " Guid: " |= "ScriptSourceSettings" |= "UserSub"',
            'start': date_start*1000000000,
            'end': date_end*1000000000,
            'limit': 5000
        }
        try:
            resp = json.loads(requests.get(f'{ ССЫЛКА НА СТЕНД/ }loki/api/v1/query_range', params=params).text)

            if resp['data']['result']:
                print('Есть входы')
                length_of_vh = len(resp['data']['result'][0]['values'])
                print(f'Количество входов - {length_of_vh}')
                for vhod in range(length_of_vh):
                    log_if_exist = resp['data']['result'][0]['values'][vhod][1]
                    UserSub_indexes = [m.start() for m in re.finditer('\", UserSub', log_if_exist)]
                    ActionId_indexes = [m.start() for m in re.finditer('\", ActionId', log_if_exist)]
                    times_indexes = [m.start() for m in re.finditer(',"attrs":{"component":"dashboard-service"},"time":"', log_if_exist)]

                    for one_entity in range(len(UserSub_indexes)):
                        one_usersub = log_if_exist[UserSub_indexes[one_entity]+14:ActionId_indexes[one_entity]-1]
                        one_time = datetime.datetime.fromisoformat(log_if_exist[times_indexes[one_entity]+51:times_indexes[one_entity]+70])
                        dash_views_df_dict["Id дашборда"].append(dash_guid_one)
                        dash_views_df_dict["sub"].append(one_usersub)
                        dash_views_df_dict["Время посещения"].append(one_time)
dash_views_df = pd.DataFrame(dash_views_df_dict)

Итогом всех процессов, на выходе получаем dataframe cо следующими полями:

 ["Id дашборда"], ["sub"], ["Время посещения"].

Делаем запрос к AD, и на выходе также получаем dataframe:

['Email'], ['Группа']

JOIN-им этот dataframe по ['Email'] с предыдущим dataframe [Имя пользователя], [Фамилия], [Имя], [Отчество], [Email], [sub]*

Обращаемся по api к "admin/api/webAppDashboards"

query_headers = {
        "X-API-VERSION": "3.7",
        "Content-Type": 'application/json',
        "Authorization": "Bearer " + token['access_token']
}
razdel_dash_requests = requests.get(url={ ССЫЛКА НА СТЕНД/ + admin/api/webAppDashboards, headers=query_headers).text
razdel_dash = json.loads(razdel_dash_requests)
for one_razdel in razdel_dash:
    razdel_name = one_razdel['name']
    razdel_id = one_razdel['id']
    try:
        if type(one_razdel['dashboardsList']) != dict:
            for one_dash in one_razdel['dashboardsList']:
                dash_name = one_dash['Name']
                dash_id = one_dash['_id']
                dash_url = main_url[:-1] + one_dash['url']
                razdel_dash_df_dict["Наименование раздела"].append(razdel_name)
                razdel_dash_df_dict["Ссылка на раздел"].append(main_url + 'dashboardPreviews?sectionId=' + razdel_id)
                razdel_dash_df_dict["Наименование дашборда"].append(dash_name)
                razdel_dash_df_dict["Id дашборда"].append(dash_id)
                razdel_dash_df_dict["Ссылка на дашборд"].append(dash_url)
        else:
            dash_name = one_razdel['dashboardsList']['Name']
            dash_id = one_razdel['dashboardsList']['_id']
            dash_url = main_url[:-1] + one_razdel['dashboardsList']['url']
            razdel_dash_df_dict["Наименование раздела"].append(razdel_name)
            razdel_dash_df_dict["Ссылка на раздел"].append(main_url + 'dashboardPreviews?sectionId=' + razdel_id)
            razdel_dash_df_dict["Наименование дашборда"].append(dash_name)
            razdel_dash_df_dict["Id дашборда"].append(dash_id)
            razdel_dash_df_dict["Ссылка на дашборд"].append(dash_url)
razdel_dash_df = pd.DataFrame(razdel_dash_df_dict)

формируем dataframe ["Наименование раздела", "Ссылка на раздел", "Наименование дашборда", "Id дашборда", "Ссылка на дашборд"]

На стороне Visiology JOIN-им все наши массивы по ['sub'] и ['Id дашборда']

select  
posesh."Id дашборда"
,part_2."Наименование дашборда"
,User2."Имя пользователя"
,User2."Фамилия" +' '+ User2."Имя" +' '+ User2."Отчество" as "ФИО"
,LOWER(User2."Email")
,User2."Системный пользователь"
,User2."Дата регистрации"
,User2."Дата последнего входа"
,posesh."sub"
,User2."Группа"
,User2."Уволен"
,User2."Содержится в AD"
,1 as "1ka"
,part_1."Наименование раздела"
,CAST(posesh."Время посещения" as date) as "Время посещения по дням"

,part_1."i"
,posesh."Время посещения"
,part_1."Наименование раздела"+ '/' +part_2."Наименование дашборда" as "Раздел + Даш"
,CAST(DATEPART(week,posesh."Время посещения") as char(20)) + '/'+CAST(DATEPART(year,posesh."Время посещения") as char(20))"Неделя+Год"
,User2."Фамилия" +' '+ SUBSTRING (User2."Имя",1,1) +'.'+ SUBSTRING (User2."Отчество",1,1)+'.' as "ФИО короткое"
,CAST(User2."Площадка" as char) as "Площадка"
,LOWER(User2."Email") as "Email  OpenID Для JOIN БК"
,LOWER(User2."Email") as "Email  OpenID join vip"
,1 as "1ka Изм"

from [Visiology].[dbo].[_Python_Visiology_views_only] as posesh
LEFT JOIN [Visiology].[dbo].[_Python_Visiology_Users_Visiology_AD] as User2
on posesh."sub"=User2."sub"
LEFT JOIN (select "Id дашборда", "Наименование дашборда" from   [Visiology].[dbo].[_Python_visiologyParser_TEMP_MAIN_PART_2] 
group by 
"Id дашборда", 
"Наименование дашборда"
) as part_2
on posesh."Id дашборда"=part_2."Id дашборда"

LEFT JOIN [Visiology].[dbo].[_Python_visiologyParser_TEMP_MAIN_PART_1] as part_1
on posesh."Id дашборда"=part_1."Id дашборда"