เทมเพลตข้อมูลสำหรับการจัดการคำขอบริการ

project = "JSM" AND issuetype = "Service Request" AND created >= "2023-01-01" AND created <= "2023-12-31" ORDER BY created ASC

WITH issue_base AS (
    SELECT 
        i.id AS issue_id,
        i.issuenum,
        i.project AS project_id,
        p.pkey || '-' || i.issuenum AS ServiceRequestId,
        i.created AS creation_timestamp,
        i.resolutiondate AS resolution_timestamp,
        u.display_name AS Assignee,
        pr.pname AS RequestPriority,
        s.pname AS RequestStatus,
        it.pname AS IssueTypeName, 
        cf_type.stringvalue AS RequestType,
        cf_sla.datevalue AS SlaDueDate
    FROM jiraissue i
    JOIN project p ON i.project = p.id
    LEFT JOIN issuetype it ON i.issuetype = it.id
    LEFT JOIN issuestatus s ON i.issuestatus = s.id
    LEFT JOIN priority pr ON i.priority = pr.id
    LEFT JOIN app_user u ON i.assignee = u.user_key
    LEFT JOIN customfieldvalue cf_type ON i.id = cf_type.issue AND cf_type.customfield = [Your Request Type Custom Field ID]
    LEFT JOIN customfieldvalue cf_sla ON i.id = cf_sla.issue AND cf_sla.customfield = [Your SLA Due Date Custom Field ID]
    WHERE p.pkey IN ('[Your Project Key]') 
    AND it.pname = '[Your Service Request Issue Type]' 
    AND i.created >= '[YYYY-MM-DD]'
),
change_history AS (
    SELECT 
        cg.issueid AS issue_id,
        cg.created AS change_timestamp,
        ci.field AS changed_field,
        ci.oldstring AS old_value,
        ci.newstring AS new_value
    FROM changegroup cg
    JOIN changeitem ci ON cg.id = ci.groupid
    WHERE ci.fieldtype = 'jira' AND ci.field IN ('status', 'assignee', 'resolution')
)
SELECT
    ib.ServiceRequestId,
    'Service Request Created' AS ActivityName,
    ib.creation_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib

UNION ALL

SELECT
    ib.ServiceRequestId,
    'Request Triaged' AS ActivityName,
    ch.change_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib
JOIN change_history ch ON ib.issue_id = ch.issue_id
WHERE ch.changed_field = 'status' AND ch.new_value = '[Status for Triage]'

UNION ALL

SELECT
    ib.ServiceRequestId,
    'Request Assigned' AS ActivityName,
    ch.change_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib
JOIN change_history ch ON ib.issue_id = ch.issue_id
WHERE ch.changed_field = 'assignee' AND ch.new_value IS NOT NULL

UNION ALL

SELECT
    ib.ServiceRequestId,
    'Information Requested' AS ActivityName,
    ch.change_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib
JOIN change_history ch ON ib.issue_id = ch.issue_id
WHERE ch.changed_field = 'status' AND ch.new_value = '[Waiting for Customer Status]'

UNION ALL

SELECT
    ib.ServiceRequestId,
    'Information Provided' AS ActivityName,
    ch.change_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib
JOIN change_history ch ON ib.issue_id = ch.issue_id
WHERE ch.changed_field = 'status' AND ch.old_value = '[Waiting for Customer Status]'

UNION ALL

SELECT
    ib.ServiceRequestId,
    'Vendor Engagement Started' AS ActivityName,
    ch.change_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib
JOIN change_history ch ON ib.issue_id = ch.issue_id
WHERE ch.changed_field = 'status' AND ch.new_value = '[Waiting for Vendor Status]'

UNION ALL

SELECT
    ib.ServiceRequestId,
    'Vendor Engagement Ended' AS ActivityName,
    ch.change_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib
JOIN change_history ch ON ib.issue_id = ch.issue_id
WHERE ch.changed_field = 'status' AND ch.old_value = '[Waiting for Vendor Status]'

UNION ALL

SELECT
    ib.ServiceRequestId,
    'Solution Implemented' AS ActivityName,
    ch.change_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib
JOIN change_history ch ON ib.issue_id = ch.issue_id
WHERE ch.changed_field = 'status' AND ch.new_value = '[Solution Implemented Status]'

UNION ALL

SELECT
    ib.ServiceRequestId,
    'Resolution Proposed' AS ActivityName,
    ch.change_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib
JOIN change_history ch ON ib.issue_id = ch.issue_id
WHERE ch.changed_field = 'status' AND ch.new_value = '[Pending Customer Acceptance Status]'

UNION ALL

SELECT
    ib.ServiceRequestId,
    'Request Reopened' AS ActivityName,
    ch.change_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib
JOIN change_history ch ON ib.issue_id = ch.issue_id
WHERE ch.changed_field = 'resolution' AND ch.new_value IS NULL AND ch.old_value IS NOT NULL

UNION ALL

SELECT
    ib.ServiceRequestId,
    'Resolution Confirmed' AS ActivityName,
    ch.change_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib
JOIN change_history ch ON ib.issue_id = ch.issue_id
WHERE ch.changed_field = 'status' AND ch.old_value = '[Pending Customer Acceptance Status]' AND ch.new_value = '[Resolved Status]'

UNION ALL

SELECT
    ib.ServiceRequestId,
    'Service Request Resolved' AS ActivityName,
    ib.resolution_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib
WHERE ib.resolution_timestamp IS NOT NULL

UNION ALL

SELECT
    ib.ServiceRequestId,
    'Service Request Closed' AS ActivityName,
    ch.change_timestamp AS EventTime,
    'Jira' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    ib.Assignee,
    ib.RequestPriority,
    ib.RequestStatus,
    ib.RequestType,
    ib.SlaDueDate
FROM issue_base ib
JOIN change_history ch ON ib.issue_id = ch.issue_id
WHERE ch.changed_field = 'status' AND ch.new_value = '[Closed Status]'
ORDER BY 1, 3;

import os
import requests
import pandas as pd
from datetime import datetime, timezone

class=class="hl-string">"hl-comment"># --- CONFIGURATION --- 
class=class="hl-string">"hl-comment"># Replace with your Jira instance details
JIRA_URL = class="hl-string">"https://your-domain.atlassian.net"
JIRA_EMAIL = class="hl-string">"your-email@example.com"
JIRA_API_TOKEN = os.environ.get(class="hl-string">"JIRA_API_TOKEN", class="hl-string">"YOUR_JIRA_API_TOKEN") class=class="hl-string">"hl-comment"># Best practice: use environment variables

class=class="hl-string">"hl-comment"># JQL query to fetch service request issues. 
class=class="hl-string">"hl-comment"># Recommended: Filter by project, issuetype, and a date range.
jql_query = class="hl-string">"project = 'SD' AND issuetype = 'Service Request' AND created >= '-6m' ORDER BY created DESC"

class=class="hl-string">"hl-comment"># Map your specific Jira status names to the desired activity names.
class=class="hl-string">"hl-comment"># This is CRITICAL for capturing the correct process flow.
STATUS_ACTIVITY_MAP = {
    class="hl-string">'In Progress': class="hl-string">'Request Triaged',
    class="hl-string">'Waiting for customer': class="hl-string">'Information Requested',
    class="hl-string">'Waiting for support': class="hl-string">'Information Provided',
    class="hl-string">'Waiting for vendor': class="hl-string">'Vendor Engagement Started',
    class="hl-string">'Vendor Responded': class="hl-string">'Vendor Engagement Ended',
    class="hl-string">'Pending Review': class="hl-string">'Solution Implemented',
    class="hl-string">'Pending Customer Acceptance': class="hl-string">'Resolution Proposed',
    class="hl-string">'Reopened': class="hl-string">'Request Reopened',
    class="hl-string">'Resolved': class="hl-string">'Resolution Confirmed', class=class="hl-string">"hl-comment"># Or could be class="hl-string">'Solution Implemented' depending on workflow
    class="hl-string">'Closed': class="hl-string">'Service Request Closed'
}

class=class="hl-string">"hl-comment"># Map desired attributes to their custom field IDs in Jira.
class=class="hl-string">"hl-comment"># Find these IDs in your Jira instance under Settings > Issues > Custom Fields.
CUSTOM_FIELD_IDS = {
    class="hl-string">'RequestType': class="hl-string">'[customfield_10021]', class=class="hl-string">"hl-comment"># Example ID for Request Type
    class="hl-string">'SlaDueDate': class="hl-string">'[customfield_10030]'    class=class="hl-string">"hl-comment"># Example ID for SLA Due Date
}

class=class="hl-string">"hl-comment"># --- SCRIPT LOGIC --- 
def fetch_jira_issues(url, email, token, jql):
    class="hl-string">"""Fetches all issues from Jira matching the JQL query, handling pagination."""
    headers = {
        class="hl-string">"Accept": class="hl-string">"application/json",
        class="hl-string">"Content-Type": class="hl-string">"application/json"
    }
    auth = (email, token)
    
    issues = []
    start_at = 0
    max_results = 50
    total = -1

    print(fclass="hl-string">"Fetching issues from Jira with JQL: {jql}")
    while total == -1 or start_at < total:
        api_url = fclass="hl-string">"{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'
        }
        
        response = requests.get(api_url, headers=headers, params=params, auth=auth)
        response.raise_for_status() class=class="hl-string">"hl-comment"># Exit if there is an error
        data = response.json()
        
        if total == -1:
            total = data[class="hl-string">'total']
            print(fclass="hl-string">"Found {total} total issues to process.")

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

        if len(page_issues) == 0:
            break

    return issues

def process_issues_to_event_log(issues):
    class="hl-string">"""Processes raw issue data into a structured event log."""
    event_log = []
    now_utc = datetime.now(timezone.utc).isoformat()
    source_system = class="hl-string">"Jira Service Management"

    for issue in issues:
        case_id = issue[class="hl-string">'key']
        fields = issue.get(class="hl-string">'fields', {})
        changelog = issue.get(class="hl-string">'changelog', {}).get(class="hl-string">'histories', [])
        
        class=class="hl-string">"hl-comment"># Helper to get nested field values safely
        def get_field_value(field_data, key):
            return field_data.get(key)
        
        def get_custom_field_value(field_data, custom_field_id):
            custom_field = field_data.get(custom_field_id)
            if isinstance(custom_field, dict):
                return custom_field.get(class="hl-string">'value')
            return custom_field

        class=class="hl-string">"hl-comment"># --- Extract common attributes for all events of this case ---
        assignee = get_field_value(fields.get(class="hl-string">'assignee', {}), class="hl-string">'displayName')
        priority = get_field_value(fields.get(class="hl-string">'priority', {}), class="hl-string">'name')
        status = get_field_value(fields.get(class="hl-string">'status', {}), class="hl-string">'name')
        request_type = get_custom_field_value(fields, CUSTOM_FIELD_IDS[class="hl-string">'RequestType'])
        sla_due_date = get_custom_field_value(fields, CUSTOM_FIELD_IDS[class="hl-string">'SlaDueDate'])

        common_attributes = {
            class="hl-string">'ServiceRequestId': case_id,
            class="hl-string">'SourceSystem': source_system,
            class="hl-string">'LastDataUpdate': now_utc,
            class="hl-string">'Assignee': assignee,
            class="hl-string">'RequestPriority': priority,
            class="hl-string">'RequestStatus': status,
            class="hl-string">'RequestType': request_type,
            class="hl-string">'SlaDueDate': sla_due_date
        }

        class=class="hl-string">"hl-comment"># 1. Activity: Service Request Created
        event_log.append({
            **common_attributes,
            class="hl-string">'ActivityName': class="hl-string">'Service Request Created',
            class="hl-string">'EventTime': fields[class="hl-string">'created']
        })

        class=class="hl-string">"hl-comment"># 2. Activity: Service Request Resolved
        if fields.get(class="hl-string">'resolutiondate'):
            event_log.append({
                **common_attributes,
                class="hl-string">'ActivityName': class="hl-string">'Service Request Resolved',
                class="hl-string">'EventTime': fields[class="hl-string">'resolutiondate']
            })

        class=class="hl-string">"hl-comment"># 3. Process changelog for all other activities
        for history in changelog:
            created_time = history[class="hl-string">'created']
            for item in history[class="hl-string">'items']:
                class=class="hl-string">"hl-comment"># Activity: Request Assigned
                if item[class="hl-string">'field'].lower() == class="hl-string">'assignee':
                    event_log.append({
                        **common_attributes,
                        class="hl-string">'ActivityName': class="hl-string">'Request Assigned',
                        class="hl-string">'EventTime': created_time,
                        class="hl-string">'Assignee': item.get(class="hl-string">'toString') class=class="hl-string">"hl-comment"># Use the new assignee for this event
                    })
                
                class=class="hl-string">"hl-comment"># Activities based on status changes
                if item[class="hl-string">'field'].lower() == class="hl-string">'status':
                    to_status = item.get(class="hl-string">'toString')
                    if to_status in STATUS_ACTIVITY_MAP:
                        activity = STATUS_ACTIVITY_MAP[to_status]
                        event_log.append({
                            **common_attributes,
                            class="hl-string">'ActivityName': activity,
                            class="hl-string">'EventTime': created_time,
                            class="hl-string">'RequestStatus': to_status class=class="hl-string">"hl-comment"># Use the new status for this event
                        })

    return event_log

if __name__ == class="hl-string">"__main__":
    try:
        issues_data = fetch_jira_issues(JIRA_URL, JIRA_EMAIL, JIRA_API_TOKEN, jql_query)
        if issues_data:
            event_log_data = process_issues_to_event_log(issues_data)
            df = pd.DataFrame(event_log_data)

            class=class="hl-string">"hl-comment"># Reorder and format columns for final output
            column_order = [
                class="hl-string">'ServiceRequestId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
                class="hl-string">'Assignee', class="hl-string">'RequestPriority', class="hl-string">'RequestStatus', class="hl-string">'RequestType', class="hl-string">'SlaDueDate'
            ]
            df = df[column_order]
            df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime']).dt.strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')

            output_filename = class="hl-string">'jira_event_log.csv'
            df.to_csv(output_filename, index=False)
            print(fclass="hl-string">"\nSuccessfully extracted {len(df)} events to {output_filename}")
        else:
            print(class="hl-string">"No issues found for the given JQL query.")

    except requests.exceptions.HTTPError as e:
        print(fclass="hl-string">"HTTP Error: {e.response.status_code} - {e.response.text}")
    except Exception as e:
        print(fclass="hl-string">"An unexpected error occurred: {e}")