数据模板：事件管理

// This is a conceptual configuration for the Freshservice Analytics report builder, not an executable script.
// The logic should be implemented using the UI's formula fields and widget settings.

REPORT_WIDGET_TYPE: Table

DATA_SOURCES:
  - Primary: Incidents
  - Additional: Ticket Activities

COLUMNS_AND_DERIVATION_LOGIC:

1.  **IncidentId**
    - Source: Incidents."ID"

2.  **ActivityName** (Calculated Column using a Formula)
    - This formula combines multiple IF conditions to generate the activity name based on the activity type and data changes. The logic below represents a CASE statement structure.
    - **WHEN** Activity Type is 'Created': 'Incident Reported'
    - **WHEN** Activity Field is 'Group' and it is the first update to this field for the ticket: 'Incident Assigned to Group'
    - **WHEN** Activity Field is 'Agent' and it is the first update to this field for the ticket: 'Agent Assigned to Incident'
    - **WHEN** Activity Field is 'Priority': 'Incident Prioritized'
    - **WHEN** Activity Type is 'First Response Sent': 'First Response Sent'
    - **WHEN** Activity Field is 'Group' and it is NOT the first update: 'Incident Reassigned'
    - **WHEN** Activity Field is 'Agent' and it is NOT the first update: 'Incident Reassigned'
    - **WHEN** Activity Field is 'Status' and New Value is 'In Progress': 'Status Changed to In Progress'
    - **WHEN** Activity Field is 'Status' and New Value is 'Pending': 'Status Changed to Pending'
    - **WHEN** Activity Note contains 'Workaround Provided' or a specific workaround tag is added: 'Workaround Provided'
    - **WHEN** Activity Type is 'Resolution Note Added': 'Resolution Note Added'
    - **WHEN** Activity Field is 'Status' and New Value is 'Resolved': 'Incident Resolved'
    - **WHEN** Activity Type is 'SLA Violated' and SLA Type is 'Resolution': 'SLA Target Breached'
    - **WHEN** Activity Field is 'Status' and Old Value was 'Resolved' and New Value is 'Open': 'Incident Reopened'
    - **WHEN** Activity Field is 'Status' and New Value is 'Closed': 'Incident Closed'

3.  **EventTimestamp** (Calculated Column using a Formula)
    - **IF** ActivityName is 'Incident Reported', THEN use Incidents."Created at"
    - **ELSE**, use Ticket Activities."Performed at"

4.  **SourceSystem** (Calculated Column)
    - Value: 'Freshservice'

5.  **LastDataUpdate** (Calculated Column)
    - Value: Use a formula function that returns the current timestamp of the report execution.

6.  **AssignedAgent**
    - Source: Ticket Activities."Performed By" if available, otherwise the current Incidents."Agent"

7.  **AssignedGroup**
    - Source: The value of the 'Group' field at the time of the activity.

8.  **IncidentPriority**
    - Source: The value of the 'Priority' field at the time of the activity.

9.  **IncidentSeverity**
    - Source: The value of the 'Severity' field at the time of the activity.

10. **IncidentStatus**
    - Source: The new value from the 'Status' field change activity.

11. **IncidentCategory**
    - Source: The value of the 'Category' field at the time of the activity.

12. **ResolutionSlaTargetTime**
    - Source: Incidents."Resolution due by"

REPORT_FILTERS:
  - Incidents."Created at" BETWEEN '[Specify Start Date]' AND '[Specify End Date]'
  - Incidents."Type" EQUALS 'Incident'

import requests
import pandas as pd
import time
import datetime
import os

class=class="hl-string">"hl-comment"># --- Configuration Settings ---
class=class="hl-string">"hl-comment"># Replace with your Freshservice domain
FRESHSERVICE_DOMAIN = class="hl-string">"[your_domain].freshservice.com"
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"># Set the start date for the extraction (YYYY-MM-DD)
START_DATE_STR = class="hl-string">"2023-01-01"

class=class="hl-string">"hl-comment"># --- Constants ---
BASE_URL = fclass="hl-string">"https протокол://{FRESHSERVICE_DOMAIN}/api/v2"
PAGE_SIZE = 100
OUTPUT_FILENAME = class="hl-string">"freshservice_incident_event_log.csv"

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def create_event(incident_id, activity_name, event_timestamp, ticket_data, source_system, last_data_update):
    class="hl-string">"""Helper function to create a structured event dictionary."""
    class=class="hl-string">"hl-comment"># Use latest known values for attributes if available
    sla_policy = ticket_data.get(class="hl-string">'sla_policy', {})
    resolution_due = None
    if sla_policy and sla_policy.get(class="hl-string">'resolution_sla', {}):
        resolution_due = sla_policy.get(class="hl-string">'resolution_sla', {}).get(class="hl-string">'due_by')

    return {
        class="hl-string">"IncidentId": incident_id,
        class="hl-string">"ActivityName": activity_name,
        class="hl-string">"EventTimestamp": event_timestamp,
        class="hl-string">"SourceSystem": source_system,
        class="hl-string">"LastDataUpdate": last_data_update,
        class="hl-string">"AssignedAgent": ticket_data.get(class="hl-string">'responder_id'),
        class="hl-string">"AssignedGroup": ticket_data.get(class="hl-string">'group_id'),
        class="hl-string">"IncidentPriority": ticket_data.get(class="hl-string">'priority'),
        class="hl-string">"IncidentSeverity": ticket_data.get(class="hl-string">'impact'), class=class="hl-string">"hl-comment"># Impact is often used for Severity
        class="hl-string">"IncidentStatus": ticket_data.get(class="hl-string">'status'),
        class="hl-string">"IncidentCategory": ticket_data.get(class="hl-string">'category'),
        class="hl-string">"ResolutionSlaTargetTime": resolution_due
    }

def get_all_tickets(session, start_date):
    class="hl-string">"""Fetches all tickets from Freshservice updated since the start date, handling pagination."""
    tickets = []
    page = 1
    while True:
        params = {
            class="hl-string">"updated_since": start_date + class="hl-string">"T00:00:00Z",
            class="hl-string">"per_page": PAGE_SIZE,
            class="hl-string">"page": page,
            class="hl-string">"order_type": class="hl-string">"asc" class=class="hl-string">"hl-comment"># Process tickets in chronological order
        }
        try:
            response = session.get(fclass="hl-string">"{BASE_URL}/tickets", params=params)
            response.raise_for_status()
            data = response.json().get(class="hl-string">"tickets", [])
            if not data:
                break
            tickets.extend(data)
            print(fclass="hl-string">"Fetched page {page}, {len(data)} tickets...")
            page += 1
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching tickets: {e}")
            break
    return tickets

def get_ticket_activities(session, ticket_id):
    class="hl-string">"""Fetches all activities (audit log) for a specific ticket."""
    try:
        response = session.get(fclass="hl-string">"{BASE_URL}/tickets/{ticket_id}/activities")
        response.raise_for_status()
        return response.json().get(class="hl-string">"activities", [])
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Error fetching activities for ticket {ticket_id}: {e}")
        return []

def main():
    class="hl-string">"""Main function to run the extraction process."""
    session = requests.Session()
    session.auth = (API_KEY, class="hl-string">'X') class=class="hl-string">"hl-comment"># class="hl-string">'X' is a placeholder password
    session.headers.update({class="hl-string">'Content-Type': class="hl-string">'application/json'})

    last_data_update = datetime.datetime.now(datetime.timezone.utc).isoformat()
    source_system = class="hl-string">'Freshservice'
    event_log = []

    print(class="hl-string">"Starting Freshservice incident extraction...")
    all_tickets = get_all_tickets(session, START_DATE_STR)
    print(fclass="hl-string">"Total tickets to process: {len(all_tickets)}")

    for i, ticket in enumerate(all_tickets):
        incident_id = ticket[class="hl-string">'id']
        print(fclass="hl-string">"Processing ticket {i+1}/{len(all_tickets)}: ID {incident_id}")

        class=class="hl-string">"hl-comment"># 1. Incident Reported
        event_log.append(create_event(incident_id, class="hl-string">"Incident Reported", ticket[class="hl-string">'created_at'], ticket, source_system, last_data_update))

        class=class="hl-string">"hl-comment"># Fetch detailed activities for more events
        activities = get_ticket_activities(session, incident_id)
        
        assigned_group_done = False
        agent_assigned_done = False
        first_response_done = False

        for activity in sorted(activities, key=lambda x: x[class="hl-string">'performed_at']):
            activity_ts = activity[class="hl-string">'performed_at']
            class=class="hl-string">"hl-comment"># 2. Incident Assigned to Group (first occurrence)
            if not assigned_group_done and activity[class="hl-string">'activity_type'] == class="hl-string">'Update' and class="hl-string">'group_id' in activity.get(class="hl-string">'changes', {}):
                event_log.append(create_event(incident_id, class="hl-string">"Incident Assigned to Group", activity_ts, ticket, source_system, last_data_update))
                assigned_group_done = True
            
            class=class="hl-string">"hl-comment"># 3. Agent Assigned to Incident (first occurrence)
            if not agent_assigned_done and activity[class="hl-string">'activity_type'] == class="hl-string">'Update' and class="hl-string">'responder_id' in activity.get(class="hl-string">'changes', {}):
                event_log.append(create_event(incident_id, class="hl-string">"Agent Assigned to Incident", activity_ts, ticket, source_system, last_data_update))
                agent_assigned_done = True

            class=class="hl-string">"hl-comment"># 4. Incident Prioritized
            if activity[class="hl-string">'activity_type'] == class="hl-string">'Update' and class="hl-string">'priority' in activity.get(class="hl-string">'changes', {}):
                event_log.append(create_event(incident_id, class="hl-string">"Incident Prioritized", activity_ts, ticket, source_system, last_data_update))

            class=class="hl-string">"hl-comment"># 5. First Response Sent (first public note by an agent)
            if not first_response_done and activity[class="hl-string">'activity_type'] == class="hl-string">'Note' and activity.get(class="hl-string">'note', {}).get(class="hl-string">'private') is False and activity.get(class="hl-string">'performer_type') == class="hl-string">'User':
                event_log.append(create_event(incident_id, class="hl-string">"First Response Sent", activity_ts, ticket, source_system, last_data_update))
                first_response_done = True

            class=class="hl-string">"hl-comment"># 6. Incident Reassigned (subsequent assignments)
            if (assigned_group_done and activity[class="hl-string">'activity_type'] == class="hl-string">'Update' and class="hl-string">'group_id' in activity.get(class="hl-string">'changes', {})) or \
               (agent_assigned_done and activity[class="hl-string">'activity_type'] == class="hl-string">'Update' and class="hl-string">'responder_id' in activity.get(class="hl-string">'changes', {})):
                event_log.append(create_event(incident_id, class="hl-string">"Incident Reassigned", activity_ts, ticket, source_system, last_data_update))

            class=class="hl-string">"hl-comment"># Status Changes
            if activity[class="hl-string">'activity_type'] == class="hl-string">'Update' and class="hl-string">'status' in activity.get(class="hl-string">'changes', {}):
                new_status = activity[class="hl-string">'changes'][class="hl-string">'status'][1]
                class=class="hl-string">"hl-comment"># 7. Status Changed to In Progress
                if new_status == 3: class=class="hl-string">"hl-comment"># Assuming 3 is class="hl-string">'In Progress'
                    event_log.append(create_event(incident_id, class="hl-string">"Status Changed to In Progress", activity_ts, ticket, source_system, last_data_update))
                class=class="hl-string">"hl-comment"># 8. Status Changed to Pending
                elif new_status == 6: class=class="hl-string">"hl-comment"># Assuming 6 is class="hl-string">'Pending'
                    event_log.append(create_event(incident_id, class="hl-string">"Status Changed to Pending", activity_ts, ticket, source_system, last_data_update))

            class=class="hl-string">"hl-comment"># 9. Workaround Provided (Keyword search in notes)
            if activity[class="hl-string">'activity_type'] == class="hl-string">'Note' and class="hl-string">'workaround' in activity.get(class="hl-string">'note', {}).get(class="hl-string">'body_text', class="hl-string">'').lower():
                event_log.append(create_event(incident_id, class="hl-string">"Workaround Provided", activity_ts, ticket, source_system, last_data_update))
            
            class=class="hl-string">"hl-comment"># 10. Resolution Note Added
            if activity[class="hl-string">'activity_type'] == class="hl-string">'Note' and activity.get(class="hl-string">'note', {}).get(class="hl-string">'note_type') == class="hl-string">'resolution':
                event_log.append(create_event(incident_id, class="hl-string">"Resolution Note Added", activity_ts, ticket, source_system, last_data_update))

        class=class="hl-string">"hl-comment"># 11. Incident Resolved
        if ticket.get(class="hl-string">'resolved_at'):
            event_log.append(create_event(incident_id, class="hl-string">"Incident Resolved", ticket[class="hl-string">'resolved_at'], ticket, source_system, last_data_update))

        class=class="hl-string">"hl-comment"># 12. SLA Target Breached
        if ticket.get(class="hl-string">'sla_policy') and ticket[class="hl-string">'sla_policy'].get(class="hl-string">'violation_details', {}).get(class="hl-string">'resolution_sla', {}).get(class="hl-string">'is_violated'):
            breach_time = ticket[class="hl-string">'sla_policy'][class="hl-string">'violation_details'][class="hl-string">'resolution_sla'][class="hl-string">'due_by']
            event_log.append(create_event(incident_id, class="hl-string">"SLA Target Breached", breach_time, ticket, source_system, last_data_update))

        class=class="hl-string">"hl-comment"># 13. Incident Reopened
        if ticket.get(class="hl-string">'reopened'):
            class=class="hl-string">"hl-comment"># Finding the exact reopened timestamp can be tricky, we approximate from class="hl-string">'stats'
            reopen_ts = ticket.get(class="hl-string">'stats', {}).get(class="hl-string">'reopened_at', ticket[class="hl-string">'updated_at'])
            event_log.append(create_event(incident_id, class="hl-string">"Incident Reopened", reopen_ts, ticket, source_system, last_data_update))

        class=class="hl-string">"hl-comment"># 14. Incident Closed
        if ticket.get(class="hl-string">'closed_at'):
            event_log.append(create_event(incident_id, class="hl-string">"Incident Closed", ticket[class="hl-string">'closed_at'], ticket, source_system, last_data_update))

        class=class="hl-string">"hl-comment"># Be respectful of API rate limits
        time.sleep(1)

    class=class="hl-string">"hl-comment"># Convert to DataFrame and save
    if event_log:
        df = pd.DataFrame(event_log)
        df[class="hl-string">'EventTimestamp'] = pd.to_datetime(df[class="hl-string">'EventTimestamp'])
        df = df.sort_values(by=[class="hl-string">'IncidentId', class="hl-string">'EventTimestamp'])
        df.to_csv(OUTPUT_FILENAME, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%f%z')
        print(fclass="hl-string">"\nSuccessfully extracted {len(df)} events for {df['IncidentId'].nunique()} incidents.")
        print(fclass="hl-string">"Event log saved to {OUTPUT_FILENAME}")
    else:
        print(class="hl-string">"No events were generated.")

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

// This is a conceptual configuration for the Freshservice Analytics report builder, not an executable script.
// The logic should be implemented using the UI's formula fields and widget settings.

REPORT_WIDGET_TYPE: Table

DATA_SOURCES:
  - Primary: Incidents
  - Additional: Ticket Activities

COLUMNS_AND_DERIVATION_LOGIC:

1.  **IncidentId**
    - Source: Incidents."ID"

2.  **ActivityName** (Calculated Column using a Formula)
    - This formula combines multiple IF conditions to generate the activity name based on the activity type and data changes. The logic below represents a CASE statement structure.
    - **WHEN** Activity Type is 'Created': 'Incident Reported'
    - **WHEN** Activity Field is 'Group' and it is the first update to this field for the ticket: 'Incident Assigned to Group'
    - **WHEN** Activity Field is 'Agent' and it is the first update to this field for the ticket: 'Agent Assigned to Incident'
    - **WHEN** Activity Field is 'Priority': 'Incident Prioritized'
    - **WHEN** Activity Type is 'First Response Sent': 'First Response Sent'
    - **WHEN** Activity Field is 'Group' and it is NOT the first update: 'Incident Reassigned'
    - **WHEN** Activity Field is 'Agent' and it is NOT the first update: 'Incident Reassigned'
    - **WHEN** Activity Field is 'Status' and New Value is 'In Progress': 'Status Changed to In Progress'
    - **WHEN** Activity Field is 'Status' and New Value is 'Pending': 'Status Changed to Pending'
    - **WHEN** Activity Note contains 'Workaround Provided' or a specific workaround tag is added: 'Workaround Provided'
    - **WHEN** Activity Type is 'Resolution Note Added': 'Resolution Note Added'
    - **WHEN** Activity Field is 'Status' and New Value is 'Resolved': 'Incident Resolved'
    - **WHEN** Activity Type is 'SLA Violated' and SLA Type is 'Resolution': 'SLA Target Breached'
    - **WHEN** Activity Field is 'Status' and Old Value was 'Resolved' and New Value is 'Open': 'Incident Reopened'
    - **WHEN** Activity Field is 'Status' and New Value is 'Closed': 'Incident Closed'

3.  **EventTimestamp** (Calculated Column using a Formula)
    - **IF** ActivityName is 'Incident Reported', THEN use Incidents."Created at"
    - **ELSE**, use Ticket Activities."Performed at"

4.  **SourceSystem** (Calculated Column)
    - Value: 'Freshservice'

5.  **LastDataUpdate** (Calculated Column)
    - Value: Use a formula function that returns the current timestamp of the report execution.

6.  **AssignedAgent**
    - Source: Ticket Activities."Performed By" if available, otherwise the current Incidents."Agent"

7.  **AssignedGroup**
    - Source: The value of the 'Group' field at the time of the activity.

8.  **IncidentPriority**
    - Source: The value of the 'Priority' field at the time of the activity.

9.  **IncidentSeverity**
    - Source: The value of the 'Severity' field at the time of the activity.

10. **IncidentStatus**
    - Source: The new value from the 'Status' field change activity.

11. **IncidentCategory**
    - Source: The value of the 'Category' field at the time of the activity.

12. **ResolutionSlaTargetTime**
    - Source: Incidents."Resolution due by"

REPORT_FILTERS:
  - Incidents."Created at" BETWEEN '[Specify Start Date]' AND '[Specify End Date]'
  - Incidents."Type" EQUALS 'Incident'