Допустим, у нас есть инженерная команда, которая использует Jira для управления инцидентами. В тикетах SRE инженеры учитывают все детали: время начала, время выполнения, системы, которые затронул инцидент и так далее. Кроме этого, в тикетах указывают потраченное время на фикс инцидента — основное для менеджмента, чтобы руководство могло понять эффективность сотрудников, критичность инцидентов и их количество.
Архитектура будет выглядеть примерно так:
Идея здесь следующая:
Разделим наш кейс на четыре основные части:
В любом проекте Jira, который вы хотите взять за основу данных по отчетам, нужно заполнить нужные поля в тикетах.
Например, тикет в Jira, где есть общее описание проблемы / инцидента / задачи с разными данными (отмечены красными линиями):
Alert Time: время регистрации инцидента
Resolve Time: время закрытия
Priority: приоритет задачи
Assignee: ответственный инженер
Components: сервис, который участвует проблеме
Escalation: была ли эскалация другим инженерам
Time Spent SRE (h): потраченное на решение проблемы время
Rate: ставка
Кроме тикетов с данными, в Jira нам потребуется токен:
Сохраните API-токен, он нам пригодиться при написании скрипта.
Для начала устанавливаем 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
> /dev/null 2>&1 перенаправляет как стандартный вывод, так и стандартную ошибку в /dev/null, что эффективно отбрасывает их.
Установка PowerBI достаточно простая и не занимает много времени. Есть инструкция от Microsoft тут.
Как только у вас появился PowerBI, нам требуется установить такую штуку как on-premise gateway на основном сервере. Это нужно, чтобы был коннект между данными и PowerBI.
Шаги для установки on-premise gateway:
Просмотрите информацию в последнем окне. Поскольку в этом примере используется одна и та же учетная запись для Power BI, Power Apps и Power Automate, шлюз доступен для всех трех служб.
Если хотите подробнее узнать про этот шлюз, его установку и как открыть его для для кластеров, можете ознакомиться в этой инструкции от Microsoft.
Как только всё настроено, можем увидеть наши финальные данные в PowerBI, которые подтянулись из основного PostgreSQL сервера — теперь можно строить визуализацию:
Вот так можно автоматизировать процесс выгрузки тикетов из Jira и их визуализацию в PowerBI.
Благодаря Python для скриптинга и интеграции с Jira API, а также мощным инструментам PowerBI для визуализации, вы сможете создавать наглядные и информативные отчеты с минимальными усилиями.
Это решение помогло в нашей практике — менеджмент доволен и наличием такого решения, и результатом. Ручная подготовка отчетов, которая создавала много препятствий основным задачам команды, ушла в прошлое.