顧客サービスデータテンプレート

DECLARE @StartDate AS DATETIME = '2023-01-01T00:00:00';
DECLARE @EndDate AS DATETIME = '2024-01-01T00:00:00';

WITH BaseCases AS (
    SELECT 
        i.incidentid,
        i.ticketnumber,
        i.createdon,
        i.prioritycode,
        i.caseorigincode,
        i.statuscode,
        i.subjectidname AS servicerequesttype,
        acc.name AS customername,
        own.fullname AS ownername
    FROM 
        incident i
    LEFT JOIN 
        account acc ON i.customerid = acc.accountid
    LEFT JOIN 
        systemuser own ON i.ownerid = own.systemuserid
    WHERE
        i.createdon >= @StartDate AND i.createdon < @EndDate
)
-- 1. Case Created
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Case Created' AS ActivityName,
    bc.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    BaseCases bc
JOIN 
    systemuser u ON bc.createdon = u.createdon AND bc.ownername = u.fullname

UNION ALL

-- 2. Case Assigned & 8. Case Escalated
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Case Assigned' AS ActivityName, -- Note: This logic captures all owner changes. You may need to add filters on NewOwner.fullname to identify escalations specifically.
    a.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    audit a
JOIN
    BaseCases bc ON a.objectid = bc.incidentid
JOIN
    systemuser u ON a.userid = u.systemuserid
WHERE
    a.objecttypecode = 112 -- Incident entity
    AND a.attributename = 'ownerid' 
    AND a.createdon > bc.createdon

UNION ALL

-- 3. SLA Timer Started
SELECT
    bc.ticketnumber AS ServiceRequest,
    'SLA Timer Started' AS ActivityName,
    si.createdon AS EventTime,
    bc.ownername AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    si.failuretime AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    slaitem si
JOIN
    BaseCases bc ON si.regardingobjectid = bc.incidentid
WHERE
    si.name = 'First Response By' OR si.name = 'Resolve By' -- Customize based on your SLA names

UNION ALL

-- 4. Queue Item Picked By Agent
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Queue Item Picked By Agent' AS ActivityName,
    qi.workeridmodifiedon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    queueitem qi
JOIN
    BaseCases bc ON qi.objectid = bc.incidentid
JOIN
    systemuser u ON qi.workerid = u.systemuserid
WHERE
    qi.workeridmodifiedon IS NOT NULL

UNION ALL

-- 5. Agent Investigated Issue
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Agent Investigated Issue' AS ActivityName,
    ki.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    knowledgearticleincident ki
JOIN
    BaseCases bc ON ki.incidentid = bc.incidentid
JOIN
    systemuser u ON ki.createdby = u.systemuserid

UNION ALL

-- 6. Information Requested From Customer
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Information Requested From Customer' AS ActivityName,
    e.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    email e
JOIN
    BaseCases bc ON e.regardingobjectid = bc.incidentid
JOIN
    systemuser u ON e.createdby = u.systemuserid
WHERE
    e.directioncode = 1 -- Outbound

UNION ALL

-- 7. Case Categorization Changed
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Case Categorization Changed' AS ActivityName,
    a.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    audit a
JOIN
    BaseCases bc ON a.objectid = bc.incidentid
JOIN
    systemuser u ON a.userid = u.systemuserid
WHERE
    a.objecttypecode = 112 
    AND a.attributename = 'subjectid'

UNION ALL

-- 9. Solution Proposed To Customer
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Solution Proposed To Customer' AS ActivityName,
    a.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    'Pending Customer Confirmation' AS StatusReason, -- Status is explicitly set
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    audit a
JOIN
    BaseCases bc ON a.objectid = bc.incidentid
JOIN
    systemuser u ON a.userid = u.systemuserid
WHERE
    a.objecttypecode = 112
    AND a.attributename = 'statuscode' 
    AND a.newvalue LIKE '%[Value for Pending Customer Confirmation]%'

UNION ALL

-- 10. Case Reactivated
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Case Reactivated' AS ActivityName,
    a.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    'In Progress' AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    audit a
JOIN
    BaseCases bc ON a.objectid = bc.incidentid
JOIN
    systemuser u ON a.userid = u.systemuserid
WHERE
    a.objecttypecode = 112
    AND a.attributename = 'statecode'
    AND a.oldvalue = '1' -- Resolved
    AND a.newvalue = '0' -- Active

UNION ALL

-- 11. Case Resolved
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Case Resolved' AS ActivityName,
    cr.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    'Problem Solved' AS StatusReason, -- StatusReason is explicitly 'Resolved'
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    caseresolution cr
JOIN
    BaseCases bc ON cr.incidentid = bc.incidentid
JOIN
    systemuser u ON cr.createdby = u.systemuserid

UNION ALL

-- 12. Satisfaction Survey Sent
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Satisfaction Survey Sent' AS ActivityName,
    e.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    email e
JOIN
    BaseCases bc ON e.regardingobjectid = bc.incidentid
JOIN
    systemuser u ON e.createdby = u.systemuserid
WHERE
    e.directioncode = 1 -- Outbound
    AND e.subject LIKE '%Your Feedback on Case%' -- Adjust this pattern based on your survey email subject

UNION ALL

-- 13. Case Closed
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Case Closed' AS ActivityName,
    i.modifiedon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    'Closed' AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    incident i
JOIN
    BaseCases bc ON i.incidentid = bc.incidentid
JOIN
    systemuser u ON i.modifiedby = u.systemuserid
WHERE
    i.statecode = 1; -- 1 = Resolved/Closed

DECLARE @StartDate AS DATETIME = '2023-01-01T00:00:00';
DECLARE @EndDate AS DATETIME = '2024-01-01T00:00:00';

WITH BaseCases AS (
    SELECT 
        i.incidentid,
        i.ticketnumber,
        i.createdon,
        i.prioritycode,
        i.caseorigincode,
        i.statuscode,
        i.subjectidname AS servicerequesttype,
        acc.name AS customername,
        own.fullname AS ownername
    FROM 
        incident i
    LEFT JOIN 
        account acc ON i.customerid = acc.accountid
    LEFT JOIN 
        systemuser own ON i.ownerid = own.systemuserid
    WHERE
        i.createdon >= @StartDate AND i.createdon < @EndDate
)
-- 1. Case Created
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Case Created' AS ActivityName,
    bc.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    BaseCases bc
JOIN 
    systemuser u ON bc.createdon = u.createdon AND bc.ownername = u.fullname

UNION ALL

-- 2. Case Assigned & 8. Case Escalated
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Case Assigned' AS ActivityName, -- Note: This logic captures all owner changes. You may need to add filters on NewOwner.fullname to identify escalations specifically.
    a.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    audit a
JOIN
    BaseCases bc ON a.objectid = bc.incidentid
JOIN
    systemuser u ON a.userid = u.systemuserid
WHERE
    a.objecttypecode = 112 -- Incident entity
    AND a.attributename = 'ownerid' 
    AND a.createdon > bc.createdon

UNION ALL

-- 3. SLA Timer Started
SELECT
    bc.ticketnumber AS ServiceRequest,
    'SLA Timer Started' AS ActivityName,
    si.createdon AS EventTime,
    bc.ownername AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    si.failuretime AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    slaitem si
JOIN
    BaseCases bc ON si.regardingobjectid = bc.incidentid
WHERE
    si.name = 'First Response By' OR si.name = 'Resolve By' -- Customize based on your SLA names

UNION ALL

-- 4. Queue Item Picked By Agent
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Queue Item Picked By Agent' AS ActivityName,
    qi.workeridmodifiedon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    queueitem qi
JOIN
    BaseCases bc ON qi.objectid = bc.incidentid
JOIN
    systemuser u ON qi.workerid = u.systemuserid
WHERE
    qi.workeridmodifiedon IS NOT NULL

UNION ALL

-- 5. Agent Investigated Issue
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Agent Investigated Issue' AS ActivityName,
    ki.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    knowledgearticleincident ki
JOIN
    BaseCases bc ON ki.incidentid = bc.incidentid
JOIN
    systemuser u ON ki.createdby = u.systemuserid

UNION ALL

-- 6. Information Requested From Customer
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Information Requested From Customer' AS ActivityName,
    e.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    email e
JOIN
    BaseCases bc ON e.regardingobjectid = bc.incidentid
JOIN
    systemuser u ON e.createdby = u.systemuserid
WHERE
    e.directioncode = 1 -- Outbound

UNION ALL

-- 7. Case Categorization Changed
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Case Categorization Changed' AS ActivityName,
    a.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    audit a
JOIN
    BaseCases bc ON a.objectid = bc.incidentid
JOIN
    systemuser u ON a.userid = u.systemuserid
WHERE
    a.objecttypecode = 112 
    AND a.attributename = 'subjectid'

UNION ALL

-- 9. Solution Proposed To Customer
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Solution Proposed To Customer' AS ActivityName,
    a.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    'Pending Customer Confirmation' AS StatusReason, -- Status is explicitly set
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    audit a
JOIN
    BaseCases bc ON a.objectid = bc.incidentid
JOIN
    systemuser u ON a.userid = u.systemuserid
WHERE
    a.objecttypecode = 112
    AND a.attributename = 'statuscode' 
    AND a.newvalue LIKE '%[Value for Pending Customer Confirmation]%'

UNION ALL

-- 10. Case Reactivated
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Case Reactivated' AS ActivityName,
    a.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    'In Progress' AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    audit a
JOIN
    BaseCases bc ON a.objectid = bc.incidentid
JOIN
    systemuser u ON a.userid = u.systemuserid
WHERE
    a.objecttypecode = 112
    AND a.attributename = 'statecode'
    AND a.oldvalue = '1' -- Resolved
    AND a.newvalue = '0' -- Active

UNION ALL

-- 11. Case Resolved
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Case Resolved' AS ActivityName,
    cr.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    'Problem Solved' AS StatusReason, -- StatusReason is explicitly 'Resolved'
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    caseresolution cr
JOIN
    BaseCases bc ON cr.incidentid = bc.incidentid
JOIN
    systemuser u ON cr.createdby = u.systemuserid

UNION ALL

-- 12. Satisfaction Survey Sent
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Satisfaction Survey Sent' AS ActivityName,
    e.createdon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    CASE bc.statuscode WHEN 1 THEN 'In Progress' WHEN 2 THEN 'On Hold' WHEN 3 THEN 'Waiting for Details' WHEN 4 THEN 'Researching' WHEN 5 THEN 'Problem Solved' ELSE 'Unknown' END AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    email e
JOIN
    BaseCases bc ON e.regardingobjectid = bc.incidentid
JOIN
    systemuser u ON e.createdby = u.systemuserid
WHERE
    e.directioncode = 1 -- Outbound
    AND e.subject LIKE '%Your Feedback on Case%' -- Adjust this pattern based on your survey email subject

UNION ALL

-- 13. Case Closed
SELECT
    bc.ticketnumber AS ServiceRequest,
    'Case Closed' AS ActivityName,
    i.modifiedon AS EventTime,
    u.fullname AS AgentName,
    bc.servicerequesttype AS ServiceRequestType,
    CASE bc.prioritycode WHEN 1 THEN 'High' WHEN 2 THEN 'Normal' WHEN 3 THEN 'Low' ELSE 'Unknown' END AS Priority,
    CASE bc.caseorigincode WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' WHEN 3 THEN 'Web' ELSE 'Other' END AS Channel,
    NULL AS SlaTargetResolutionTime,
    'Closed' AS StatusReason,
    bc.customername AS CustomerName,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM
    incident i
JOIN
    BaseCases bc ON i.incidentid = bc.incidentid
JOIN
    systemuser u ON i.modifiedby = u.systemuserid
WHERE
    i.statecode = 1; -- 1 = Resolved/Closed

import requests
import json
import pandas as pd
from msal import ConfidentialClientApplication
from datetime import datetime

class=class="hl-string">"hl-comment"># === CONFIGURATION ===
class=class="hl-string">"hl-comment"># REPLACE WITH YOUR DYNAMICS 365 ENVIRONMENT DETAILS
ENVIRONMENT_URL = class="hl-string">"https://[yourorg].api.crm.dynamics.com"
CLIENT_ID = class="hl-string">"[your-client-id]"
CLIENT_SECRET = class="hl-string">"[your-client-secret]"
TENANT_ID = class="hl-string">"[your-tenant-id]"

class=class="hl-string">"hl-comment"># API settings
API_VERSION = class="hl-string">"v9.2"
API_URL = fclass="hl-string">"{ENVIRONMENT_URL}/api/data/{API_VERSION}/"
AUTHORITY_URL = fclass="hl-string">"https://login.microsoftonline.com/{TENANT_ID}"
SCOPES = [fclass="hl-string">"{ENVIRONMENT_URL}/.default"]

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

def get_access_token():
    class="hl-string">"""Authenticates and retrieves an access token."""
    app = ConfidentialClientApplication(CLIENT_ID, authority=AUTHORITY_URL, client_credential=CLIENT_SECRET)
    result = app.acquire_token_for_client(scopes=SCOPES)
    if class="hl-string">"access_token" in result:
        return result[class="hl-string">'access_token']
    else:
        print(class="hl-string">"Error acquiring token:", result.get(class="hl-string">"error_description"))
        return None

def fetch_all_data(session, url):
    class="hl-string">"""Fetches all pages of data from a Dataverse OData URL."""
    all_results = []
    while url:
        response = session.get(url)
        if response.status_code == 200:
            data = response.json()
            all_results.extend(data[class="hl-string">'value'])
            url = data.get(class="hl-string">'@odata.nextLink')
        else:
            print(fclass="hl-string">"Error fetching data: {response.status_code} - {response.text}")
            break
    return all_results

def main():
    class="hl-string">"""Main function to extract, transform, and save the event log."""
    access_token = get_access_token()
    if not access_token:
        return

    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'OData-MaxVersion': class="hl-string">'4.0',
        class="hl-string">'OData-Version': class="hl-string">'4.0',
        class="hl-string">'Accept': class="hl-string">'application/json',
        class="hl-string">'Prefer': class="hl-string">'odata.include-annotations="*"'
    }
    
    session = requests.Session()
    session.headers.update(headers)
    all_events = []
    last_data_update = datetime.utcnow().isoformat() + class="hl-string">"Z"

    class=class="hl-string">"hl-comment"># Define base attributes to select for all queries
    base_incident_select = class="hl-string">"ticketnumber,createdon,prioritycode,_customerid_value,caseorigincode,statuscode,_ownerid_value"
    base_incident_expand = class="hl-string">"ownerid(fullname),customerid_contact(fullname),customerid_account(name)"
    
    print(class="hl-string">"Extracting activities...")

    class=class="hl-string">"hl-comment"># 1. Case Created
    print(class="hl-string">"1. Case Created")
    query_url = (fclass="hl-string">"{API_URL}incidents?$select={base_incident_select},incidentstagecode" + 
                 fclass="hl-string">"&$expand={base_incident_expand}" + 
                 fclass="hl-string">"&$filter=createdon ge {start_date_str} and createdon le {end_date_str}")
    results = fetch_all_data(session, query_url)
    for r in results:
        all_events.append({
            class="hl-string">'ServiceRequest': r.get(class="hl-string">'ticketnumber'),
            class="hl-string">'ActivityName': class="hl-string">'Case Created',
            class="hl-string">'EventTime': r.get(class="hl-string">'createdon'),
            class="hl-string">'AgentName': r.get(class="hl-string">'ownerid', {}).get(class="hl-string">'fullname'),
            class="hl-string">'ServiceRequestType': r.get(class="hl-string">'incidentstagecode@OData.Community.Display.V1.FormattedValue'),
            class="hl-string">'Priority': r.get(class="hl-string">'prioritycode@OData.Community.Display.V1.FormattedValue'),
            class="hl-string">'Channel': r.get(class="hl-string">'caseorigincode@OData.Community.Display.V1.FormattedValue'),
            class="hl-string">'StatusReason': r.get(class="hl-string">'statuscode@OData.Community.Display.V1.FormattedValue'),
            class="hl-string">'CustomerName': r.get(class="hl-string">'customerid_contact', {}).get(class="hl-string">'fullname') or r.get(class="hl-string">'customerid_account', {}).get(class="hl-string">'name')
        })

    class=class="hl-string">"hl-comment"># 2. Case Assigned & 7. Case Categorization Changed & 8. Case Escalated & 10. Case Reactivated & 13. Case Closed
    class=class="hl-string">"hl-comment"># These are extracted from Audit logs for precision
    print(class="hl-string">"2, 7, 8, 10, 13. Audit Log Events")
    class=class="hl-string">"hl-comment"># Note: Audit API does not support complex OData filtering like other entities. We filter post-extraction.
    audit_query_url = (fclass="hl-string">"{API_URL}audits?$select=_objectid_value,createdon,_userid_value,action,attributemask,changejson" + 
                       fclass="hl-string">"&$filter=createdon ge {start_date_str} and createdon le {end_date_str} and objecttypecode eq 'incident' and (operation eq 2 or operation eq 3)")
    audit_logs = fetch_all_data(session, audit_query_url)
    class=class="hl-string">"hl-comment"># To enrich with case details, we fetch all relevant case data first
    case_ids = {log[class="hl-string">'_objectid_value'] for log in audit_logs}
    case_details = {}
    if case_ids:
        case_ids_filter = class="hl-string">" or ".join([fclass="hl-string">"incidentid eq {cid}" for cid in case_ids])
        case_details_url = fclass="hl-string">"{API_URL}incidents?$select=ticketnumber,incidentid&$filter={case_ids_filter}"
        case_data = fetch_all_data(session, case_details_url)
        case_details = {c[class="hl-string">'incidentid']: c[class="hl-string">'ticketnumber'] for c in case_data}
    
    for log in audit_logs:
        case_id = log.get(class="hl-string">'_objectid_value')
        ticket_number = case_details.get(case_id)
        if not ticket_number: continue

        event = {class="hl-string">'ServiceRequest': ticket_number, class="hl-string">'EventTime': log.get(class="hl-string">'createdon')}
        activity_name = None
        
        if class="hl-string">'ownerid' in (log.get(class="hl-string">'attributemask') or class="hl-string">''):
            activity_name = class="hl-string">'Case Assigned' class=class="hl-string">"hl-comment"># Could also be Case Escalated, context is needed
        elif class="hl-string">'subjectid' in (log.get(class="hl-string">'attributemask') or class="hl-string">''):
            activity_name = class="hl-string">'Case Categorization Changed'
        elif class="hl-string">'statuscode' in (log.get(class="hl-string">'attributemask') or class="hl-string">''):
            try:
                change_data = json.loads(log.get(class="hl-string">'changejson', class="hl-string">'{}'))
                old_status = change_data.get(class="hl-string">'OldValue', {}).get(class="hl-string">'statuscode')
                new_status = change_data.get(class="hl-string">'NewValue', {}).get(class="hl-string">'statuscode')
                if new_status == class="hl-string">'5': class=class="hl-string">"hl-comment"># 5 = Resolved
                    class=class="hl-string">"hl-comment"># This is captured more accurately by CaseResolution entity
                    continue
                elif new_status == class="hl-string">'1000': class=class="hl-string">"hl-comment"># 1000 = Closed
                    activity_name = class="hl-string">'Case Closed'
                elif old_status in [class="hl-string">'5', class="hl-string">'1000'] and new_status not in [class="hl-string">'5', class="hl-string">'1000']:
                    activity_name = class="hl-string">'Case Reactivated'
            except json.JSONDecodeError:
                pass
        
        if activity_name:
            event[class="hl-string">'ActivityName'] = activity_name
            all_events.append(event)

    class=class="hl-string">"hl-comment"># 3. SLA Timer Started
    print(class="hl-string">"3. SLA Timer Started")
    query_url = (fclass="hl-string">"{API_URL}slakpiinstances?$select=createdon,name,_regarding_value,slakpiinstanceid" + 
                 fclass="hl-string">"&$expand=regarding_incident(ticketnumber,prioritycode,caseorigincode,_customerid_value,statuscode,_ownerid_value,resolveby)" + 
                 fclass="hl-string">"&$filter=createdon ge {start_date_str} and createdon le {end_date_str} and statecode eq 0")
    results = fetch_all_data(session, query_url)
    for r in results:
        incident = r.get(class="hl-string">'regarding_incident', {})
        if not incident: continue
        all_events.append({
            class="hl-string">'ServiceRequest': incident.get(class="hl-string">'ticketnumber'),
            class="hl-string">'ActivityName': class="hl-string">'SLA Timer Started',
            class="hl-string">'EventTime': r.get(class="hl-string">'createdon'),
            class="hl-string">'SlaTargetResolutionTime': incident.get(class="hl-string">'resolveby')
        })

    class=class="hl-string">"hl-comment"># 4. Queue Item Picked By Agent
    print(class="hl-string">"4. Queue Item Picked By Agent")
    query_url = (fclass="hl-string">"{API_URL}queueitems?$select=modifiedon,_workerid_value,_objectid_value" + 
                 fclass="hl-string">"&$expand=workerid(fullname),objectid_incident(ticketnumber)" + 
                 fclass="hl-string">"&$filter=modifiedon ge {start_date_str} and modifiedon le {end_date_str} and objecttypecode eq 112 and _workerid_value ne null")
    results = fetch_all_data(session, query_url)
    for r in results:
        if r.get(class="hl-string">'objectid_incident'):
            all_events.append({
                class="hl-string">'ServiceRequest': r[class="hl-string">'objectid_incident'].get(class="hl-string">'ticketnumber'),
                class="hl-string">'ActivityName': class="hl-string">'Queue Item Picked By Agent',
                class="hl-string">'EventTime': r.get(class="hl-string">'modifiedon'),
                class="hl-string">'AgentName': r.get(class="hl-string">'workerid', {}).get(class="hl-string">'fullname')
            })

    class=class="hl-string">"hl-comment"># 5. Agent Investigated Issue
    print(class="hl-string">"5. Agent Investigated Issue")
    query_url = (fclass="hl-string">"{API_URL}knowledgearticleincidents?$select=createdon,_incidentid_value,_knowledgearticleid_value" + 
                 fclass="hl-string">"&$expand=incidentid(ticketnumber,_ownerid_value),ownerid(fullname)" + 
                 fclass="hl-string">"&$filter=createdon ge {start_date_str} and createdon le {end_date_str}")
    results = fetch_all_data(session, query_url)
    for r in results:
        if r.get(class="hl-string">'incidentid'):
            all_events.append({
                class="hl-string">'ServiceRequest': r[class="hl-string">'incidentid'].get(class="hl-string">'ticketnumber'),
                class="hl-string">'ActivityName': class="hl-string">'Agent Investigated Issue',
                class="hl-string">'EventTime': r.get(class="hl-string">'createdon'),
                class="hl-string">'AgentName': r[class="hl-string">'incidentid'].get(class="hl-string">'ownerid', {}).get(class="hl-string">'fullname')
            })

    class=class="hl-string">"hl-comment"># 6. Information Requested From Customer & 9. Solution Proposed & 12. Satisfaction Survey Sent
    print(class="hl-string">"6, 9, 12. Email Activities")
    query_url = (fclass="hl-string">"{API_URL}emails?$select=subject,createdon,directioncode,_regardingobjectid_value,_ownerid_value" + 
                 fclass="hl-string">"&$expand=regardingobjectid_incident(ticketnumber),ownerid(fullname)" + 
                 fclass="hl-string">"&$filter=createdon ge {start_date_str} and createdon le {end_date_str} and directioncode eq true and _regardingobjectid_value ne null")
    results = fetch_all_data(session, query_url)
    for r in results:
        incident = r.get(class="hl-string">'regardingobjectid_incident')
        if incident:
            subject = r.get(class="hl-string">'subject', class="hl-string">'').lower()
            activity_name = class="hl-string">'Information Requested From Customer' class=class="hl-string">"hl-comment"># Default outbound email
            if class="hl-string">'solution' in subject or class="hl-string">'resolved' in subject:
                activity_name = class="hl-string">'Solution Proposed To Customer'
            elif class="hl-string">'satisfaction survey' in subject or class="hl-string">'feedback' in subject:
                activity_name = class="hl-string">'Satisfaction Survey Sent'

            all_events.append({
                class="hl-string">'ServiceRequest': incident.get(class="hl-string">'ticketnumber'),
                class="hl-string">'ActivityName': activity_name,
                class="hl-string">'EventTime': r.get(class="hl-string">'createdon'),
                class="hl-string">'AgentName': r.get(class="hl-string">'ownerid', {}).get(class="hl-string">'fullname')
            })

    class=class="hl-string">"hl-comment"># 11. Case Resolved
    print(class="hl-string">"11. Case Resolved")
    query_url = (fclass="hl-string">"{API_URL}incidentresolutions?$select=createdon,_incidentid_value,_ownerid_value" + 
                 fclass="hl-string">"&$expand=incidentid(ticketnumber),ownerid(fullname)" + 
                 fclass="hl-string">"&$filter=createdon ge {start_date_str} and createdon le {end_date_str}")
    results = fetch_all_data(session, query_url)
    for r in results:
        if r.get(class="hl-string">'incidentid'):
            all_events.append({
                class="hl-string">'ServiceRequest': r[class="hl-string">'incidentid'].get(class="hl-string">'ticketnumber'),
                class="hl-string">'ActivityName': class="hl-string">'Case Resolved',
                class="hl-string">'EventTime': r.get(class="hl-string">'createdon'),
                class="hl-string">'AgentName': r.get(class="hl-string">'ownerid', {}).get(class="hl-string">'fullname')
            })

    print(fclass="hl-string">"Total events extracted: {len(all_events)}")

    if not all_events:
        print(class="hl-string">"No events found. Exiting.")
        return

    class=class="hl-string">"hl-comment"># Create DataFrame and format
    df = pd.DataFrame(all_events)
    df[class="hl-string">'SourceSystem'] = class="hl-string">'Dynamics 365'
    df[class="hl-string">'LastDataUpdate'] = last_data_update
    
    class=class="hl-string">"hl-comment"># Fill missing recommended attributes where possible
    base_case_data = df[df[class="hl-string">'ActivityName'] == class="hl-string">'Case Created'][[ class="hl-string">'ServiceRequest', class="hl-string">'ServiceRequestType', class="hl-string">'Priority', class="hl-string">'Channel', class="hl-string">'StatusReason', class="hl-string">'CustomerName', class="hl-string">'SlaTargetResolutionTime']].drop_duplicates(subset=class="hl-string">'ServiceRequest')
    df = pd.merge(df, base_case_data, on=class="hl-string">'ServiceRequest', how=class="hl-string">'left', suffixes=(class="hl-string">'', class="hl-string">'_y'))
    
    class=class="hl-string">"hl-comment"># Coalesce columns
    for col in [class="hl-string">'ServiceRequestType', class="hl-string">'Priority', class="hl-string">'Channel', class="hl-string">'StatusReason', class="hl-string">'CustomerName', class="hl-string">'SlaTargetResolutionTime']:
        df[col] = df[col].fillna(df[col + class="hl-string">'_y'])
        df.drop(columns=[col + class="hl-string">'_y'], inplace=True)

    class=class="hl-string">"hl-comment"># Final column selection and ordering
    final_cols = [class="hl-string">'ServiceRequest', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate', class="hl-string">'AgentName', class="hl-string">'ServiceRequestType', class="hl-string">'Priority', class="hl-string">'Channel', class="hl-string">'SlaTargetResolutionTime', class="hl-string">'StatusReason', class="hl-string">'CustomerName']
    df = df.reindex(columns=final_cols)
    df.sort_values(by=[class="hl-string">'ServiceRequest', class="hl-string">'EventTime'], inplace=True)

    class=class="hl-string">"hl-comment"># Save to CSV
    df.to_csv(class="hl-string">'event_log.csv', index=False)
    print(class="hl-string">"Event log saved to event_log.csv")

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

/*
This extraction is configured using a Power Automate cloud flow. The core logic consists of multiple Dataverse 'List rows' actions, each with a specific OData filter query to extract one activity. The results are then shaped and combined.

Flow Trigger: Recurrence (e.g., Daily)

Variables:
- ExtractionStartDate (Expression: addDays(utcNow(), -1))
- SourceSystem (String: 'Microsoft Dynamics 365')
- LastDataUpdate (Expression: utcNow())

Below are the OData Filter Queries used in the 'List rows' action for each activity.
*/

-- Activity 1: Case Created (Table: incidents)
createdon ge @{variables('ExtractionStartDate')} and statecode eq 0

-- Activity 2: Case Assigned (Table: audits)
objecttypecode eq 'incident' and (attributename eq 'ownerid') and createdon ge @{variables('ExtractionStartDate')}

-- Activity 3: SLA Timer Started (Table: slakpiinstances)
(status eq 2) and createdon ge @{variables('ExtractionStartDate')} and regarding_incident/statecode eq 0

-- Activity 4: Queue Item Picked By Agent (Table: queueitems)
objecttypecode eq 'incident' and workedon ge @{variables('ExtractionStartDate')} and (workerid ne null)

-- Activity 5: Agent Investigated Issue (Table: knowledgearticleincidents)
-- Note: This is an indirect relationship. We query incidents where this relationship was made in the timeframe.
-- Query incidents first, then filter based on the linkage creation time. This requires a more complex FetchXML query or post-filtering logic in the flow.
-- Simplified OData on Incidents: incidents?$filter=modifiedon ge @{variables('ExtractionStartDate')} and knowledgearticle_incidents/any()

-- Activity 6: Information Requested From Customer (Table: audits)
objecttypecode eq 'incident' and (attributename eq 'statuscode') and createdon ge @{variables('ExtractionStartDate')} and newvalue eq '[Your Status Reason value for Waiting]'

-- Activity 7: Case Categorization Changed (Table: audits)
objecttypecode eq 'incident' and (attributename eq 'subjectid') and createdon ge @{variables('ExtractionStartDate')}

-- Activity 8: Case Escalated (Table: incidents)
escalatedon ge @{variables('ExtractionStartDate')} and statecode eq 0

-- Activity 9: Solution Proposed To Customer (Table: audits)
objecttypecode eq 'incident' and (attributename eq 'statuscode') and createdon ge @{variables('ExtractionStartDate')} and newvalue eq '[Your Status Reason value for Proposed]'

-- Activity 10: Case Reactivated (Table: audits)
objecttypecode eq 'incident' and (attributename eq 'statecode') and createdon ge @{variables('ExtractionStartDate')} and oldvalue eq '1' and newvalue eq '0'

-- Activity 11: Case Resolved (Table: incidentresolutions)
createdon ge @{variables('ExtractionStartDate')} and incidentid/statecode ne 2

-- Activity 12: Satisfaction Survey Sent (Table: emails)
-- This query assumes a specific subject line for survey emails.
subject eq '[Your CSAT Survey Email Subject]' and createdon ge @{variables('ExtractionStartDate')} and regardingobjectid_incident_email/ticketnumber ne null

-- Activity 13: Case Closed (Table: audits)
objecttypecode eq 'incident' and (attributename eq 'statecode') and createdon ge @{variables('ExtractionStartDate')} and newvalue eq '2'