データテンプレート: インシデント管理

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.")

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)