เทมเพลตข้อมูลการจัดการเหตุขัดข้อง

import pandas as pd
import numpy as np
from datetime import datetime

# --- Configuration ---
# Specify the path to your raw CSV file exported from Jira
# This file should have one row per changelog item.
INPUT_CSV_PATH = '[Your-Jira-Export-File].csv'
# Specify the desired name for the final event log file
OUTPUT_CSV_PATH = 'jira_incident_event_log.csv'

# --- Main Transformation Logic ---
def transform_jira_to_event_log(input_path, output_path):
    """Transforms a detailed Jira changelog export into a process mining event log."""
    try:
        # Load the raw data, assuming common column names from Jira exporters.
        # Adjust column names here to match your specific export file.
        # 'Issue Key' -> Incident ID
        # 'Date' -> Timestamp of the change
        # 'Field' -> The field that was changed (e.g., 'status', 'assignee')
        # 'Original value' -> Old value
        # 'New value' -> New value
        # You must also include columns for 'Created', 'Resolved', 'Priority', etc., at the issue level.
        df_raw = pd.read_csv(input_path)
    except FileNotFoundError:
        print(f"Error: Input file not found at '{input_path}'. Please check the path.")
        return

    # Standardize column names for easier processing
    # Replace these with the actual column names from your export
    column_map = {
        'Issue key': 'IncidentId',
        'History Date': 'EventTimestamp',
        'History Author': 'Author',
        'Field': 'ChangedField',
        'From String': 'OldValue',
        'To String': 'NewValue',
        'Created': 'CreatedDate',
        'Resolved': 'ResolutionDate',
        'Priority': 'Priority',
        'Status': 'Status',
        'Assignee': 'Assignee',
        # Add mappings for your custom fields if necessary
        # '[Your Assignment Group Field]': 'AssignmentGroup'
    }
    df_raw.rename(columns=column_map, inplace=True)

    # Ensure timestamp columns are in datetime format
    df_raw['EventTimestamp'] = pd.to_datetime(df_raw['EventTimestamp'], errors='coerce')
    df_raw['CreatedDate'] = pd.to_datetime(df_raw['CreatedDate'], errors='coerce')
    df_raw['ResolutionDate'] = pd.to_datetime(df_raw['ResolutionDate'], errors='coerce')

    events = []
    # Group by incident to process each ticket's lifecycle
    for incident_id, group in df_raw.groupby('IncidentId'):
        # Sort changes chronologically
        group = group.sort_values(by='EventTimestamp').reset_index()

        # Get issue-level attributes from the first record
        first_record = group.iloc[0]
        created_date = first_record['CreatedDate']
        resolved_date = first_record['ResolutionDate']

        # 1. Incident Created Event
        events.append({
            'IncidentId': incident_id,
            'ActivityName': 'Incident Created',
            'EventTimestamp': created_date,
            'Assignee': first_record.get('Assignee'),
            'AssignmentGroup': first_record.get('AssignmentGroup'),
            'Status': first_record.get('Status'),
            'Priority': first_record.get('Priority')
        })

        assignee_set = False
        priority_set = False

        # 2. Process Changelog for other events
        for _, row in group.iterrows():
            activity = None
            field = row['ChangedField']
            old_val = str(row['OldValue'])
            new_val = str(row['NewValue'])
            timestamp = row['EventTimestamp']

            # Map changelog entries to activities
            if field == 'status':
                if new_val == 'In Progress' and old_val not in ['Waiting for customer', 'In Progress']:
                    activity = 'Investigation Started'
                elif new_val == 'Waiting for customer':
                    activity = 'Waiting For Customer'
                elif old_val == 'Waiting for customer' and new_val not in ['Resolved', 'Closed']:
                    activity = 'Customer Responded'
                elif new_val == 'Resolved':
                    activity = 'Resolution Proposed'
                    # Also add Incident Resolved at the same time
                    events.append({'IncidentId': incident_id, 'ActivityName': 'Incident Resolved', 'EventTimestamp': timestamp, 'Assignee': row.get('Assignee'), 'AssignmentGroup': row.get('AssignmentGroup'), 'Status': new_val, 'Priority': row.get('Priority')})
                elif new_val == 'Closed':
                    activity = 'Incident Closed'
                elif old_val in ['Resolved', 'Closed'] and new_val not in ['Resolved', 'Closed']:
                    activity = 'Incident Reopened'

            elif field == 'assignee':
                if not assignee_set and new_val != 'nan':
                    activity = 'Incident Assigned'
                    assignee_set = True
                elif assignee_set and new_val != old_val:
                    activity = 'Incident Reassigned'

            elif field == 'priority' and not priority_set:
                activity = 'Incident Prioritized'
                priority_set = True

            elif field == '[Support Team Custom Field]': # Placeholder for your escalation field
                activity = 'Escalated To Specialist Team'
            
            elif field == 'Link' and 'Problem' in new_val:
                 activity = 'Linked To Problem Ticket'

            # Other activities like 'Workaround Provided' and 'Comment Added' are not typically in the changelog
            # and would require different extraction methods (e.g., API calls for comments).

            if activity:
                events.append({
                    'IncidentId': incident_id,
                    'ActivityName': activity,
                    'EventTimestamp': timestamp,
                    'Assignee': row.get('Assignee'),
                    'AssignmentGroup': row.get('AssignmentGroup'),
                    'Status': new_val if field == 'status' else row.get('Status'),
                    'Priority': new_val if field == 'priority' else row.get('Priority')
                })

    # Create final event log DataFrame
    if not events:
        print("Warning: No events were generated. Check your input file and column mappings.")
        return
        
    df_events = pd.DataFrame(events)

    # Add required and recommended attributes
    df_events['SourceSystem'] = 'Jira Service Management'
    df_events['LastDataUpdate'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    
    # Merge issue-level details back to get created/resolved dates for all rows
    issue_details = df_raw[['IncidentId', 'CreatedDate', 'ResolutionDate']].drop_duplicates(subset='IncidentId')
    df_final = pd.merge(df_events, issue_details, on='IncidentId', how='left')

    # Calculate cycle time
    df_final['IncidentResolutionCycleTime'] = (df_final['ResolutionDate'] - df_final['CreatedDate']).dt.total_seconds() / 3600.0
    
    # Sort and write to CSV
    df_final = df_final.sort_values(by=['IncidentId', 'EventTimestamp'])
    df_final.to_csv(output_path, index=False)
    print(f"Successfully created event log at '{output_path}' with {len(df_final)} events.")

# --- Script Execution ---
if __name__ == '__main__':
    transform_jira_to_event_log(INPUT_CSV_PATH, OUTPUT_CSV_PATH)

WITH Incidents AS (
  SELECT
    i.id AS issue_id,
    p.pkey || '-' || i.issuenum AS IncidentId,
    i.assignee AS assignee_key,
    i.issuestatus AS status_id,
    i.priority AS priority_id,
    i.created AS CreatedDate,
    i.resolutiondate AS ResolutionDate,
    EXTRACT(EPOCH FROM (i.resolutiondate - i.created)) AS IncidentResolutionCycleTime
  FROM jiraissue i
  JOIN project p ON i.project = p.id
  JOIN issuetype it ON i.issuetype = it.id
  WHERE it.pname = 'Incident'
    AND p.pkey IN ('[your_project_key]') -- E.g., 'ITSM', 'HELPDESK'
    AND i.created BETWEEN '[start_date]' AND '[end_date]' -- E.g., '2023-01-01' AND '2023-06-30'
)
-- 1. Incident Created
SELECT
  i.IncidentId,
  'Incident Created' AS ActivityName,
  i.CreatedDate AS EventTimestamp,
  'Jira Service Management' AS SourceSystem,
  CURRENT_TIMESTAMP AS LastDataUpdate,
  au.display_name AS Assignee,
  cfo.customvalue AS AssignmentGroup,
  s.pname AS Status,
  pr.pname AS Priority,
  i.CreatedDate,
  i.ResolutionDate,
  i.IncidentResolutionCycleTime
FROM Incidents i
LEFT JOIN app_user au ON i.assignee_key = au.user_key
LEFT JOIN issuestatus s ON i.status_id = s.id
LEFT JOIN priority pr ON i.priority_id = pr.id
LEFT JOIN customfieldvalue cfv_group ON i.issue_id = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group] -- Placeholder for Assignment Group custom field ID
LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id

UNION ALL

-- 2. Incident Prioritized (First Time)
SELECT
  i.IncidentId, 'Incident Prioritized' AS ActivityName, T.EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, T.Assignee, T.AssignmentGroup, T.Status, T.Priority, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM Incidents i
JOIN (
    SELECT cg.issueid, cg.created AS EventTimestamp, au_cg.display_name AS Assignee, cfo.customvalue AS AssignmentGroup, s.pname AS Status, ci.newstring AS Priority,
    ROW_NUMBER() OVER(PARTITION BY cg.issueid ORDER BY cg.created ASC) as rn
    FROM changegroup cg
    JOIN changeitem ci ON cg.id = ci.groupid
    JOIN Incidents inc_filter ON cg.issueid = inc_filter.issue_id
    LEFT JOIN app_user au_cg ON cg.author = au_cg.user_key
    LEFT JOIN jiraissue ji_state ON cg.issueid = ji_state.id
    LEFT JOIN issuestatus s ON ji_state.issuestatus = s.id
    LEFT JOIN customfieldvalue cfv_group ON cg.issueid = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
    LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
    WHERE ci.field = 'Priority'
) T ON i.issue_id = T.issueid AND T.rn = 1

UNION ALL

-- 3. Incident Assigned (First Time)
SELECT
  i.IncidentId, 'Incident Assigned' AS ActivityName, T.EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, T.Assignee, T.AssignmentGroup, T.Status, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM Incidents i
JOIN (
    SELECT cg.issueid, cg.created AS EventTimestamp, ci.newstring AS Assignee, cfo.customvalue AS AssignmentGroup, s.pname AS Status,
    ROW_NUMBER() OVER(PARTITION BY cg.issueid ORDER BY cg.created ASC) as rn
    FROM changegroup cg
    JOIN changeitem ci ON cg.id = ci.groupid
    JOIN Incidents inc_filter ON cg.issueid = inc_filter.issue_id
    LEFT JOIN jiraissue ji_state ON cg.issueid = ji_state.id
    LEFT JOIN issuestatus s ON ji_state.issuestatus = s.id
    LEFT JOIN customfieldvalue cfv_group ON cg.issueid = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
    LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
    WHERE ci.fieldtype = 'jira' AND ci.field = 'assignee' AND ci.newvalue IS NOT NULL
) T ON i.issue_id = T.issueid AND T.rn = 1
LEFT JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN priority pr ON ji.priority = pr.id

UNION ALL

-- 4. Investigation Started
SELECT
  i.IncidentId, 'Investigation Started' AS ActivityName, cg.created AS EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, au.display_name, cfo.customvalue, ci.newstring, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM changegroup cg
JOIN changeitem ci ON cg.id = ci.groupid
JOIN Incidents i ON cg.issueid = i.issue_id
LEFT JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN app_user au ON ji.assignee = au.user_key
LEFT JOIN priority pr ON ji.priority = pr.id
LEFT JOIN customfieldvalue cfv_group ON i.issue_id = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
WHERE ci.field = 'status' AND ci.oldstring IN ('Open', 'New') AND ci.newstring = 'In Progress'

UNION ALL

-- 5. Comment Added
SELECT
  i.IncidentId, 'Comment Added' AS ActivityName, ja.created AS EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, au.display_name, cfo.customvalue, s.pname, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM jiraaction ja
JOIN Incidents i ON ja.issueid = i.issue_id
LEFT JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN app_user au ON ji.assignee = au.user_key
LEFT JOIN issuestatus s ON ji.issuestatus = s.id
LEFT JOIN priority pr ON ji.priority = pr.id
LEFT JOIN customfieldvalue cfv_group ON i.issue_id = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
WHERE ja.actiontype = 'comment'

UNION ALL

-- 6. Incident Reassigned
SELECT
  i.IncidentId, 'Incident Reassigned' AS ActivityName, T.EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, T.Assignee, T.AssignmentGroup, T.Status, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM Incidents i
JOIN (
    SELECT cg.issueid, cg.created AS EventTimestamp, ci.newstring AS Assignee, cfo.customvalue AS AssignmentGroup, s.pname AS Status,
    ROW_NUMBER() OVER(PARTITION BY cg.issueid ORDER BY cg.created ASC) as rn
    FROM changegroup cg
    JOIN changeitem ci ON cg.id = ci.groupid
    JOIN Incidents inc_filter ON cg.issueid = inc_filter.issue_id
    LEFT JOIN jiraissue ji_state ON cg.issueid = ji_state.id
    LEFT JOIN issuestatus s ON ji_state.issuestatus = s.id
    LEFT JOIN customfieldvalue cfv_group ON cg.issueid = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
    LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
    WHERE ci.fieldtype = 'jira' AND ci.field = 'assignee' AND ci.newvalue IS NOT NULL
) T ON i.issue_id = T.issueid AND T.rn > 1
LEFT JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN priority pr ON ji.priority = pr.id

UNION ALL

-- 7. Escalated To Specialist Team
SELECT
  i.IncidentId, 'Escalated To Specialist Team' AS ActivityName, cg.created AS EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, au.display_name, ci.newstring, s.pname, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM changegroup cg
JOIN changeitem ci ON cg.id = ci.groupid
JOIN Incidents i ON cg.issueid = i.issue_id
JOIN customfield cf ON cf.cfname = '[Support Team Custom Field Name]' -- Placeholder for custom field name, e.g., 'Support Team'
JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN app_user au ON ji.assignee = au.user_key
LEFT JOIN issuestatus s ON ji.issuestatus = s.id
LEFT JOIN priority pr ON ji.priority = pr.id
WHERE ci.field = cf.id

UNION ALL

-- 8. Waiting For Customer
SELECT
  i.IncidentId, 'Waiting For Customer' AS ActivityName, cg.created AS EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, au.display_name, cfo.customvalue, ci.newstring, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM changegroup cg
JOIN changeitem ci ON cg.id = ci.groupid
JOIN Incidents i ON cg.issueid = i.issue_id
LEFT JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN app_user au ON ji.assignee = au.user_key
LEFT JOIN priority pr ON ji.priority = pr.id
LEFT JOIN customfieldvalue cfv_group ON i.issue_id = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
WHERE ci.field = 'status' AND ci.newstring = 'Waiting for Customer'

UNION ALL

-- 9. Customer Responded
SELECT
  i.IncidentId, 'Customer Responded' AS ActivityName, cg.created AS EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, au.display_name, cfo.customvalue, ci.newstring, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM changegroup cg
JOIN changeitem ci ON cg.id = ci.groupid
JOIN Incidents i ON cg.issueid = i.issue_id
LEFT JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN app_user au ON ji.assignee = au.user_key
LEFT JOIN priority pr ON ji.priority = pr.id
LEFT JOIN customfieldvalue cfv_group ON i.issue_id = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
WHERE ci.field = 'status' AND ci.oldstring = 'Waiting for Customer'

UNION ALL

-- 10. Workaround Provided
SELECT
  i.IncidentId, 'Workaround Provided' AS ActivityName, cg.created AS EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, au.display_name, cfo.customvalue, ci.newstring, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM changegroup cg
JOIN changeitem ci ON cg.id = ci.groupid
JOIN Incidents i ON cg.issueid = i.issue_id
LEFT JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN app_user au ON ji.assignee = au.user_key
LEFT JOIN priority pr ON ji.priority = pr.id
LEFT JOIN customfieldvalue cfv_group ON i.issue_id = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
WHERE ci.field = 'status' AND ci.newstring = 'Workaround Provided' -- Assumes this status exists

UNION ALL

-- 11. Resolution Proposed & 14. Incident Resolved
SELECT
  i.IncidentId, 'Resolution Proposed' AS ActivityName, cg.created AS EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, au.display_name, cfo.customvalue, ci.newstring, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM changegroup cg
JOIN changeitem ci ON cg.id = ci.groupid
JOIN Incidents i ON cg.issueid = i.issue_id
LEFT JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN app_user au ON ji.assignee = au.user_key
LEFT JOIN priority pr ON ji.priority = pr.id
LEFT JOIN customfieldvalue cfv_group ON i.issue_id = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
WHERE ci.field = 'status' AND ci.newstring = 'Resolved'

UNION ALL

-- Also capture Incident Resolved from the resolutiondate field for cases where history might be missed.
SELECT
  i.IncidentId, 'Incident Resolved' AS ActivityName, i.ResolutionDate AS EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, au.display_name, cfo.customvalue, s.pname, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM Incidents i
LEFT JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN app_user au ON ji.assignee = au.user_key
LEFT JOIN issuestatus s ON ji.issuestatus = s.id
LEFT JOIN priority pr ON ji.priority = pr.id
LEFT JOIN customfieldvalue cfv_group ON i.issue_id = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
WHERE i.ResolutionDate IS NOT NULL

UNION ALL

-- 12. Linked To Problem Ticket
SELECT
  i.IncidentId, 'Linked To Problem Ticket' AS ActivityName, il.created AS EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, au.display_name, cfo.customvalue, s.pname, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM issuelink il
JOIN Incidents i ON il.source = i.issue_id
JOIN jiraissue linked_issue ON il.destination = linked_issue.id
JOIN issuetype linked_issuetype ON linked_issue.issuetype = linked_issuetype.id
LEFT JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN app_user au ON ji.assignee = au.user_key
LEFT JOIN issuestatus s ON ji.issuestatus = s.id
LEFT JOIN priority pr ON ji.priority = pr.id
LEFT JOIN customfieldvalue cfv_group ON i.issue_id = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
WHERE linked_issuetype.pname = 'Problem'

UNION ALL

-- 13. Incident Reopened
SELECT
  i.IncidentId, 'Incident Reopened' AS ActivityName, cg.created AS EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, au.display_name, cfo.customvalue, ci.newstring, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM changegroup cg
JOIN changeitem ci ON cg.id = ci.groupid
JOIN Incidents i ON cg.issueid = i.issue_id
LEFT JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN app_user au ON ji.assignee = au.user_key
LEFT JOIN priority pr ON ji.priority = pr.id
LEFT JOIN customfieldvalue cfv_group ON i.issue_id = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
WHERE ci.field = 'status' AND ci.oldstring IN ('Resolved', 'Closed') AND ci.newstring NOT IN ('Resolved', 'Closed')

UNION ALL

-- 15. Incident Closed
SELECT
  i.IncidentId, 'Incident Closed' AS ActivityName, cg.created AS EventTimestamp, 'Jira Service Management', CURRENT_TIMESTAMP, au.display_name, cfo.customvalue, ci.newstring, pr.pname, i.CreatedDate, i.ResolutionDate, i.IncidentResolutionCycleTime
FROM changegroup cg
JOIN changeitem ci ON cg.id = ci.groupid
JOIN Incidents i ON cg.issueid = i.issue_id
LEFT JOIN jiraissue ji ON i.issue_id = ji.id
LEFT JOIN app_user au ON ji.assignee = au.user_key
LEFT JOIN priority pr ON ji.priority = pr.id
LEFT JOIN customfieldvalue cfv_group ON i.issue_id = cfv_group.issue AND cfv_group.customfield = [custom_field_id_for_assignment_group]
LEFT JOIN customfieldoption cfo ON cfv_group.stringvalue = cfo.id
WHERE ci.field = 'status' AND ci.newstring = 'Closed'

import requests
import pandas as pd
import os
from datetime import datetime, timezone
from dateutil.parser import parse

class=class="hl-string">"hl-comment">## CONFIGURATION ##
class=class="hl-string">"hl-comment"># Your Jira instance URL
JIRA_URL = class="hl-string">"https://[your-company].atlassian.net"
class=class="hl-string">"hl-comment"># Your email address used for Jira login
JIRA_EMAIL = class="hl-string">"[your-email@company.com]"
class=class="hl-string">"hl-comment"># Your Jira API token. Store this securely, for example as an environment variable.
API_TOKEN = os.environ.get(class="hl-string">"JIRA_API_TOKEN", class="hl-string">"[Your_Jira_API_Token]")

class=class="hl-string">"hl-comment"># JQL query to fetch incidents. It is recommended to limit the date range.
JQL_QUERY = class="hl-string">"issuetype = Incident AND project = '[YOUR_PROJECT_KEY]' AND created >= '-180d'"

class=class="hl-string">"hl-comment"># Custom field IDs for recommended attributes. Find these in your Jira settings.
class=class="hl-string">"hl-comment"># Example: class="hl-string">'customfield_10021'
ASSIGNMENT_GROUP_FIELD_ID = class="hl-string">"[customfield_id_for_assignment_group]"

class=class="hl-string">"hl-comment"># Status names as defined in your Jira workflow
STATUS_IN_PROGRESS = class="hl-string">'In Progress'
STATUS_WAITING_FOR_CUSTOMER = class="hl-string">'Waiting for customer'
STATUS_RESOLVED = class="hl-string">'Resolved'
STATUS_CLOSED = class="hl-string">'Closed'

class=class="hl-string">"hl-comment"># --- End of Configuration ---

def get_field_value_from_changelog(item):
    class=class="hl-string">"hl-comment"># Helper to get the actual value, which can be in different keys
    return item.get(class="hl-string">'toString') or item.get(class="hl-string">'to') or class="hl-string">''

def get_field_from_changelog(item):
    return item.get(class="hl-string">'fromString') or item.get(class="hl-string">'from') or class="hl-string">''

def calculate_cycle_time(start_str, end_str):
    if not start_str or not end_str:
        return None
    try:
        start_dt = parse(start_str)
        end_dt = parse(end_str)
        duration_seconds = (end_dt - start_dt).total_seconds()
        return round(duration_seconds / 3600, 2) class=class="hl-string">"hl-comment"># Return cycle time in hours
    except (ValueError, TypeError):
        return None

def fetch_jira_issues(jql):
    class="hl-string">"""Fetches issues from Jira API, handling pagination."""
    auth = (JIRA_EMAIL, API_TOKEN)
    headers = {class="hl-string">"Accept": class="hl-string">"application/json"}
    issues = []
    start_at = 0
    max_results = 50

    while True:
        api_url = fclass="hl-string">"{JIRA_URL}/rest/api/3/search"
        params = {
            class="hl-string">'jql': jql,
            class="hl-string">'startAt': start_at,
            class="hl-string">'maxResults': max_results,
            class="hl-string">'expand': class="hl-string">'changelog'
        }
        try:
            response = requests.get(api_url, headers=headers, params=params, auth=auth)
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            data = response.json()
            issues.extend(data.get(class="hl-string">'issues', []))

            total = data.get(class="hl-string">'total', 0)
            start_at += len(data.get(class="hl-string">'issues', []))
            print(fclass="hl-string">"Fetched {start_at} of {total} issues...")

            if start_at >= total:
                break
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching data from Jira: {e}")
            if e.response is not None:
                print(fclass="hl-string">"Response content: {e.response.text}")
            break

    return issues

def create_event_log(issues):
    class="hl-string">"""Processes issues and their changelogs to create a structured event log."""
    event_log = []
    last_data_update = datetime.now(timezone.utc).isoformat()

    for issue in issues:
        incident_id = issue[class="hl-string">'key']
        fields = issue[class="hl-string">'fields']
        changelog = issue.get(class="hl-string">'changelog', {}).get(class="hl-string">'histories', [])
        
        created_date = fields.get(class="hl-string">'created')
        resolution_date = fields.get(class="hl-string">'resolutiondate')
        cycle_time = calculate_cycle_time(created_date, resolution_date)

        class=class="hl-string">"hl-comment"># Track current state for attributes
        current_assignee = fields.get(class="hl-string">'assignee', {}).get(class="hl-string">'displayName') if fields.get(class="hl-string">'assignee') else None
        current_status = fields.get(class="hl-string">'status', {}).get(class="hl-string">'name')
        current_priority = fields.get(class="hl-string">'priority', {}).get(class="hl-string">'name') if fields.get(class="hl-string">'priority') else None
        current_assignment_group = fields.get(ASSIGNMENT_GROUP_FIELD_ID)

        class=class="hl-string">"hl-comment"># Base attributes for every event of this case
        base_event = {
            class="hl-string">'IncidentId': incident_id,
            class="hl-string">'SourceSystem': class="hl-string">'Jira Service Management',
            class="hl-string">'LastDataUpdate': last_data_update,
            class="hl-string">'CreatedDate': created_date,
            class="hl-string">'ResolutionDate': resolution_date,
            class="hl-string">'IncidentResolutionCycleTime': cycle_time
        }

        class=class="hl-string">"hl-comment"># 1. Incident Created
        event_log.append({
            **base_event,
            class="hl-string">'ActivityName': class="hl-string">'Incident Created',
            class="hl-string">'EventTimestamp': created_date,
            class="hl-string">'Assignee': current_assignee,
            class="hl-string">'AssignmentGroup': current_assignment_group,
            class="hl-string">'Status': fields.get(class="hl-string">'status', {}).get(class="hl-string">'name'),
            class="hl-string">'Priority': current_priority
        })

        class=class="hl-string">"hl-comment"># 2. Comments Added
        if fields.get(class="hl-string">'comment', {}).get(class="hl-string">'comments'):
            for comment in fields[class="hl-string">'comment'][class="hl-string">'comments']:
                event_log.append({
                    **base_event,
                    class="hl-string">'ActivityName': class="hl-string">'Comment Added',
                    class="hl-string">'EventTimestamp': comment[class="hl-string">'created'],
                    class="hl-string">'Assignee': current_assignee, class=class="hl-string">"hl-comment"># Note: Assignee at time of comment is not easily available
                    class="hl-string">'AssignmentGroup': current_assignment_group,
                    class="hl-string">'Status': current_status,
                    class="hl-string">'Priority': current_priority
                })

        class=class="hl-string">"hl-comment"># 3. Linked To Problem Ticket
        if fields.get(class="hl-string">'issuelinks'):
            for link in fields[class="hl-string">'issuelinks']:
                link_type = link.get(class="hl-string">'type', {}).get(class="hl-string">'name')
                if class="hl-string">'inwardIssue' in link and link.get(class="hl-string">'inwardIssue', {}).get(class="hl-string">'fields', {}).get(class="hl-string">'issuetype', {}).get(class="hl-string">'name') == class="hl-string">'Problem':
                     event_log.append({
                        **base_event,
                        class="hl-string">'ActivityName': class="hl-string">'Linked To Problem Ticket',
                        class="hl-string">'EventTimestamp': created_date, class=class="hl-string">"hl-comment"># Approximation: link creation time is not readily available
                        class="hl-string">'Assignee': current_assignee,
                        class="hl-string">'AssignmentGroup': current_assignment_group,
                        class="hl-string">'Status': current_status,
                        class="hl-string">'Priority': current_priority
                    })

        class=class="hl-string">"hl-comment"># Process changelog for state changes
        first_assignment = True
        first_prioritization = True

        for history in sorted(changelog, key=lambda x: x[class="hl-string">'created']):
            event_timestamp = history[class="hl-string">'created']
            author = history.get(class="hl-string">'author', {}).get(class="hl-string">'displayName')

            for item in history[class="hl-string">'items']:
                field = item.get(class="hl-string">'field', class="hl-string">'').lower()
                activity = None

                class=class="hl-string">"hl-comment"># Track attribute changes over time
                if field == class="hl-string">'assignee':
                    current_assignee = get_field_value_from_changelog(item)
                    if first_assignment and get_field_from_changelog(item) is None:
                        activity = class="hl-string">'Incident Assigned'
                        first_assignment = False
                    elif not first_assignment:
                        activity = class="hl-string">'Incident Reassigned'
                elif field == class="hl-string">'priority' and first_prioritization:
                    current_priority = get_field_value_from_changelog(item)
                    activity = class="hl-string">'Incident Prioritized'
                    first_prioritization = False
                elif field == class="hl-string">'status':
                    from_status = item.get(class="hl-string">'fromString')
                    to_status = item.get(class="hl-string">'toString')
                    current_status = to_status
                    if to_status == STATUS_IN_PROGRESS:
                        activity = class="hl-string">'Investigation Started'
                    elif to_status == STATUS_WAITING_FOR_CUSTOMER:
                        activity = class="hl-string">'Waiting For Customer'
                    elif from_status == STATUS_WAITING_FOR_CUSTOMER:
                        activity = class="hl-string">'Customer Responded'
                    elif to_status == STATUS_RESOLVED:
                        class=class="hl-string">"hl-comment"># This can represent both Proposed and Resolved
                        event_log.append({
                            **base_event,
                            class="hl-string">'ActivityName': class="hl-string">'Resolution Proposed',
                            class="hl-string">'EventTimestamp': event_timestamp,
                            class="hl-string">'Assignee': current_assignee,
                            class="hl-string">'AssignmentGroup': current_assignment_group,
                            class="hl-string">'Status': to_status,
                            class="hl-string">'Priority': current_priority
                        })
                        activity = class="hl-string">'Incident Resolved'
                    elif to_status == STATUS_CLOSED:
                        activity = class="hl-string">'Incident Closed'
                    elif from_status in [STATUS_RESOLVED, STATUS_CLOSED] and to_status not in [STATUS_RESOLVED, STATUS_CLOSED]:
                         activity = class="hl-string">'Incident Reopened'
                elif item.get(class="hl-string">'fieldId') == ASSIGNMENT_GROUP_FIELD_ID:
                    current_assignment_group = get_field_value_from_changelog(item)
                    activity = class="hl-string">'Escalated To Specialist Team'
                elif field == class="hl-string">'resolution' and item.get(class="hl-string">'toString') == class="hl-string">'Workaround provided':
                    class=class="hl-string">"hl-comment"># This is one way to capture class="hl-string">'Workaround Provided', highly dependent on configuration
                    activity = class="hl-string">'Workaround Provided'

                if activity:
                    event_log.append({
                        **base_event,
                        class="hl-string">'ActivityName': activity,
                        class="hl-string">'EventTimestamp': event_timestamp,
                        class="hl-string">'Assignee': current_assignee,
                        class="hl-string">'AssignmentGroup': current_assignment_group,
                        class="hl-string">'Status': current_status,
                        class="hl-string">'Priority': current_priority
                    })

    return event_log

if __name__ == class="hl-string">"__main__":
    print(class="hl-string">"Starting Jira incident data extraction...")
    issues_data = fetch_jira_issues(JQL_QUERY)
    
    if issues_data:
        print(fclass="hl-string">"\nProcessing {len(issues_data)} issues to create event log...")
        event_log_data = create_event_log(issues_data)
        
        class=class="hl-string">"hl-comment"># Create DataFrame and save to CSV
        df = pd.DataFrame(event_log_data)
        class=class="hl-string">"hl-comment"># Sort by case and timestamp for a logical event log order
        df = df.sort_values(by=[class="hl-string">'IncidentId', class="hl-string">'EventTimestamp'])
        
        output_filename = class="hl-string">'jira_incident_event_log.csv'
        df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%f%z')
        print(fclass="hl-string">"\nExtraction complete. Event log saved to '{output_filename}'.")
    else:
        print(class="hl-string">"No issues found for the given JQL query.")

WITH source_events AS (
    SELECT
        CAST(NULL AS VARCHAR(255)) AS IncidentId,
        CAST(NULL AS VARCHAR(255)) AS ActivityName,
        CAST(NULL AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS VARCHAR(255)) AS SourceSystem,
        CAST(NULL AS TIMESTAMP) AS LastDataUpdate,
        CAST(NULL AS VARCHAR(255)) AS Assignee,
        CAST(NULL AS VARCHAR(255)) AS AssignmentGroup,
        CAST(NULL AS VARCHAR(255)) AS Status,
        CAST(NULL AS VARCHAR(255)) AS Priority,
        CAST(NULL AS TIMESTAMP) AS CreatedDate,
        CAST(NULL AS TIMESTAMP) AS ResolutionDate
    WHERE 1 = 0
), webhook_events AS (
    SELECT
        CAST([Your incident identifier expression] AS VARCHAR(255)) AS IncidentId,
        CAST([Your activity name expression] AS VARCHAR(255)) AS ActivityName,
        CAST([Your source event timestamp expression] AS TIMESTAMP) AS EventTimestamp,
        CAST('Jira Service Management' AS VARCHAR(255)) AS SourceSystem,
        CAST([Your extraction refresh timestamp expression] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Your assignee expression] AS VARCHAR(255)) AS Assignee,
        CAST([Your assignment group expression] AS VARCHAR(255)) AS AssignmentGroup,
        CAST([Your status expression] AS VARCHAR(255)) AS Status,
        CAST([Your priority expression] AS VARCHAR(255)) AS Priority,
        CAST([Your created date expression] AS TIMESTAMP) AS CreatedDate,
        CAST([Your resolution date expression] AS TIMESTAMP) AS ResolutionDate,
        CAST([Your issue type expression] AS VARCHAR(255)) AS IssueType,
        CAST([Your previous status expression] AS VARCHAR(255)) AS PreviousStatus,
        CAST([Your changed field expression] AS VARCHAR(255)) AS ChangedField,
        CAST([Your previous assignee expression] AS VARCHAR(255)) AS PreviousAssignee,
        CAST([Your previous assignment group expression] AS VARCHAR(255)) AS PreviousAssignmentGroup,
        CAST([Your support team expression] AS VARCHAR(255)) AS SupportTeam,
        CAST([Your previous support team expression] AS VARCHAR(255)) AS PreviousSupportTeam,
        CAST([Your comment identifier expression] AS VARCHAR(255)) AS CommentIdentifier,
        CAST([Your issue link type expression] AS VARCHAR(255)) AS IssueLinkType,
        CAST([Your linked issue type expression] AS VARCHAR(255)) AS LinkedIssueType,
        CAST([Your workaround condition expression] AS BOOLEAN) AS WorkaroundCondition,
        CAST([Your resolution confirmation expression] AS BOOLEAN) AS ResolutionConfirmation
    FROM [Your webhook event source]
    WHERE CAST([Your issue type expression] AS VARCHAR(255)) = 'Incident'
      AND CAST([Your source event timestamp expression] AS TIMESTAMP) >= CAST([Your start date parameter] AS TIMESTAMP)
      AND CAST([Your source event timestamp expression] AS TIMESTAMP) < CAST([Your end date parameter] AS TIMESTAMP)
      AND [Your company or scope filter expression]
), activity_events AS (
    SELECT IncidentId, 'Incident Created' AS ActivityName, EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Incident Created'
    UNION ALL
    SELECT IncidentId, 'Incident Prioritized', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Incident Prioritized'
       OR (ChangedField IN ('Priority', 'Severity') AND Priority IS NOT NULL)
    UNION ALL
    SELECT IncidentId, 'Incident Assigned', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Incident Assigned'
       OR (ChangedField IN ('Assignee', 'Assigned Group') AND (Assignee IS NOT NULL OR AssignmentGroup IS NOT NULL) AND PreviousAssignee IS NULL AND PreviousAssignmentGroup IS NULL)
    UNION ALL
    SELECT IncidentId, 'Investigation Started', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Investigation Started'
       OR (PreviousStatus IN ('Open', 'New') AND Status = 'In Progress')
    UNION ALL
    SELECT IncidentId, 'Comment Added', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Comment Added'
       OR (ChangedField = 'Comment' AND CommentIdentifier IS NOT NULL)
    UNION ALL
    SELECT IncidentId, 'Incident Reassigned', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Incident Reassigned'
       OR (ChangedField IN ('Assignee', 'Assigned Group') AND (PreviousAssignee IS NOT NULL OR PreviousAssignmentGroup IS NOT NULL))
    UNION ALL
    SELECT IncidentId, 'Escalated To Specialist Team', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Escalated To Specialist Team'
       OR (ChangedField = 'Support Team' AND SupportTeam IN ('[Your specialist team value]'))
       OR (ChangedField IN ('Assignee', 'Assigned Group') AND AssignmentGroup IN ('[Your specialist team value]'))
    UNION ALL
    SELECT IncidentId, 'Waiting For Customer', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Waiting For Customer'
       OR Status = '[Your waiting for customer status]'
    UNION ALL
    SELECT IncidentId, 'Customer Responded', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Customer Responded'
       OR (PreviousStatus = '[Your waiting for customer status]' AND Status <> '[Your waiting for customer status]')
    UNION ALL
    SELECT IncidentId, 'Workaround Provided', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Workaround Provided'
       OR WorkaroundCondition = TRUE
    UNION ALL
    SELECT IncidentId, 'Resolution Proposed', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Resolution Proposed'
       OR (PreviousStatus <> 'Resolved' AND Status = 'Resolved')
    UNION ALL
    SELECT IncidentId, 'Linked To Problem Ticket', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Linked To Problem Ticket'
       OR (IssueLinkType IN ('relates to', 'caused by') AND LinkedIssueType = 'Problem')
    UNION ALL
    SELECT IncidentId, 'Incident Reopened', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Incident Reopened'
       OR (PreviousStatus IN ('Resolved', 'Closed') AND Status IN ('Open', 'New', 'Reopened', 'In Progress'))
    UNION ALL
    SELECT IncidentId, 'Incident Resolved', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Incident Resolved'
       OR (ResolutionConfirmation = TRUE)
       OR (PreviousStatus <> 'Resolved' AND Status = 'Resolved')
    UNION ALL
    SELECT IncidentId, 'Incident Closed', EventTimestamp, SourceSystem, LastDataUpdate, Assignee, AssignmentGroup, Status, Priority, CreatedDate, ResolutionDate
    FROM webhook_events
    WHERE ActivityName = 'Incident Closed'
       OR (PreviousStatus <> 'Closed' AND Status = 'Closed')
)
SELECT
    IncidentId,
    ActivityName,
    EventTimestamp,
    SourceSystem,
    LastDataUpdate,
    Assignee,
    AssignmentGroup,
    Status,
    Priority,
    CreatedDate,
    ResolutionDate
FROM activity_events
WHERE IncidentId IS NOT NULL
  AND ActivityName IS NOT NULL
  AND EventTimestamp IS NOT NULL
ORDER BY IncidentId, EventTimestamp, ActivityName;