• /
  • /

Автоматизация выгрузки тикетов из Jira
и визуализации отчетов в PowerBI

Рассказываем про удобный менеджмент тикетов Jira и визуализацию данных по ним — и как это можно автоматизировать. Статья пригодится не только DevOps специалистам, но и продакт менеджерам, HR и дата-аналитикам. В общем, упрощаем подход аплодинга и постройки визуализации, чтобы сэкономить время и силы.
Если вам приходилось строить отчеты по работе отделов компании, то вы знаете, что делать это вручную каждую неделю или месяц достаточно долго и требует много внимания к деталям. Человеческий фактор может всё испортить и заставит делать заново.

Тут на помощь приходит автоматизация с помощью обычного скриптинга и интеграция с Jira API и PowerBI. Этот подход упростит процесс подготовки отчетов и визуализации для любого бизнеса, который работает с любой отчетностью. Сможете готовить отчеты быстро, легко и без потерь.

Что используем

Jira

Jira — это система для управления проектами и отслеживания задач. Первоначально она предназначена для отслеживания ошибок и проблем в разработке, но со временем функционал расширился и Jira стала универсальной платформой для управления различными типами проектов.

Именно в Jira будут создаваться наши тикеты с нужными данными внутри.
PowerBI

PowerBI — это платформа для аналитики, которая позволяет собирать, обрабатывать и визуализировать данные, предоставляя пользователям инструменты для создания отчетов и интерактивных панелей мониторинга. Это наш основной инструмент визуализации отчетов.
Python

Для скриптинга по выгрузке данных с Jira будем использовать Python.

Настройка автоматизации отчетов на практике

Допустим, у нас есть инженерная команда, которая использует Jira для управления инцидентами. В тикетах SRE инженеры учитывают все детали: время начала, время выполнения, системы, которые затронул инцидент и так далее. Кроме этого, в тикетах указывают потраченное время на фикс инцидента — основное для менеджмента, чтобы руководство могло понять эффективность сотрудников, критичность инцидентов и их количество.


Архитектура будет выглядеть примерно так:

Идея здесь следующая:


  • У нас есть Python, откуда будет каждый час запускаться скрипт, и главный сервер с PostgreSQL, куда записываются данные.
  • Jira, от которой через скрипт исходят все API-запросы для сбора данных.
  • PowerBI клиент с настроенными ODBC коннекторами с PostgreSQL сервером для получения данных и визуализации.

Разделим наш кейс на четыре основные части:


Всё, что требуется из Jira

В любом проекте Jira, который вы хотите взять за основу данных по отчетам, нужно заполнить нужные поля в тикетах.


Например, тикет в Jira, где есть общее описание проблемы / инцидента / задачи с разными данными (отмечены красными линиями):

Alert Time: время регистрации инцидента

Resolve Time: время закрытия

Priority: приоритет задачи

Assignee: ответственный инженер

Components: сервис, который участвует проблеме

Escalation: была ли эскалация другим инженерам

Time Spent SRE (h): потраченное на решение проблемы время

Rate: ставка


Кроме тикетов с данными, в Jira нам потребуется токен:


  1. Войдите в свою учетную запись через веб-интерфейс
  2. Найдите настройки аккаунта, доступно через меню пользователя в правом верхнем углу
  3. Затем переходим вкладку Security, выбираем Create and manage API tokens, нажимаем Create API token

Сохраните API-токен, он нам пригодиться при написании скрипта.

Подготовка основного сервера: установка PostgreSQL и Python

Для начала устанавливаем PostgreSQL Server и его инструменты. Подключаемся к серверу 192.168.1.100 и запускаем следующие команды:

#Обновление пакетов
sudo apt update
#Установка PostgreSQL
sudo apt install postgresql postgresql-contrib -y
#Установка инструментов PostgreSQL (psql-client), в том числе pg_dump, который нам потребуется для бэкапов.
sudo apt install postgresql-client -y
#Вывод этой команды покажет, запущен ли процесс PostgreSQL на сервере
sudo systemctl status postgresql
#Вывод этой команды покажет версию установленного PostgreSQL
psql --version


#После установки PostgreSQL создается юзер postgres и можно подключиться к базе локально. Вы можете получить доступ к командной строке PostgreSQL, используя этого пользователя:
sudo -i -u postgres
psql
postgres=#

Теперь на этом же сервере установим Python, который будет автоматизировать процесс выгрузки данных из Jira и их последующей обработки:

#Начнем с обновления пакетов на сервере
sudo apt update
#Установим последнюю стабильную версию Python и необходимые инструменты
sudo apt install python3 python3-pip -y
#Чтобы убедиться, что Python установлен правильно, проверьте его версию
python3 --version
#Для работы со скриптами, которые будут взаимодействовать с Jira API и PostgreSQL, потребуется установить дополнительные библиотеки. Используем pip для установки необходимых пакетов:
pip3 install requests jira pandas xlsxwriter python-dotenv psycopg2-binary sqlalchemy

Это обобщенная инструкция по установке базы данных и Python. Если вам требуется поставить свои конфигурации, user credentials и так далее, или вы используете другую SQL систему, то вы вольны сделать это.

Написание скрипта и его автозапуск

Теперь на нашем сервере, где уже установлены PostgreSQL и Python, создадим новую директорию со скриптом и его переменными:

mkdir jira_script_folder
cd jira_script_folder
touch config.env

Открываем файл config.env и добавляем следующее содержимое

/* Env file for credentials replace x with relevanat things (DB-DB_port is meant to be not enclosed)*/

DB_host="x"  
DB_port= x
DB_user="x"
DB_password="x"
DB_name="x"

jiraToken = "x" #input your credentials here 
server = "x"
accountName = "x"

Теперь этот файл предназначен для указания базы credentials из основного сервера и Jira API токен, который мы создали в начале. Как только ввели свои секреты и остальное, сохраняем и закрываем файл.


В этой же директории создаем файл jiraScript.py:

touch jiraScript.py

Открываем этот файл и добавляем следующее содержимое:

# Code for installing dependencies if not found
def install_dependencies():
    required_modules = ['requests', 'jira', 'pandas', 'xlsxwriter', 'datetime', 'subprocess', 'sys', 'base64', 'os', 'sqlalchemy']

    for module in required_modules:
        try:
            # Check if the required module is already installed
            __import__(module)
        except ImportError:
            print(f"{module} not found. Installing...")
            
            # Use subprocess to run the pip install command
            subprocess.check_call([sys.executable, '-m', 'pip', 'install', module])

            print(f"Installation of {module} successful.")

# Call the function to check and install dependencies
install_dependencies()

import subprocess
import sys
import requests
import base64
from jira.client import Jira
import pandas as pd
from datetime import datetime, timedelta
import os
from dotenv import load_dotenv
import psycopg2

from datetime import datetime
from sqlalchemy import create_engine

script_dir = os.path.dirname(__file__)
dotenv_path = os.path.join(script_dir, 'config.env')
load_dotenv(dotenv_path=dotenv_path)
# Creds from config.env
host = os.getenv('DB_host')
port = os.getenv('DB_port')
user = os.getenv('DB_user')
password = os.getenv('DB_password')
dbname = os.getenv('DB_name')

server = os.getenv('server')
accountName = os.getenv('accountName')  
jiraToken = os.getenv('jiraToken')

end_date = datetime.utcnow().replace(day=1, hour=0, minute=0, second=0, microsecond=0) - timedelta(days=1)
start_date = end_date.replace(day=1)

# Format the dates as required by the Opsgenie API
start_date_str = start_date.strftime('%Y-%m-%dT00:00:00.000Z')  # Alter here to to = "2024-03-01T00:00:00.000Z" this format to limit your date
current_date = datetime.now()
first_day_of_current_month = current_date.replace(day=1)
last_day_of_previous_month = first_day_of_current_month - timedelta(days=1)
end_date_str = end_date.strftime('%Y-%m-%dT23:59:59.999Z')
end_date_str_Jira = current_date.strftime('%Y-%m-%d 23:59')  # "2024-05-01 23:59"#last_day_of_previous_month.strftime('%Y-%m-%d 23:59')
start_date_str_Jira = start_date.strftime('%Y-%m-%d 00:00')  # Alter here to to = "2024-03-01 00:00" this format to limit your date
jql = f"project = ITSM and \"acknowledgment time[time stamp]\" >= \"{start_date_str_Jira}\" and \"acknowledgment time[time stamp]\" <= \"{end_date_str_Jira}\" and \"rate[dropdown]\" in (1.5, 2, 2.5)  AND \"Time Spent SRE (h)[Short text]\" IS NOT EMPTY"  # Jira query 
regex_pattern_components = r"name='([^']+)'"
incident = "Incident"
cred = "Basic " + base64.b64encode(str.encode(accountName + ":" + jiraToken)).decode("utf-8")  # here insert your own account and token from Jira
headers = {
   "Accept": "application/json",
   "Content-Type": "application/json",
   "Authorization": cred
}

# Connect to Jira
jira = Jira(options={'server': server}, basic_auth=(accountName, jiraToken))
jira_issues = jira.search_issues(jql, maxResults=False)

# Convert Jira issues to pandas DataFrame
issues_list = []
for issue in jira_issues:
    component_value = str(issue.fields.components)
    start_index = component_value.find("'") + 1
    end_index = component_value.find("'", start_index)
    component_name = component_value[start_index:end_index]
    d = {
        'id': issue.id,
        'key': issue.key,
        'self': issue.self,
        'assignee': str(issue.fields.customfield_12249.displayName),
        'Begin_Date1': pd.to_datetime(issue.fields.customfield_12254).tz_convert('Europe/Vilnius').strftime('%Y-%m-%d %H:%M'),
        'timespent': float(issue.fields.customfield_12260),
        'summary': str(issue.fields.summary),
        'rate': str(issue.fields.customfield_12170),
        'Working hours': str(issue.fields.customfield_12171),
        'Escalation': str(issue.fields.customfield_12168),
        'Post Mortem': str(issue.fields.customfield_12167),
        'components': component_name,
    }
    issues_list.append(d)
issues = pd.DataFrame(issues_list)

engine = create_engine(f'postgresql+psycopg2://{user}:{password}@{host}:{port}/{dbname}')
# Load existing issues
existing_issues_df = pd.read_sql("SELECT * FROM public.issues", engine)
# Drop duplicates based on 'key' column
existing_issues_df = existing_issues_df.drop_duplicates(subset=['key'])

# Convert existing issues to dictionary for quick lookup
existing_issues_dict = existing_issues_df.set_index('key').to_dict(orient='index')

# Prepare new data for insertion and update
new_rows = []
update_rows = []

for index, row in issues.iterrows():
    key = row['key']
    if key in existing_issues_dict:
        existing_row = existing_issues_dict[key]
        # Compare all columns
        columns_to_compare = ['self', 'assignee', 'Begin_Date1', 'timespent', 'summary', 'rate',
                              'Working hours', 'Escalation', 'Post Mortem', 'components']
        if any(row[col] != existing_row[col] for col in columns_to_compare):
            update_rows.append(row)
    else:
        new_rows.append(row)

# Insert new rows into the "issues" table

if new_rows:
    new_rows_df = pd.DataFrame(new_rows)
    new_rows_df.to_sql("issues", engine, schema="public", if_exists="append", index=False)
# Update existing rows in the "issues" table
conn = engine.raw_connection()
cursor = conn.cursor()

for row in update_rows:
    key = row['key']
    cursor.execute("""
    INSERT INTO issues (
        id, "key", "self", assignee, "Begin_Date1", timespent, summary, rate,
        "Working hours", Escalation, "Post Mortem", components
    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    ON CONFLICT (id) DO UPDATE SET
        "key" = EXCLUDED."key",
        "self" = EXCLUDED."self",
        assignee = EXCLUDED.assignee,
        "Begin_Date1" = EXCLUDED."Begin_Date1",
        timespent = EXCLUDED.timespent,
        summary = EXCLUDED.summary,
        rate = EXCLUDED.rate,
        "Working hours" = EXCLUDED."Working hours",
        Escalation = EXCLUDED.Escalation,
        "Post Mortem" = EXCLUDED."Post Mortem",
        components = EXCLUDED.components
    """, (row['id'], row['key'], row['self'], row['assignee'], row['Begin_Date1'], row['timespent'], 
          row['summary'], row['rate'], row['Working hours'], row['Escalation'], row['Post Mortem'], row['components']))

# Commit changes and close connection
conn.commit()
cursor.close()
conn.close()

print('All done!')

Сохраняем файл и закрываем его. Разберем каждую секцию этого скрипта:


Строчки 1-18 проверяют наличие необходимых модулей и устанавливают их, если они отсутствуют. Используется встроенная функция __import__ для проверки и subprocess.check_call для установки модулей через pip.


20-32 строчки: Импорт необходимых модулей и библиотек для работы скрипта.


34-46 строчки: Загрузка переменных окружения из файла config.env и их присваивание соответствующим переменным.


49-59 строчки: Определение временных интервалов для запросов к API и форматирование дат в нужный формат.


60-72 строчки: Формирование JQL запроса для поиска задач в Jira, подготовка авторизационных данных и подключение к Jira.


74-96 строчки: Преобразование найденных задач из Jira в список словарей и создание DataFrame с использованием pandas.


98-121 строчки: Подключение к базе данных PostgreSQL, загрузка существующих задач, проверка на наличие дубликатов и подготовка данных для вставки и обновления.


123-157 строчки: Вставка новых строк и обновление существующих в таблице issues базы данных PostgreSQL.


После этого мы можем настроить автоматический запуск скрипта каждый час, чтобы данные были обновлены и хранились для базы и визуализации. Тут можно использовать простой crontab -e.


Затем указываем частоту запуска скрипта jiraScript.py

0 * * * * /usr/bin/python3 /path/to/jira_script_folder/jiraScript.py > /dev/null 2>&1


  • 0 * * * * указывает, что скрипт будет выполняться в начале каждого часа.
  • /usr/bin/python3 указывает полный путь к вашему интерпретатору Python. При необходимости адаптируйте этот путь (команда which python3 поможет найти правильный).
  • /path/to/jira_script_folder/jiraScript.py следует заменить на фактический путь к вашему Python скрипту.

> /dev/null 2>&1 перенаправляет как стандартный вывод, так и стандартную ошибку в /dev/null, что эффективно отбрасывает их.

Установка PowerBI и настройка конфигурации

Установка PowerBI достаточно простая и не занимает много времени. Есть инструкция от Microsoft тут.


Как только у вас появился PowerBI, нам требуется установить такую штуку как on-premise gateway на основном сервере. Это нужно, чтобы был коннект между данными и PowerBI.


Шаги для установки on-premise gateway:


  1. Скачать его отсюда:
  2. В установщике gateway сохраните путь установки по умолчанию, примите условия использования и выберите Install.
  3. Введите данные своего аккаунта Microsoft.
  4. Затем выберите Register a new gateway on this computer > Next:
  5. Введите имя шлюза. Имя должно быть уникальным в пределах клиента. Также введите ключ восстановления. Этот ключ понадобится вам, если вы когда-нибудь захотите восстановить или переместить шлюз. Затем выберите Configure:

Просмотрите информацию в последнем окне. Поскольку в этом примере используется одна и та же учетная запись для Power BI, Power Apps и Power Automate, шлюз доступен для всех трех служб.

Если хотите подробнее узнать про этот шлюз, его установку и как открыть его для для кластеров, можете ознакомиться в этой инструкции от Microsoft.


Как только всё настроено, можем увидеть наши финальные данные в PowerBI, которые подтянулись из основного PostgreSQL сервера — теперь можно строить визуализацию:

Заключение

Вот так можно автоматизировать процесс выгрузки тикетов из Jira и их визуализацию в PowerBI.


Благодаря Python для скриптинга и интеграции с Jira API, а также мощным инструментам PowerBI для визуализации, вы сможете создавать наглядные и информативные отчеты с минимальными усилиями.


Это решение помогло в нашей практике — менеджмент доволен и наличием такого решения, и результатом. Ручная подготовка отчетов, которая создавала много препятствий основным задачам команды, ушла в прошлое.

Внедрим и настроим любой продукт Atlassian в вашу организацию, чтобы автоматизировать процессы и организовать отделы в слаженные группы.
Подключите Jira и развивайте бизнес