변경 관리 데이터 템플릿

/* This is a configuration guide for creating widgets in Freshservice Analytics. 
Create one widget for each of the following 15 configurations. */

// Widget 1: Change Request Created
Widget.Module = "Changes"
Widget.Metric = "Count of Changes"
Widget.GroupBy = ["Change ID", "Created at"]
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "Created at": "EventTime"}

// Widget 2: Approval Requested
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Status history contains 'Awaiting Approval')"
Widget.GroupBy = ["Change ID", "First moved to awaiting approval at"]
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "First moved to awaiting approval at": "EventTime"}

// Widget 3: Risk Assessment Completed
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Risk is not empty)"
Widget.GroupBy = ["Change ID", "[Timestamp of first Risk update]"] // Note: This may require using 'Last Updated at' with a specific condition or a custom field.
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "[Timestamp of first Risk update]": "EventTime"}

// Widget 4: Note Added to Change
Widget.Module = "Ticket Activities"
Widget.Metric = "Count of Activities (Filtered: Activity Type is 'Note added' AND Parent Type is 'Change')"
Widget.GroupBy = ["Parent ID", "Performed at"]
Widget.ColumnsToExport = {"Parent ID": "ChangeRequestId", "Performed at": "EventTime"}

// Widget 5: Change Approved
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Status is 'Approved')"
Widget.GroupBy = ["Change ID", "Approved on"]
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "Approved on": "EventTime"}

// Widget 6: Change Rejected
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Status is 'Rejected')"
Widget.GroupBy = ["Change ID", "Last Updated at"]
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "Last Updated at": "EventTime"}

// Widget 7: Planning Completed
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Status history contains 'Pending Release')"
Widget.GroupBy = ["Change ID", "Last Updated at"]
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "Last Updated at": "EventTime"}

// Widget 8: Change Scheduled
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Scheduled Start Time is not empty)"
Widget.GroupBy = ["Change ID", "[Timestamp of first Schedule update]"] // Note: This often corresponds to the 'Last Updated at' when the schedule fields were populated.
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "[Timestamp of first Schedule update]": "EventTime"}

// Widget 9: Implementation Started
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Status is 'In Progress')"
Widget.GroupBy = ["Change ID", "Last Updated at"]
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "Last Updated at": "EventTime"}

// Widget 10: Implementation Completed
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Status history contains 'Pending Review')"
Widget.GroupBy = ["Change ID", "Last Updated at"]
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "Last Updated at": "EventTime"}

// Widget 11: Testing Completed
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Status history contains 'Pending Review' OR custom testing status)"
Widget.GroupBy = ["Change ID", "[Timestamp for status change to review]"] // Use 'Last Updated at' corresponding to the status change.
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "[Timestamp for status change to review]": "EventTime"}

// Widget 12: Post-Implementation Review Done
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Status is 'Closed' AND [PIR field is populated])"
Widget.GroupBy = ["Change ID", "Last Updated at"]
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "Last Updated at": "EventTime"}

// Widget 13: Change Re-Opened
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Status history contains 'Re-opened')"
Widget.GroupBy = ["Change ID", "Last Updated at"]
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "Last Updated at": "EventTime"}

// Widget 14: Change Closed
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Status is 'Closed')"
Widget.GroupBy = ["Change ID", "Closed at"]
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "Closed at": "EventTime"}

// Widget 15: Change Cancelled
Widget.Module = "Changes"
Widget.Metric = "Count of Changes (Filtered: Status is 'Cancelled' OR Status is 'Withdrawn')"
Widget.GroupBy = ["Change ID", "Last Updated at"]
Widget.ColumnsToExport = {"Change ID": "ChangeRequestId", "Last Updated at": "EventTime"}

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

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Freshservice domain (e.g., class="hl-string">'your-company.freshservice.com')
FRESHSERVICE_DOMAIN = class="hl-string">"[YOUR_DOMAIN]"
class=class="hl-string">"hl-comment"># Replace with your Freshservice API Key
API_KEY = class="hl-string">"[YOUR_API_KEY]"

class=class="hl-string">"hl-comment"># Filter for changes updated since a specific date (ISO 8601 format)
class=class="hl-string">"hl-comment"># Example: class="hl-string">'2023-01-01T00:00:00Z'
UPDATED_SINCE_FILTER = class="hl-string">"2023-01-01T00:00:00Z"

class=class="hl-string">"hl-comment"># --- Script --- 
BASE_URL = fclass="hl-string">"https://{FRESHSERVICE_DOMAIN}/api/v2"

def get_all_changes(updated_since):
    changes = []
    url = fclass="hl-string">"{BASE_URL}/changes?updated_since={updated_since}"
    page = 1
    while url:
        print(fclass="hl-string">"Fetching changes page {page}...")
        try:
            response = requests.get(url, auth=(API_KEY, class="hl-string">'X'))
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            data = response.json()
            changes.extend(data[class="hl-string">'changes'])
            class=class="hl-string">"hl-comment"># Freshservice uses the class="hl-string">'Link' header for pagination
            if class="hl-string">'Link' in response.headers:
                url = response.headers[class="hl-string">'Link'].split(class="hl-string">';')[0].strip(class="hl-string">'<>')
            else:
                url = None
            page += 1
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching changes: {e}")
            if response.status_code == 429: class=class="hl-string">"hl-comment"># Rate limit exceeded
                print(class="hl-string">"Rate limit exceeded. Waiting for 60 seconds...")
                time.sleep(60)
                continue class=class="hl-string">"hl-comment"># Retry the same URL
            else:
                url = None class=class="hl-string">"hl-comment"># Stop on other errors
    return changes

def get_change_activities(change_id):
    activities = []
    url = fclass="hl-string">"{BASE_URL}/changes/{change_id}/activities"
    while url:
        try:
            response = requests.get(url, auth=(API_KEY, class="hl-string">'X'))
            response.raise_for_status()
            data = response.json()
            activities.extend(data[class="hl-string">'activities'])
            if class="hl-string">'Link' in response.headers:
                url = response.headers[class="hl-string">'Link'].split(class="hl-string">';')[0].strip(class="hl-string">'<>')
            else:
                url = None
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching activities for change {change_id}: {e}")
            if response.status_code == 429: class=class="hl-string">"hl-comment"># Rate limit exceeded
                print(class="hl-string">"Rate limit exceeded. Waiting for 60 seconds...")
                time.sleep(60)
                continue class=class="hl-string">"hl-comment"># Retry the same URL
            else:
                url = None class=class="hl-string">"hl-comment"># Stop on other errors
    return activities

def main():
    print(class="hl-string">"Starting Freshservice data extraction...")
    all_changes = get_all_changes(UPDATED_SINCE_FILTER)
    event_log = []
    total_changes = len(all_changes)
    
    print(fclass="hl-string">"Found {total_changes} changes to process.")

    for index, change in enumerate(all_changes):
        print(fclass="hl-string">"Processing change {index + 1}/{total_changes}: ID {change['id']}")
        change_id = change[class="hl-string">'id']
        requester_name = change[class="hl-string">'requester'][class="hl-string">'first_name'] + class="hl-string">' ' + change[class="hl-string">'requester'][class="hl-string">'last_name'] if change.get(class="hl-string">'requester') else None
        assigned_group = change[class="hl-string">'group'][class="hl-string">'name'] if change.get(class="hl-string">'group') else None

        class=class="hl-string">"hl-comment"># Base attributes for all events of this case
        base_event = {
            class="hl-string">'ChangeRequestId': change_id,
            class="hl-string">'ChangeStatus': change.get(class="hl-string">'status_name'),
            class="hl-string">'ChangeType': change.get(class="hl-string">'change_type_name'),
            class="hl-string">'ChangePriority': change.get(class="hl-string">'priority_name'),
            class="hl-string">'RequesterName': requester_name,
            class="hl-string">'AssignedGroup': assigned_group,
            class="hl-string">'RiskLevel': change.get(class="hl-string">'risk_name'),
            class="hl-string">'TargetCompletionDate': change.get(class="hl-string">'planned_end_date'),
            class="hl-string">'SourceSystem': class="hl-string">'Freshservice',
            class="hl-string">'LastDataUpdate': datetime.now(timezone.utc).isoformat()
        }

        class=class="hl-string">"hl-comment"># 1. Change Request Created
        event_log.append({
            **base_event,
            class="hl-string">'ActivityName': class="hl-string">'Change Request Created',
            class="hl-string">'EventTime': change[class="hl-string">'created_at']
        })

        class=class="hl-string">"hl-comment"># Fetch and process activities
        activities = get_change_activities(change_id)
        for activity in activities:
            activity_event_time = activity[class="hl-string">'performed_at']

            class=class="hl-string">"hl-comment"># 4. Note Added to Change
            if activity[class="hl-string">'activity_type'] == class="hl-string">'Note Added':
                event_log.append({
                    **base_event,
                    class="hl-string">'ActivityName': class="hl-string">'Note Added to Change',
                    class="hl-string">'EventTime': activity_event_time
                })
            
            class=class="hl-string">"hl-comment"># 5. Change Approved & 6. Change Rejected
            if activity[class="hl-string">'activity_type'] == class="hl-string">'Approval' and class="hl-string">'status_name' in activity[class="hl-string">'activity_data']:
                if activity[class="hl-string">'activity_data'][class="hl-string">'status_name'] == class="hl-string">'Approved':
                    event_log.append({
                        **base_event,
                        class="hl-string">'ActivityName': class="hl-string">'Change Approved',
                        class="hl-string">'EventTime': activity_event_time
                    })
                elif activity[class="hl-string">'activity_data'][class="hl-string">'status_name'] == class="hl-string">'Rejected':
                    event_log.append({
                        **base_event,
                        class="hl-string">'ActivityName': class="hl-string">'Change Rejected',
                        class="hl-string">'EventTime': activity_event_time
                    })
            
            class=class="hl-string">"hl-comment"># Process status changes and field updates
            if class="hl-string">'changes' in activity[class="hl-string">'activity_data']:
                previous_status_id = activity[class="hl-string">'activity_data'][class="hl-string">'changes'].get(class="hl-string">'status', [None, None])[0]
                new_status_name = activity[class="hl-string">'activity_data'][class="hl-string">'changes'].get(class="hl-string">'status', [None, class="hl-string">''])[1]
                previous_status_name = change[class="hl-string">'status_name_label_mapping'].get(str(previous_status_id))

                class=class="hl-string">"hl-comment"># 2. Approval Requested
                if new_status_name == class="hl-string">'Awaiting Approval':
                    event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Approval Requested', class="hl-string">'EventTime': activity_event_time})
                
                class=class="hl-string">"hl-comment"># 3. Risk Assessment Completed
                if class="hl-string">'risk' in activity[class="hl-string">'activity_data'][class="hl-string">'changes']:
                    event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Risk Assessment Completed', class="hl-string">'EventTime': activity_event_time})

                class=class="hl-string">"hl-comment"># 7. Planning Completed (Inferred)
                if new_status_name == class="hl-string">'Open' and previous_status_name == class="hl-string">'Awaiting Approval':
                     event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Planning Completed', class="hl-string">'EventTime': activity_event_time})

                class=class="hl-string">"hl-comment"># 8. Change Scheduled (Inferred)
                if class="hl-string">'planned_start_date' in activity[class="hl-string">'activity_data'][class="hl-string">'changes'] or class="hl-string">'planned_end_date' in activity[class="hl-string">'activity_data'][class="hl-string">'changes']:
                     event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Change Scheduled', class="hl-string">'EventTime': activity_event_time})

                class=class="hl-string">"hl-comment"># 9. Implementation Started (Inferred)
                if new_status_name == class="hl-string">'In Progress':
                     event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Implementation Started', class="hl-string">'EventTime': activity_event_time})

                class=class="hl-string">"hl-comment"># 10. Implementation Completed (Inferred)
                if new_status_name == class="hl-string">'Pending Review':
                     event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Implementation Completed', class="hl-string">'EventTime': activity_event_time})

                class=class="hl-string">"hl-comment"># 11. Testing Completed (Inferred)
                if new_status_name == class="hl-string">'Testing Complete': class=class="hl-string">"hl-comment"># This status name may need customization
                     event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Testing Completed', class="hl-string">'EventTime': activity_event_time})
                
                class=class="hl-string">"hl-comment"># 12. Post-Implementation Review Done (Inferred)
                if new_status_name == class="hl-string">'Pending PIR' and previous_status_name != class="hl-string">'Closed': class=class="hl-string">"hl-comment"># Example status, might need adjustment
                     event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Post-Implementation Review Done', class="hl-string">'EventTime': activity_event_time})
                
                class=class="hl-string">"hl-comment"># 13. Change Re-Opened
                is_reopened = (previous_status_name in [class="hl-string">'Closed', class="hl-string">'Cancelled']) and (new_status_name not in [class="hl-string">'Closed', class="hl-string">'Cancelled'])
                if is_reopened:
                     event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Change Re-Opened', class="hl-string">'EventTime': activity_event_time})

                class=class="hl-string">"hl-comment"># 14. Change Closed
                if new_status_name == class="hl-string">'Closed':
                     event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Change Closed', class="hl-string">'EventTime': activity_event_time})

                class=class="hl-string">"hl-comment"># 15. Change Cancelled
                if new_status_name == class="hl-string">'Cancelled':
                     event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Change Cancelled', class="hl-string">'EventTime': activity_event_time})

    if not event_log:
        print(class="hl-string">"No events were generated. Check your filters and API permissions.")
        return

    df = pd.DataFrame(event_log)

    class=class="hl-string">"hl-comment"># Post-processing
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'])
    df = df.sort_values(by=[class="hl-string">'ChangeRequestId', class="hl-string">'EventTime']).drop_duplicates()

    class=class="hl-string">"hl-comment"># Calculate EndTime
    end_times = df.groupby(class="hl-string">'ChangeRequestId')[class="hl-string">'EventTime'].max().to_dict()
    df[class="hl-string">'EndTime'] = df[class="hl-string">'ChangeRequestId'].map(end_times)

    class=class="hl-string">"hl-comment"># Reorder columns
    column_order = [
        class="hl-string">'ChangeRequestId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate', class="hl-string">'EndTime',
        class="hl-string">'ChangeStatus', class="hl-string">'ChangeType', class="hl-string">'ChangePriority', class="hl-string">'RequesterName', class="hl-string">'AssignedGroup', class="hl-string">'RiskLevel',
        class="hl-string">'TargetCompletionDate'
    ]
    df = df[column_order]
    
    class=class="hl-string">"hl-comment"># Save to CSV
    output_file = class="hl-string">'freshservice_change_management_event_log.csv'
    df.to_csv(output_file, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%f%z')
    print(fclass="hl-string">"Extraction complete. Event log saved to {output_file}")

if __name__ == class="hl-string">'__main__':
    main()