Datová šablona pro řízení změn

This extraction is configured through the Cherwell Search Manager UI, not a single script. Below are the configurations for the two required searches.

**Search 1: ProcessMind - Change Creation**

*   **Business Object**: `Change Request`
*   **Search Criteria**:
    *   `Change Request.CreatedDateTime` between `[Specify Start Date]` and `[Specify End Date]`
*   **Grid Columns (Fields to Return)**:
    1.  `ChangeRequestId`: `Change Request.ChangeID`
    2.  `ActivityName`: Custom Expression -> `'Change Request Created'`
    3.  `EventTime`: `Change Request.CreatedDateTime`
    4.  `SourceSystem`: Custom Expression -> `'Ivanti Cherwell'`
    5.  `LastDataUpdate`: Custom Expression -> `SystemFunctions.CurrentDateTime()`
    6.  `ChangeStatus`: `Change Request.Status`
    7.  `ChangeType`: `Change Request.ChangeType`
    8.  `ChangeOwner`: `Change Request.OwnedBy`
    9.  `ChangeTeam`: `Change Request.OwnedByTeam`
    10. `ChangeRiskLevel`: `Change Request.Risk`
    11. `TargetCompletionDate`: `Change Request.ProposedEndDate`

**Search 2: ProcessMind - Change Status History**

*   **Business Object**: `Change Request Journal - History`
*   **Search Criteria**:
    *   `Change Request Journal - History.FieldName` equals `Status`
    *   `Change Request Journal - History.CreatedDateTime` between `[Specify Start Date]` and `[Specify End Date]`
*   **Grid Columns (Fields to Return)**:
    1.  `ChangeRequestId`: `Change Request.ChangeID` (from related Change Request)
    2.  `ActivityName`: Custom Expression -> Case expression on `Change Request Journal - History.NewValue`:
        ```
        CASE NewValue
          WHEN 'Assessing' THEN 'Change Submitted For Assessment'
          WHEN 'Awaiting Approval' THEN 'Impact and Risk Assessed'
          WHEN 'Pending Approval' THEN 'Change Awaiting Approval' -- Or 'Awaiting CAB' based on your config
          WHEN 'Approved' THEN 'Change Approved by CAB'
          WHEN 'Rejected' THEN 'Change Rejected'
          WHEN 'Scheduled' THEN 'Implementation Plan Developed' -- Assumes planning is done when status moves to Scheduled
          WHEN 'Scheduled' THEN 'Change Scheduled' -- Note: Two activities map to this status change. Create two case statements if needed or choose one.
          WHEN 'In Progress' THEN 'Change Implementation Started'
          WHEN 'Implemented' THEN 'Change Implemented'
          WHEN 'Verification' THEN 'Change Verification Performed'
          WHEN 'Post Implementation Review' THEN 'Post-Implementation Review Conducted'
          WHEN 'Closed' THEN 'Change Closed'
          WHEN 'Cancelled' THEN 'Change Cancelled'
          ELSE NULL
        END
        ```
    3.  `EventTime`: `Change Request Journal - History.CreatedDateTime`
    4.  `SourceSystem`: Custom Expression -> `'Ivanti Cherwell'`
    5.  `LastDataUpdate`: Custom Expression -> `SystemFunctions.CurrentDateTime()`
    6.  `ChangeStatus`: `Change Request.Status` (from related Change Request)
    7.  `ChangeType`: `Change Request.ChangeType` (from related Change Request)
    8.  `ChangeOwner`: `Change Request.OwnedBy` (from related Change Request)
    9.  `ChangeTeam`: `Change Request.OwnedByTeam` (from related Change Request)
    10. `ChangeRiskLevel`: `Change Request.Risk` (from related Change Request)
    11. `TargetCompletionDate`: `Change Request.ProposedEndDate` (from related Change Request)

/*
Ivanti Cherwell - Change Management Extraction Query
Adjust placeholders: @StartDate, @EndDate, [Your Change Request Business Object Type ID]
*/

-- =========================================================================================
-- 1. Configuration: Set the date range for the extraction
-- =========================================================================================
DECLARE @StartDate DATETIME = '2023-01-01 00:00:00';
DECLARE @EndDate DATETIME = '2024-01-01 00:00:00';

-- =========================================================================================
-- 2. Common Table Expression (CTE) to gather base change data and status changes
-- =========================================================================================
WITH BaseChanges AS (
    SELECT 
        cr.RecID, -- Internal Record ID for joining
        cr.ChangeID AS ChangeRequestId,
        cr.Status AS ChangeStatus,
        cr.ChangeType,
        cr.OwnedBy AS ChangeOwner,
        cr.OwnedByTeam AS ChangeTeam,
        cr.Risk AS ChangeRiskLevel,
        cr.ProposedEndDate AS TargetCompletionDate,
        cr.CreatedDateTime
    FROM 
        dbo.ChangeRequest cr
    WHERE 
        cr.CreatedDateTime >= @StartDate AND cr.CreatedDateTime < @EndDate
),
StatusChanges AS (
    SELECT
        j.ParentRecID, 
        j.JournalDateTime, 
        j.Details
    FROM 
        dbo.Journal j
    WHERE 
        j.JournalTypeName = 'Status Change' 
        AND j.ParentTypeID = '[Your Change Request Business Object Type ID]' -- Important: Find and set this ID
        AND j.JournalDateTime >= @StartDate
)

-- =========================================================================================
-- 3. Main Query: Union all activities into a single event log
-- =========================================================================================
SELECT
    bc.ChangeRequestId,
    'Change Request Created' AS ActivityName,
    bc.CreatedDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Change Submitted For Assessment' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%Assessing%'

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Impact and Risk Assessed' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%Awaiting Approval%'

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Change Awaiting Approval' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%Pending Approval%' OR sc.Details LIKE '%Awaiting CAB%'

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Change Approved by CAB' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%Approved%'

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Change Rejected' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%Rejected%'

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Implementation Plan Developed' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%Scheduled%'

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Change Scheduled' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%Scheduled%'

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Change Implementation Started' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%In Progress%' OR sc.Details LIKE '%Implementing%'

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Change Implemented' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%Implemented%'

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Change Verification Performed' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%Verification%' OR sc.Details LIKE '%Testing%'

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Post-Implementation Review Conducted' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%Review%'

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Change Closed' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%Closed%'

UNION ALL

SELECT
    bc.ChangeRequestId,
    'Change Cancelled' AS ActivityName,
    sc.JournalDateTime AS EventTime,
    'Ivanti Cherwell' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    bc.ChangeStatus,
    bc.ChangeType,
    bc.ChangeOwner,
    bc.ChangeTeam,
    bc.ChangeRiskLevel,
    bc.TargetCompletionDate
FROM BaseChanges bc
JOIN StatusChanges sc ON bc.RecID = sc.ParentRecID
WHERE sc.Details LIKE '%Cancelled%';

import requests
import pandas as pd
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration --- 
class=class="hl-string">"hl-comment"># Replace with your Cherwell instance details
CHERWELL_BASE_URL = class="hl-string">"https://[your-cherwell-instance.com]/CherwellAPI"
CLIENT_ID = class="hl-string">"[Your_Client_ID]"
API_USERNAME = class="hl-string">"[Your_API_User]"
API_PASSWORD = class="hl-string">"[Your_API_Password]"

class=class="hl-string">"hl-comment"># Replace with your environment's specific Business Object IDs
class=class="hl-string">"hl-comment"># Find these in the Cherwell Administrator client under Business Object properties
CHANGE_REQUEST_BUSOBID = class="hl-string">"[Your_Change_Request_BusObId]"      class=class="hl-string">"hl-comment"># e.g., class="hl-string">'934ec7a1701c451ce5c234459494a7428f522850f2'
JOURNAL_HISTORY_BUSOBID = class="hl-string">"[Your_Journal_History_BusObId]"  class=class="hl-string">"hl-comment"># e.g., class="hl-string">'934e85d1e67b931a2471a44781b0a8501223961895'

class=class="hl-string">"hl-comment"># Date range for the extraction (ISO 8601 format)
START_DATE = class="hl-string">"2023-01-01T00:00:00"
END_DATE = class="hl-string">"2023-12-31T23:59:59"

class=class="hl-string">"hl-comment"># --- Mappings --- 
class=class="hl-string">"hl-comment"># Maps status transitions from the Journal to standard Activity Names.
class=class="hl-string">"hl-comment"># Format: class="hl-string">'new_status': class="hl-string">'ActivityName'
class=class="hl-string">"hl-comment"># This mapping is crucial and must be adapted to your specific Cherwell workflow.
STATUS_TO_ACTIVITY_MAP = {
    class="hl-string">"Assessing": class="hl-string">"Change Submitted For Assessment",
    class="hl-string">"Awaiting Approval": class="hl-string">"Impact and Risk Assessed",
    class="hl-string">"Pending Approval": class="hl-string">"Change Awaiting Approval",
    class="hl-string">"Approved": class="hl-string">"Change Approved by CAB",
    class="hl-string">"Rejected": class="hl-string">"Change Rejected",
    class="hl-string">"Scheduled": class="hl-string">"Implementation Plan Developed", class=class="hl-string">"hl-comment"># Inferred when moving from Approved to Scheduled
    class=class="hl-string">"hl-comment"># A separate logic can be added for a distinct class="hl-string">'Change Scheduled' activity if a specific field is updated
    class="hl-string">"In Progress": class="hl-string">"Change Implementation Started",
    class="hl-string">"Implementing": class="hl-string">"Change Implementation Started",
    class="hl-string">"Implemented": class="hl-string">"Change Implemented",
    class="hl-string">"Verification": class="hl-string">"Change Verification Performed",
    class="hl-string">"Testing": class="hl-string">"Change Verification Performed",
    class="hl-string">"Post Implementation Review": class="hl-string">"Post-Implementation Review Conducted",
    class="hl-string">"Closed": class="hl-string">"Change Closed",
    class="hl-string">"Cancelled": class="hl-string">"Change Cancelled"
}

def get_access_token():
    class="hl-string">"""Authenticates with Cherwell API to get a bearer token."""
    token_url = fclass="hl-string">"{CHERWELL_BASE_URL}/token"
    payload = {
        class="hl-string">'grant_type': class="hl-string">'password',
        class="hl-string">'client_id': CLIENT_ID,
        class="hl-string">'username': API_USERNAME,
        class="hl-string">'password': API_PASSWORD
    }
    headers = {class="hl-string">'Content-Type': class="hl-string">'application/x-www-form-urlencoded'}
    response = requests.post(token_url, data=payload, headers=headers, verify=True)
    response.raise_for_status()
    return response.json()[class="hl-string">'access_token']

def get_paginated_results(token, busObId, payload):
    class="hl-string">"""Handles pagination to fetch all search results from Cherwell."""
    all_records = []
    pageNumber = 0
    while True:
        payload[class="hl-string">'pageNumber'] = pageNumber
        search_url = fclass="hl-string">"{CHERWELL_BASE_URL}/api/V1/getsearchresults"
        headers = {class="hl-string">'Authorization': fclass="hl-string">'Bearer {token}'}
        response = requests.post(search_url, headers=headers, json=payload)
        response.raise_for_status()
        data = response.json()
        
        records = data.get(class="hl-string">'businessObjects', [])
        all_records.extend(records)
        
        if not records or len(records) < payload.get(class="hl-string">'pageSize', 100):
            break
        pageNumber += 1
    return all_records

def extract_cherwell_change_data():
    class="hl-string">"""Main function to extract, transform, and save Cherwell change data."""
    print(class="hl-string">"Authenticating with Cherwell...")
    token = get_access_token()
    event_log_data = []
    
    class=class="hl-string">"hl-comment"># 1. Extract class="hl-string">'Change Request Created' event
    print(class="hl-string">"Extracting 'Created' events...")
    created_payload = {
        class="hl-string">"busObId": CHANGE_REQUEST_BUSOBID,
        class="hl-string">"fields": [class="hl-string">"ChangeID", class="hl-string">"CreatedDateTime", class="hl-string">"Status", class="hl-string">"ChangeType", class="hl-string">"OwnedBy", class="hl-string">"OwnedByTeam", class="hl-string">"Risk", class="hl-string">"ScheduledImplementationDate"],
        class="hl-string">"filters": [
            {
                class="hl-string">"fieldId": class="hl-string">"CreatedDateTime",
                class="hl-string">"operator": class="hl-string">"gt",
                class="hl-string">"value": START_DATE
            },
            {
                class="hl-string">"fieldId": class="hl-string">"CreatedDateTime",
                class="hl-string">"operator": class="hl-string">"lt",
                class="hl-string">"value": END_DATE
            }
        ],
        class="hl-string">"pageSize": 1000
    }
    
    change_requests = get_paginated_results(token, CHANGE_REQUEST_BUSOBID, created_payload)
    
    for cr in change_requests:
        fields = {f[class="hl-string">'name']: f[class="hl-string">'value'] for f in cr[class="hl-string">'fields']}
        event_log_data.append({
            class="hl-string">'ChangeRequestId': fields.get(class="hl-string">'ChangeID'),
            class="hl-string">'ActivityName': class="hl-string">'Change Request Created',
            class="hl-string">'EventTime': fields.get(class="hl-string">'CreatedDateTime'),
            class="hl-string">'ChangeStatus': fields.get(class="hl-string">'Status'),
            class="hl-string">'ChangeType': fields.get(class="hl-string">'ChangeType'),
            class="hl-string">'ChangeOwner': fields.get(class="hl-string">'OwnedBy'),
            class="hl-string">'ChangeTeam': fields.get(class="hl-string">'OwnedByTeam'),
            class="hl-string">'ChangeRiskLevel': fields.get(class="hl-string">'Risk'),
            class="hl-string">'TargetCompletionDate': fields.get(class="hl-string">'ScheduledImplementationDate')
        })

    class=class="hl-string">"hl-comment"># 2. Extract status change events from the Journal
    print(class="hl-string">"Extracting status change events from Journal...")
    journal_payload = {
        class="hl-string">"busObId": JOURNAL_HISTORY_BUSOBID,
        class="hl-string">"fields": [class="hl-string">"ParentID", class="hl-string">"CreatedDateTime", class="hl-string">"Details", class="hl-string">"JournalTypeName"],
        class="hl-string">"filters": [
            {
                class="hl-string">"fieldId": class="hl-string">"ParentTypeID",
                class="hl-string">"operator": class="hl-string">"eq",
                class="hl-string">"value": CHANGE_REQUEST_BUSOBID
            },
            {
                class="hl-string">"fieldId": class="hl-string">"JournalTypeName",
                class="hl-string">"operator": class="hl-string">"eq",
                class="hl-string">"value": class="hl-string">"History"
            },
            {
                class="hl-string">"fieldId": class="hl-string">"Details",
                class="hl-string">"operator": class="hl-string">"contains",
                class="hl-string">"value": class="hl-string">"Status changed"
            },
            {
                class="hl-string">"fieldId": class="hl-string">"CreatedDateTime",
                class="hl-string">"operator": class="hl-string">"gt",
                class="hl-string">"value": START_DATE
            },
            {
                class="hl-string">"fieldId": class="hl-string">"CreatedDateTime",
                class="hl-string">"operator": class="hl-string">"lt",
                class="hl-string">"value": END_DATE
            }
        ],
        class="hl-string">"pageSize": 1000
    }
    
    journal_entries = get_paginated_results(token, JOURNAL_HISTORY_BUSOBID, journal_payload)

    class=class="hl-string">"hl-comment"># Add a separate logic for the class="hl-string">'Change Scheduled' event if needed
    class=class="hl-string">"hl-comment"># This may require a different journal query if it is not a status change.
    class=class="hl-string">"hl-comment"># For this script, we infer it from the transition to the class="hl-string">'Scheduled' status.
    
    for entry in journal_entries:
        fields = {f[class="hl-string">'name']: f[class="hl-string">'value'] for f in entry[class="hl-string">'fields']}
        details = fields.get(class="hl-string">'Details', class="hl-string">'')
        class=class="hl-string">"hl-comment"># Example class="hl-string">'Details' format: class="hl-string">'Status changed from Approved to Scheduled.'
        if class="hl-string">'Status changed from' in details:
            try:
                parts = details.split(class="hl-string">' ')
                new_status = parts[-1].replace(class="hl-string">'.', class="hl-string">'')
                
                if new_status in STATUS_TO_ACTIVITY_MAP:
                    activity_name = STATUS_TO_ACTIVITY_MAP[new_status]
                    
                    class=class="hl-string">"hl-comment"># Special handling for class="hl-string">'Change Scheduled'
                    if new_status == class="hl-string">'Scheduled' and class="hl-string">'Approved' in details:
                        event_log_data.append({
                            class="hl-string">'ChangeRequestId': fields.get(class="hl-string">'ParentID'),
                            class="hl-string">'ActivityName': class="hl-string">'Implementation Plan Developed',
                            class="hl-string">'EventTime': fields.get(class="hl-string">'CreatedDateTime')
                        })
                        activity_name = class="hl-string">'Change Scheduled'
                        
                    event_log_data.append({
                        class="hl-string">'ChangeRequestId': fields.get(class="hl-string">'ParentID'),
                        class="hl-string">'ActivityName': activity_name,
                        class="hl-string">'EventTime': fields.get(class="hl-string">'CreatedDateTime')
                    })
            except IndexError:
                continue class=class="hl-string">"hl-comment"># Ignore malformed Details string

    if not event_log_data:
        print(class="hl-string">"No data found for the specified criteria.")
        return

    class=class="hl-string">"hl-comment"># 3. Create and process the DataFrame
    print(class="hl-string">"Processing data into event log format...")
    df = pd.DataFrame(event_log_data)
    
    class=class="hl-string">"hl-comment"># Convert timestamps to consistent format
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'], errors=class="hl-string">'coerce').dt.strftime(class="hl-string">'%Y-%m-%dT%H:%M:%S')
    df.dropna(subset=[class="hl-string">'ChangeRequestId', class="hl-string">'EventTime'], inplace=True)

    class=class="hl-string">"hl-comment"># Fill forward recommended attributes to associate them with all activities
    df = df.sort_values(by=[class="hl-string">'ChangeRequestId', class="hl-string">'EventTime'])
    recommended_attrs = [class="hl-string">'ChangeStatus', class="hl-string">'ChangeType', class="hl-string">'ChangeOwner', class="hl-string">'ChangeTeam', class="hl-string">'ChangeRiskLevel', class="hl-string">'TargetCompletionDate']
    df[recommended_attrs] = df.groupby(class="hl-string">'ChangeRequestId')[recommended_attrs].fillna(method=class="hl-string">'ffill')
    df[recommended_attrs] = df.groupby(class="hl-string">'ChangeRequestId')[recommended_attrs].fillna(method=class="hl-string">'bfill')

    class=class="hl-string">"hl-comment"># Add static columns
    df[class="hl-string">'SourceSystem'] = class="hl-string">'Ivanti Cherwell'
    df[class="hl-string">'LastDataUpdate'] = datetime.utcnow().strftime(class="hl-string">'%Y-%m-%dT%H:%M:%S')

    class=class="hl-string">"hl-comment"># Reorder columns to match schema
    final_columns = [
        class="hl-string">'ChangeRequestId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'ChangeStatus', class="hl-string">'ChangeType', class="hl-string">'ChangeOwner', class="hl-string">'ChangeTeam', class="hl-string">'ChangeRiskLevel', class="hl-string">'TargetCompletionDate'
    ]
    df = df[final_columns]

    class=class="hl-string">"hl-comment"># 4. Save to CSV
    output_file = class="hl-string">'cherwell_change_management_event_log.csv'
    df.to_csv(output_file, index=False)
    print(fclass="hl-string">"Extraction complete. Event log saved to {output_file}")

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