> 持续流程改进 > 客户服务 > Microsoft Dynamics 365 Customer Service > 数据模板

您的客户服务数据模板

Microsoft Dynamics 365 Customer Service

← 返回改进指南

客户服务属性 (20) 客户服务活动 (13) 提取指南 (4)

您的客户服务数据模板

此模板为收集客户服务流程全面分析所需的关键数据提供了结构化方法。模板列出了需要收集的重要属性和需要跟踪的关键活动，并指导您高效提取这些信息。

建议收集的属性
需要跟踪的关键活动
Microsoft Dynamics 365客户服务数据提取指南

刚接触事件日志？了解如何创建流程挖掘事件日志.

下载模板

客户服务属性

以下是建议纳入事件日志的数据字段，用于全面分析客户服务流程。

5 必需 7 建议 8 可选

	名称	说明
	服务请求 ServiceRequest	客户服务请求的唯一标识符，也称为案例或工单。
说明服务请求是主要标识符，用于关联单个客户咨询或问题的所有活动。它作为流程挖掘中的案例ID，确保从创建到关闭的每次客户互动都能获得完整且一致的视图。按服务请求分析，可以跟踪端到端历程、衡量解决时长，并识别相似案例中的模式。为什么重要这是连接所有相关事件与单个流程实例的必要案例ID，使端到端流程分析成为可能。获取位置这是Microsoft Dynamics 365 Customer Service中Case（incident）实体的主键。示例 CAS-01024-F3B4V6SR-2023-00589TKT-4815162342
	最后数据更新时间 LastDataUpdate	源系统最近一次刷新或提取数据的时间戳。
说明此属性表示最近一次从Microsoft Dynamics 365提取数据的时间。它用于了解所分析数据的新鲜度，对于报告和监控也不可或缺。解读仪表板和分析结果时，它可以帮助用户了解数据的时效性。为什么重要告知用户数据的更新时间，这对于基于流程分析及时、准确地制定业务决策至关重要。获取位置此值会在提取数据时生成并写入数据集。示例 2023-10-27T08:00:00Z
	开始时间 EventTime	表示活动发生时间的时间戳。
说明此属性记录具体活动发生的准确日期和时间。它对于正确排列事件顺序，以及开展所有基于时间的分析都不可或缺，包括计算周期时间、持续时长和活动间等待时间。准确且一致的时间戳是确保流程挖掘分析可靠性的关键。为什么重要此时间戳按时间顺序排列事件，并支持所有基于时长的计算，这些计算对于绩效分析和瓶颈识别至关重要。获取位置对应Case（incident）实体或相关活动实体（例如Email、Task、Phone Call）中的“createdon”或“modifiedon”等字段。示例 2023-04-15T10:00:00Z2023-05-20T14:35:10Z2023-06-01T09:12:45Z
	活动 ActivityName	服务请求在某个时间点发生的具体业务事件名称。
说明该属性描述客户服务流程中的单个步骤或状态变化，例如“案例已创建”“坐席调查问题”或“案例已解决”。这些活动构成流程图的基础，用于呈现和分析顺序流。每项活动与时间戳结合后会生成一个事件，用于定义流程顺序。为什么重要活动定义流程中的各个步骤。分析活动的顺序和频率，是理解顺序流、识别偏差和发现瓶颈的基础。获取位置通常通过将状态变更（statuscode）或相关实体中的特定事件，例如Tasks、Emails或Phone Calls，映射为标准化活动名称来生成。示例创建案例客服专员调查问题已向客户提出解决方案案例已关闭
	源系统 SourceSystem	标识数据提取自哪个源系统。
说明此属性指定事件数据的来源。在此流程中，它将始终标识Microsoft Dynamics 365 Customer Service为来源。在多系统环境中，该字段对于区分数据来源和确保数据血缘至关重要。为什么重要提供清晰的数据血缘，这对于数据治理和排查数据不一致问题至关重要，尤其适用于混合系统分析。获取位置通常是在数据提取和转换过程中添加的静态值，用于标记数据集来源。示例 Microsoft Dynamics 365 Customer Service
	SLA目标解决时长 SlaTargetResolutionTime	合同约定的服务请求目标解决时长。
说明此属性指定根据有效服务级别协议（SLA）应在多长时间内解决服务请求。它是衡量实际绩效的基准。此值对于SLA合规监控仪表板至关重要，也用于计算SLA合规率KPI，帮助发现流程未达到服务承诺的环节。为什么重要这是衡量服务绩效是否达到承诺的主要基准，可直接支持SLA合规性和违约情况分析。获取位置此值由Dynamics 365中的SLA配置确定，并通过SLA KPI实例与案例关联。示例 2592008640014400
	优先级 Priority	分配给服务请求的优先级，用于表示其紧急程度。
说明该属性定义服务请求的紧急程度，通常分为Low、Normal、High或Urgent。Priority用于确定处理案例的顺序，并通常决定SLA目标。分析Priority如何影响顺序流、资源分配和解决时长，对于确保及时处理严重问题至关重要。为什么重要帮助了解高优先级请求是否得到更快处理并达到目标，以及不同优先级如何影响整体流程绩效。获取位置对应Case（incident）实体中的“Priority”（prioritycode）字段。示例低正常高
	客户名称 CustomerName	与服务请求关联的客户或账户名称。
说明此属性标识发起服务请求的客户。它支持从客户视角分析流程，帮助识别哪些客户提交的请求最多、解决时长最长，或问题最复杂。这对于客户关系管理和改进针对性服务交付至关重要。为什么重要支持客户层面的分析，帮助识别模式、改善重点客户服务，并了解客户旅程。获取位置这是Case（incident）实体中的“Customer”（customerid）查找字段，可指向Account或Contact记录。示例 Global Tech Inc.Jane DoeInnovate Solutions
	客服人员姓名 AgentName	负责该活动的客户服务客服人员或用户姓名。
说明此属性标识执行活动的具体客服人员或系统用户，例如从队列中接手项目或解决案例的人员。它对于分析客服人员绩效、工作量分配和资源配置至关重要。按客服人员跟踪活动，可以识别高绩效人员、培训需求和工作量失衡。为什么重要支持分析个人和团队绩效，帮助平衡工作量，并发现提升整体服务质量的辅导机会。获取位置对应Case（incident）实体中的“Owner”（ownerid）字段，该字段关联System User（systemuser）实体。示例 Alice SmithBob Johnson系统
	服务请求类型 ServiceRequestType	服务请求的主要类别或分类。
说明此属性根据服务请求的性质进行分类，例如“账单咨询”“技术支持”或“产品反馈”。它是细分流程分析的基础，有助于了解不同类型请求的处理方式。按类型分析可以发现某些类别的解决时长更长、升级率更高，或遵循不同的流程路径。为什么重要支持按类型细分流程，发现特定类型的瓶颈、资源需求和改进机会，从而优化路由和处理策略。获取位置此信息通常存储在Case（incident）实体的“Subject”（subjectid）字段或自定义类别字段中。示例账单咨询技术支持产品反馈账户管理
	渠道 Channel	发起服务请求所使用的沟通渠道。
说明此属性标识客户互动的来源，例如电话、电子邮件、门户网站或聊天。不同渠道通常具有不同的流程、客户期望和解决复杂度。按渠道分析流程，有助于优化特定渠道的工作流和资源配置。为什么重要帮助了解不同客户联系渠道如何影响流程效率、解决时长和客户满意度。获取位置对应Case（incident）实体中的“Case Origin”（caseorigincode）字段。示例电话电子邮件Web聊天
	状态原因 StatusReason	提供服务请求当前状态的更详细原因。
说明案例可能具有“Active”或“Resolved”等概括性状态，而状态原因则提供更具体的上下文，例如“已提供信息”或“问题已解决”。此属性对于理解案例如何以及为何经历生命周期变化至关重要。例如，它可以区分成功解决的案例和由客户取消的案例，这对于准确分析结果十分重要。为什么重要提供案例结果及状态变更原因的细粒度信息，从而更准确地分析解决路径和根本原因。获取位置对应Case（incident）实体中的“Status Reason”（statuscode）字段。示例处理中暂缓问题已解决已提供信息
	CSAT得分 CustomerSatisfactionScore	客户在案例解决后提供的满意度得分。
说明此属性保存客户满意度（CSAT）调查中的数值或分类评分，通常在服务请求关闭后收集。它直接反映客户对服务质量的感受。此数据对于客户满意度趋势仪表板和解决后平均CSAT得分KPI至关重要，可将流程绩效与客户结果关联起来。为什么重要直接衡量客户满意度，使组织能够将流程行为与客户感受关联，并推动改进。获取位置通常来源于Customer Voice等相关调查实体，并关联回Case（incident）实体。示例 5341
	所有者团队 OwnerTeam	当前负责该服务请求的团队。
说明此属性标识负责服务请求的团队。请求可以由个人客服人员或团队（队列）负责。按团队分析对于了解团队绩效、不同支持层级或专业团队之间的工作量分配，以及识别团队间的流程差异至关重要。为什么重要支持团队层面的绩效分析，对于有效管理支持层级和专业团队不可或缺。获取位置当所有者是Team记录而非System User时，根据Case（incident）实体中的“Owner”（ownerid）字段生成。示例一级支持账单部门技术专家
	是否已升级 IsEscalated	表示服务请求是否已升级的标记。
说明此布尔属性表示服务请求是否经历过升级。当一线支持无法解决问题，需要更高级别团队或专家介入时，就会发生升级。跟踪此标记对于内部升级路径仪表板和内部升级率KPI至关重要，有助于识别升级根因以及初始支持层级中的薄弱环节。为什么重要直接衡量升级频率，突出首次联系解决方面的问题，并指向需要改进流程或客服人员技能的领域。获取位置对应Case（incident）实体中的“Is Escalated”（isescalated）字段。示例 truefalse
	是否返工 IsRework	表示案例是否包含返工活动的计算标记。
说明此布尔标记用于识别包含返工循环或重复活动的案例，例如多次出现“向客户请求信息”，或解决后出现“案例已重新激活”。它通过分析活动顺序，检测表明低效或首次未能正确解决问题的模式来计算。此属性是返工与重复联系分析仪表板的关键依据。为什么重要帮助量化并隔离流程低效问题，使分析人员能够聚焦于重复工作和资源浪费的根本原因。获取位置通过流程挖掘分析检测特定活动序列（例如Resolved→Reactivated）或案例中的重复活动来计算。示例 truefalse
	是否违反SLA IsSlaBreached	表示服务请求是否超过SLA目标的计算标记。
说明此布尔标记通过比较服务请求的实际解决时长与“SLA目标解决时长”确定。如果实际时长超过目标时长，则设为true。此属性是SLA合规监控仪表板和SLA合规率KPI计算的基础，为每个案例的SLA绩效提供清晰的二元结果。为什么重要为每个案例的SLA遵循情况提供明确的成功或失败结果，便于筛选、汇总和分析违约根因。获取位置通过比较“ServiceRequestCycleTime”与“SlaTargetResolutionTime”计算。示例 truefalse
	是否首次联系解决 IsFirstContactResolution	表示请求是否在首次联系时解决的标记。
说明此计算属性用于识别无需客户后续互动，且无需因明显延迟而重新分配客服人员即可解决的服务请求。具体逻辑可能较为复杂，但通常包括检查较短的周期时间，以及初次互动后没有重新打开或客户咨询活动。它是首次联系解决率KPI的基础。为什么重要这是衡量服务效率和客户满意度的关键指标，体现了快速、彻底解决问题的能力。获取位置根据每个案例事件日志中的活动顺序和时间计算。示例 truefalse
	涉及的产品 ProductInvolved	与客户服务请求相关的产品。
说明此属性标识客户问题涉及的具体产品或服务。它支持按产品细分服务流程，有助于发现某些产品是否产生更多支持请求、问题是否更复杂，或是否需要专门技能。此类分析有助于产品改进和资源规划。为什么重要支持产品级流程分析，帮助识别反复出现的产品问题、改进支持文档，并有效配置专家资源。获取位置对应Case（incident）实体中的“Product”（productid）查找字段。示例 Alpha-100 PrinterZeta CRM SoftwareOmega Data Plan
	知识文章ID KnowledgeArticleId	与服务请求关联的知识库文章标识符。
说明此属性记录服务请求解决过程中使用或关联的知识文章ID。它可以直接衡量客服人员对知识库的使用效果。此数据是知识文章使用情况仪表板及其对应KPI的关键依据，有助于评估知识库的价值和完整性。为什么重要跟踪知识库使用情况，帮助了解客服人员是否利用现有资源更快、更一致地解决问题。获取位置此信息来自Case（incident）实体与Knowledge Article（knowledgearticle）实体之间的关系。示例 KA-01337KA-02048

必需建议可选

客户服务活动

以下是需要记录在事件日志中的关键流程步骤和里程碑，用于准确发现和优化客户服务。

6 建议 7 可选

	活动	说明
	SLA计时器启动	表示案例的服务级别协议（SLA）计时器已激活，开始根据“First Response By”或“Resolve By”等定义的服务指标计时。此明确事件由Dynamics 365 SLA引擎管理。
为什么重要此活动是监控SLA合规性、了解服务承诺计时起点的基础，也直接支持分析服务目标是否达成。获取位置记录在“SLA KPI Instance”实体中，该实体与“Incident”实体相关联。相关SLA KPI Instance记录的“createdon”时间戳标志着计时开始。采集使用与案例关联的“SLA KPI Instance”记录的创建时间戳。事件类型 explicit
	分配案例	此活动表示将案例分配给特定队列或用户进行处理。系统会明确记录案例所有者的变更，可通过系统审核日志进行追踪。
为什么重要追踪分配情况对于分析工作负载分布、识别与分配相关的延误，以及了解路由效率至关重要。它有助于回答案例多快能够路由至正确团队或人员等问题。获取位置通过追踪“Incident”实体中“ownerid”字段的变更进行记录。变更时间戳可在审核历史日志中获取。采集从审核日志中提取带时间戳的“ownerid”字段变更。事件类型 explicit
	创建案例	此活动标志着客户服务流程的开始，即系统中创建新的案例记录。创建是一个明确事件，当“Incident”实体记录首次保存时，系统会记录具体时间戳。
为什么重要作为主要开始事件，此活动对于计算案例的整体生命周期时长、了解案例量趋势至关重要，也是后续所有流程分析的锚点。获取位置此事件取自每条新记录“Incident”（Case）实体中的“createdon”时间戳。采集使用Incident记录的“createdon”时间戳。事件类型 explicit
	升级案例	表示将案例正式升级至更高层级的支持团队或其他团队。这可能是用户执行的明确操作，将案例重新分配给指定的升级队列或用户。
为什么重要监控升级对于“内部升级率”KPI至关重要，也有助于识别一线支持无法解决的问题的根本原因。它可以揭示流程薄弱环节和培训机会。获取位置可根据“ownerid”字段变更为指定的升级队列或团队来推断。也可以通过标记案例已升级的明确自定义操作来识别。采集识别“ownerid”变更为已知升级队列的带时间戳记录。事件类型 inferred
	案例已关闭	这是对案例记录进行最终管理关闭的操作，可能与解决同时发生，也可能在之后发生。此活动通过案例状态变更为“Closed”来捕获。
为什么重要这表示系统中流程生命周期的绝对终点。“Resolved”到“Closed”之间的时间可以反映管理开销或记录最终确认的延迟。获取位置通过“Incident”实体的“statecode”字段变更为“Canceled”（2）或自定义关闭状态来捕获。时间戳可在审计历史中获取。采集跟踪“statecode”变更为最终终止状态的时间戳，例如Canceled或Closed。事件类型 explicit
	案例已解决	这是一个关键里程碑，表示客服人员认为客户问题已得到处理。在Dynamics 365中，这是一个明确操作，会创建与案例关联的“Case Resolution”活动记录。
为什么重要作为主要的成功终点事件，此活动对于计算解决时长和成功率不可或缺，也是几乎所有客户服务KPI的重要组成部分。获取位置此事件对应于创建“Resolution”（Case Resolution）活动记录。该记录中的“actualend”时间戳表示解决时间。采集使用关联“Resolution”活动记录中的“actualend”或“createdon”时间戳。事件类型 explicit
	向客户请求信息	此活动标志着客服专员需要客户提供更多信息才能继续处理的节点。通常可通过案例状态变更为“waiting”状态，或案例时间线发送外发电子邮件来推断。
为什么重要此活动对于衡量客户相关延误、了解“Customer Information Wait Time”KPI至关重要，有助于分离等待外部输入所占用的流程时间。获取位置可根据“Incident”实体中“statuscode”字段变更为类似“On Hold”的值，并以“Waiting for Customer”为原因进行推断。使用该状态变更的时间戳。采集追踪状态代码变更为指定“waiting for customer”状态时的时间戳。事件类型 inferred
	客服专员调查问题	表示客服专员主动了解并诊断客户问题的过程。这是一项推断活动，通常通过客服专员将Knowledge Article关联至案例来识别，表明已开展调查。
为什么重要追踪此活动有助于衡量知识资源的利用情况及其对解决时间的影响，了解客服专员是否在利用现有工具高效解决问题。获取位置根据“IncidentKnowledgeBaseRecord”实体中记录的创建进行推断，该记录将Knowledge Article关联至Case。使用该记录的创建时间戳。采集使用Knowledge Article与Incident建立关联时的时间戳。事件类型 inferred
	客服专员领取队列项目	当客服专员主动从共享队列中领取案例并开始处理时，就会发生此事件。这是用户的主动操作，与系统将案例分配至队列不同。
为什么重要此活动有助于衡量案例在队列中等待客服专员开始处理的实际时间，是识别队列瓶颈、了解客服专员主动性的关键。获取位置当用户更新与案例关联的“QueueItem”实体中的“workedbyid”字段，或案例所有者从队列变更为用户时进行追踪。采集确定QueueItem中“workedbyid”字段被填入值的时间戳。事件类型 explicit
	已发送满意度调查	表示已发送客户满意度调查，通常在案例解决后自动触发。一般通过外发邮件或Customer Voice调查活动捕获。
为什么重要此活动将运营流程与客户体验结果关联起来，使您能够结合案例所经过的流程路径分析满意度得分。获取位置可根据创建包含调查链接的外发“Email”活动，或与案例相关的“Customer Voice survey invite”活动记录来推断。采集使用调查相关活动记录的创建时间戳。事件类型 inferred
	已向客户提出解决方案	此活动表示客服人员已制定解决方案并与客户沟通。通常可根据案例时间线中发送的外发邮件，或状态变更为“等待客户确认”来推断。
为什么重要此里程碑标志着流程从调查转入解决。分析提出解决方案与确认解决方案之间的时间，可以发现客户响应延迟或所提修复方案存在的问题。获取位置可根据与案例相关的外发“Email”活动记录时间戳，或状态码变更为解决前状态来推断。采集使用外发邮件活动的时间戳，或状态变更为“已提出解决方案”的时间戳。事件类型 inferred
	案例分类变更	当客服专员在案例创建后修改其类别或主题时，就会发生此事件。这是系统审核功能所追踪的明确变更。
为什么重要追踪重新分类对于“Service Request Recategorization Rate”KPI至关重要。发生频率较高表明初始分诊存在问题，可能导致错误路由和延误。获取位置从“Incident”实体的审核历史中获取，具体追踪“subjectid”字段或其他自定义分类字段的变更。采集从审核日志中提取带时间戳的“subjectid”字段变更。事件类型 explicit
	案例已重新激活	此前已解决的案例被自动或手动重新打开时触发，通常是因为客户回复或反馈问题尚未解决。这是系统的标准行为，会将案例状态从“已解决”改回“活动”。
为什么重要此活动对于识别返工和分析“首次联系解决率”至关重要。重新激活次数较多，通常表明初始解决方案不完整或效果不佳。获取位置通过“Incident”实体的“statecode”字段从“Resolved”（1）变回“Active”（0）的变更记录捕获。变更时间戳会记录在审计历史中。采集跟踪审计日志中“statecode”从Resolved变更为Active的时间戳。事件类型 explicit

建议可选

提取指南

如何从Microsoft Dynamics 365客户服务中获取数据

步骤

前提条件和设置：确保您在Microsoft Power Platform中拥有适当权限（系统管理员），并在Microsoft Azure订阅中拥有所有者或参与者权限。创建Azure Synapse Analytics工作区和Azure Data Lake Storage Gen2账户，并确保它们与Power Platform环境位于同一Azure区域，以获得最佳性能。
配置Azure Synapse Link：访问Power Apps Maker Portal（make.powerapps.com）。选择正确的环境，然后转到Dataverse > Azure Synapse Link。创建新链接，并连接到您的Azure订阅、资源组、Synapse工作区和存储账户。
选择Dataverse表：在Synapse Link配置中选择所需的提取表。至少包括：incident（工单表）、systemuser、queue、queueitem、email、knowledgearticleincident、caseresolution、account、contact和audit。audit表对于捕获分配和状态更新等变更事件至关重要。
等待初始同步：保存配置后，系统会将所选表初始同步到Azure Data Lake。具体耗时取决于数据量，可能需要较长时间。您可以在Azure Synapse Link仪表板中监控状态。
在Synapse Analytics中访问数据：同步状态变为“Active”后，打开Azure Synapse工作区。在Synapse Studio中转到“Data”中心。您会看到一个以Dataverse环境命名的新湖数据库。所选表将在此处提供查询。
创建新的SQL脚本：在Synapse Studio的“Develop”中心创建新的SQL脚本。确保脚本连接到“Built-in”无服务器SQL池。
编写事件日志查询：将本文档提供的完整SQL查询复制到新脚本窗口中。该查询适用于无服务器SQL池，可将多个表中的数据整合为统一的事件日志格式。
自定义查询参数：执行前修改查询中的占位符。设置开始和结束日期（@StartDate、@EndDate）以定义分析周期，通常使用6到12个月的数据。
执行并检查：运行查询。无服务器SQL池会直接从数据湖处理数据。在结果窗格中检查结果，确认数据正确且包含预期活动。
导出事件日志：确认结果无误后导出数据。使用Synapse Studio内置功能将结果下载为CSV文件。该CSV文件就是最终事件日志，可上传到流程挖掘工具。

配置

Azure Synapse Link：该服务持续将Dataverse表数据导出到Azure Data Lake Storage Gen2。它是此提取方法的核心组件，可提供可扩展、近实时的数据访问能力。
无服务器SQL池：查询通过Synapse Analytics中的无服务器SQL池执行。您无需管理基础设施，即可使用标准T-SQL语法直接查询数据湖。费用按每次查询处理的数据量计收。
导出表：您必须明确选择要从Dataverse导出的表。查询依赖incident、audit、systemuser、queueitem、email、knowledgearticleincident和caseresolution。如需更丰富的数据，还应包含account、contact、subject和queue。
审计历史：在Power Platform管理中心为“案例”实体（incident）启用审计。具体来说，应为“负责人”“状态原因”和“主题”等字段启用审计，因为查询依赖审计表准确重建顺序流。
日期范围：强烈建议使用提供的日期变量按具体日期范围筛选查询，例如最近6至12个月。查询完整历史记录可能速度较慢且成本较高，尤其是在高数据量系统中。
前提条件：此方法要求具备有效的Azure订阅、Azure Synapse Analytics工作区、Azure Data Lake Storage Gen2账户，以及Power Platform环境的管理员级访问权限。

a 示例查询 sql

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

步骤

确认Dynamics 365环境已开放只读Dataverse TDS终结点，并确认您的SQL客户端支持Microsoft SQL Server连接。从Dataverse管理员处获取环境专属的服务器名称、数据库名称、身份验证方式和只读凭据。请勿执行写入操作。
确认环境中提供所需的Dataverse表和列。标准环境通常提供用于工单的Incident表，但审核、SLA、队列、电子邮件、知识文章、调查和解决数据可能使用环境专属的表或视图。将查询中的每个方括号占位符替换为环境中已验证的对象名和列名。
在SQL客户端、Power BI、Azure Data Factory或其他获批准的提取工具中配置连接。使用管理员提供的TDS终结点连接信息，按要求启用加密，并在运行完整提取前测试只读SELECT语句。
使用[开始日期参数]和[结束日期参数]设置提取窗口。初次验证建议使用滚动3到6个月的时间窗口。确认查询性能和源系统保留策略后，再扩大范围。
运行完整查询。每个UNION ALL分支都会为13项必需活动中的一项创建明确的事件行。除各分支定义的源条件外，查询不会推断其他事件。基于审核、队列、SLA、电子邮件、知识、调查或解决数据的活动，需要先验证相应的源对象和列。
检查输出架构。确认包含ServiceRequest、ActivityName、EventTime、SourceSystem和LastDataUpdate，以及AgentName、ServiceRequestType、Priority、Channel、SlaTargetResolutionTime、StatusReason和CustomerName。确认EventTime为时间戳，并确保所有事件行中的ServiceRequest保持稳定。
验证事件顺序和重复记录。按ServiceRequest和EventTime排序；如果多个事件具有相同时间戳，再应用确定性的次级排序。除非源系统和业务负责人批准去重规则，否则不要合并重复行，因为ProcessMind会按原样读取事件日志。
将结果导出为UTF-8 CSV或ProcessMind支持的其他表格格式。保留准确的必需列名，每个明确事件保留一行，不要将活动透视为单独的列。上传文件或配置获批准的ProcessMind数据接入连接，然后将ServiceRequest映射为案例标识符，将ActivityName映射为活动，将EventTime映射为事件时间戳。

配置

**连接方式：**使用只读Microsoft Dataverse TDS终结点，并通过获批准的SQL客户端或提取平台连接。必须确保该终结点已为环境启用且可访问。
**源对象：**执行前验证每个方括号占位符对应的对象和列。不要假定审核历史、SLA事件、队列项目操作、Customer Voice记录或工单解决记录使用特定的表名或视图名。
**日期范围：**从3到6个月的数据开始。对于定期加载，使用滚动提取窗口，并加入较短的重叠期，以捕获延迟到达或更正后的记录。
**案例范围：**基于已验证的工单表以及相关的工单创建或修改时间戳进行过滤。只有确认环境中存在相应列后，才应用公司、业务部门、区域或其他组织筛选条件。
**活动覆盖范围：**查询包含13个明确的UNION ALL分支。依赖审核或相关活动数据的分支，必须映射到环境专属的对应源。如果某个源不可用，不要擅自替换为推断事件。
**状态和类别筛选：**验证活动、等待、等待客户确认、已解决和已关闭状态的准确选项值。Dataverse的选项值和标签可能因环境而异。
**性能：**尽可能在每个分支中限制提取日期范围和案例范围。仅选择必需列，避免不必要的连接，分别测试各分支，并对大型数据集使用增量提取。
**时区：**确认TDS时间戳以UTC、本地时间还是其他形式存储或返回。上传前统一EventTime，并记录转换方式。
**身份验证和授权：**提取身份需要对工单表以及查询使用的所有相关源拥有读取权限，包括适用的审核、队列、SLA、电子邮件、知识、调查和解决数据。
**许可和功能：**确认环境已启用所需的Dynamics 365 Customer Service、SLA、队列、审核和调查功能。Customer Voice数据可能需要单独的许可和访问权限。
**刷新时间戳：**将[最后数据更新时间表达式]替换为获批准工具生成的提取运行时间戳，或替换为已验证的源刷新时间戳。一次提取运行中的所有行应使用同一值。

a 示例查询 sql

SELECT
    CAST(i.[Your case identifier column] AS nvarchar(100)) AS ServiceRequest,
    CAST('Case Created' AS nvarchar(100)) AS ActivityName,
    CAST(i.[Your case created timestamp column] AS datetime2) AS EventTime,
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)) AS SourceSystem,
    CAST([Last data update expression] AS datetime2) AS LastDataUpdate,
    CAST(i.[Your agent or owner name column] AS nvarchar(200)) AS AgentName,
    CAST(i.[Your service request type column] AS nvarchar(200)) AS ServiceRequestType,
    CAST(i.[Your priority column] AS nvarchar(100)) AS Priority,
    CAST(i.[Your channel column] AS nvarchar(100)) AS Channel,
    CAST(i.[Your SLA target resolution time column] AS datetime2) AS SlaTargetResolutionTime,
    CAST(i.[Your status reason column] AS nvarchar(200)) AS StatusReason,
    CAST(i.[Your customer name column] AS nvarchar(200)) AS CustomerName
FROM [Your case table] i
WHERE i.[Your case created timestamp column] >= [Start date parameter]
  AND i.[Your case created timestamp column] < [End date parameter]

UNION ALL

SELECT
    CAST(a.[Your case identifier column] AS nvarchar(100)),
    CAST('Case Assigned' AS nvarchar(100)),
    CAST(a.[Your assignment change timestamp column] AS datetime2),
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)),
    CAST([Last data update expression] AS datetime2),
    CAST(a.[Your assigned user or owner name column] AS nvarchar(200)),
    CAST(i.[Your service request type column] AS nvarchar(200)),
    CAST(i.[Your priority column] AS nvarchar(100)),
    CAST(i.[Your channel column] AS nvarchar(100)),
    CAST(i.[Your SLA target resolution time column] AS datetime2),
    CAST(i.[Your status reason column] AS nvarchar(200)),
    CAST(i.[Your customer name column] AS nvarchar(200))
FROM [Your case assignment audit source] a
JOIN [Your case table] i ON i.[Your case identifier column] = a.[Your case identifier column]
WHERE a.[Your assignment change timestamp column] >= [Start date parameter]
  AND a.[Your assignment change timestamp column] < [End date parameter]
  AND a.[Your assignment change condition] = [Your assignment change value]

UNION ALL

SELECT
    CAST(s.[Your case identifier column] AS nvarchar(100)),
    CAST('SLA Timer Started' AS nvarchar(100)),
    CAST(s.[Your SLA activation timestamp column] AS datetime2),
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)),
    CAST([Last data update expression] AS datetime2),
    CAST(s.[Your SLA owner or agent name column] AS nvarchar(200)),
    CAST(i.[Your service request type column] AS nvarchar(200)),
    CAST(i.[Your priority column] AS nvarchar(100)),
    CAST(i.[Your channel column] AS nvarchar(100)),
    CAST(s.[Your SLA target resolution time column] AS datetime2),
    CAST(i.[Your status reason column] AS nvarchar(200)),
    CAST(i.[Your customer name column] AS nvarchar(200))
FROM [Your SLA activation source] s
JOIN [Your case table] i ON i.[Your case identifier column] = s.[Your case identifier column]
WHERE s.[Your SLA activation timestamp column] >= [Start date parameter]
  AND s.[Your SLA activation timestamp column] < [End date parameter]

UNION ALL

SELECT
    CAST(q.[Your case identifier column] AS nvarchar(100)),
    CAST('Queue Item Picked By Agent' AS nvarchar(100)),
    CAST(q.[Your queue item picked timestamp column] AS datetime2),
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)),
    CAST([Last data update expression] AS datetime2),
    CAST(q.[Your picking agent name column] AS nvarchar(200)),
    CAST(i.[Your service request type column] AS nvarchar(200)),
    CAST(i.[Your priority column] AS nvarchar(100)),
    CAST(i.[Your channel column] AS nvarchar(100)),
    CAST(i.[Your SLA target resolution time column] AS datetime2),
    CAST(i.[Your status reason column] AS nvarchar(200)),
    CAST(i.[Your customer name column] AS nvarchar(200))
FROM [Your queue item source] q
JOIN [Your case table] i ON i.[Your case identifier column] = q.[Your case identifier column]
WHERE q.[Your queue item picked timestamp column] >= [Start date parameter]
  AND q.[Your queue item picked timestamp column] < [End date parameter]
  AND q.[Your queue item picked condition] = [Your picked condition value]

UNION ALL

SELECT
    CAST(k.[Your case identifier column] AS nvarchar(100)),
    CAST('Agent Investigated Issue' AS nvarchar(100)),
    CAST(k.[Your knowledge article link timestamp column] AS datetime2),
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)),
    CAST([Last data update expression] AS datetime2),
    CAST(k.[Your linking agent name column] AS nvarchar(200)),
    CAST(i.[Your service request type column] AS nvarchar(200)),
    CAST(i.[Your priority column] AS nvarchar(100)),
    CAST(i.[Your channel column] AS nvarchar(100)),
    CAST(i.[Your SLA target resolution time column] AS datetime2),
    CAST(i.[Your status reason column] AS nvarchar(200)),
    CAST(i.[Your customer name column] AS nvarchar(200))
FROM [Your case knowledge article link source] k
JOIN [Your case table] i ON i.[Your case identifier column] = k.[Your case identifier column]
WHERE k.[Your knowledge article link timestamp column] >= [Start date parameter]
  AND k.[Your knowledge article link timestamp column] < [End date parameter]

UNION ALL

SELECT
    CAST(w.[Your case identifier column] AS nvarchar(100)),
    CAST('Information Requested From Customer' AS nvarchar(100)),
    CAST(w.[Your waiting status or outbound email timestamp column] AS datetime2),
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)),
    CAST([Last data update expression] AS datetime2),
    CAST(w.[Your agent or sender name column] AS nvarchar(200)),
    CAST(i.[Your service request type column] AS nvarchar(200)),
    CAST(i.[Your priority column] AS nvarchar(100)),
    CAST(i.[Your channel column] AS nvarchar(100)),
    CAST(i.[Your SLA target resolution time column] AS datetime2),
    CAST(w.[Your status reason column] AS nvarchar(200)),
    CAST(i.[Your customer name column] AS nvarchar(200))
FROM [Your waiting status or outbound email source] w
JOIN [Your case table] i ON i.[Your case identifier column] = w.[Your case identifier column]
WHERE w.[Your waiting status or outbound email timestamp column] >= [Start date parameter]
  AND w.[Your waiting status or outbound email timestamp column] < [End date parameter]
  AND w.[Your information requested condition] = [Your information requested value]

UNION ALL

SELECT
    CAST(c.[Your case identifier column] AS nvarchar(100)),
    CAST('Case Categorization Changed' AS nvarchar(100)),
    CAST(c.[Your category change timestamp column] AS datetime2),
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)),
    CAST([Last data update expression] AS datetime2),
    CAST(c.[Your changing agent name column] AS nvarchar(200)),
    CAST(c.[Your new service request type column] AS nvarchar(200)),
    CAST(i.[Your priority column] AS nvarchar(100)),
    CAST(i.[Your channel column] AS nvarchar(100)),
    CAST(i.[Your SLA target resolution time column] AS datetime2),
    CAST(i.[Your status reason column] AS nvarchar(200)),
    CAST(i.[Your customer name column] AS nvarchar(200))
FROM [Your case categorization audit source] c
JOIN [Your case table] i ON i.[Your case identifier column] = c.[Your case identifier column]
WHERE c.[Your category change timestamp column] >= [Start date parameter]
  AND c.[Your category change timestamp column] < [End date parameter]
  AND c.[Your category change condition] = [Your category change value]

UNION ALL

SELECT
    CAST(e.[Your case identifier column] AS nvarchar(100)),
    CAST('Case Escalated' AS nvarchar(100)),
    CAST(e.[Your escalation timestamp column] AS datetime2),
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)),
    CAST([Last data update expression] AS datetime2),
    CAST(e.[Your escalation agent or owner name column] AS nvarchar(200)),
    CAST(i.[Your service request type column] AS nvarchar(200)),
    CAST(i.[Your priority column] AS nvarchar(100)),
    CAST(i.[Your channel column] AS nvarchar(100)),
    CAST(i.[Your SLA target resolution time column] AS datetime2),
    CAST(i.[Your status reason column] AS nvarchar(200)),
    CAST(i.[Your customer name column] AS nvarchar(200))
FROM [Your case escalation source] e
JOIN [Your case table] i ON i.[Your case identifier column] = e.[Your case identifier column]
WHERE e.[Your escalation timestamp column] >= [Start date parameter]
  AND e.[Your escalation timestamp column] < [End date parameter]
  AND e.[Your escalation condition] = [Your escalation value]

UNION ALL

SELECT
    CAST(p.[Your case identifier column] AS nvarchar(100)),
    CAST('Solution Proposed To Customer' AS nvarchar(100)),
    CAST(p.[Your outbound email or pending confirmation timestamp column] AS datetime2),
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)),
    CAST([Last data update expression] AS datetime2),
    CAST(p.[Your agent or sender name column] AS nvarchar(200)),
    CAST(i.[Your service request type column] AS nvarchar(200)),
    CAST(i.[Your priority column] AS nvarchar(100)),
    CAST(i.[Your channel column] AS nvarchar(100)),
    CAST(i.[Your SLA target resolution time column] AS datetime2),
    CAST(p.[Your status reason column] AS nvarchar(200)),
    CAST(i.[Your customer name column] AS nvarchar(200))
FROM [Your solution proposal source] p
JOIN [Your case table] i ON i.[Your case identifier column] = p.[Your case identifier column]
WHERE p.[Your outbound email or pending confirmation timestamp column] >= [Start date parameter]
  AND p.[Your outbound email or pending confirmation timestamp column] < [End date parameter]
  AND p.[Your solution proposed condition] = [Your solution proposed value]

UNION ALL

SELECT
    CAST(r.[Your case identifier column] AS nvarchar(100)),
    CAST('Case Reactivated' AS nvarchar(100)),
    CAST(r.[Your reactivation timestamp column] AS datetime2),
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)),
    CAST([Last data update expression] AS datetime2),
    CAST(r.[Your reactivating user name column] AS nvarchar(200)),
    CAST(i.[Your service request type column] AS nvarchar(200)),
    CAST(i.[Your priority column] AS nvarchar(100)),
    CAST(i.[Your channel column] AS nvarchar(100)),
    CAST(i.[Your SLA target resolution time column] AS datetime2),
    CAST(r.[Your status reason column] AS nvarchar(200)),
    CAST(i.[Your customer name column] AS nvarchar(200))
FROM [Your case status audit source] r
JOIN [Your case table] i ON i.[Your case identifier column] = r.[Your case identifier column]
WHERE r.[Your reactivation timestamp column] >= [Start date parameter]
  AND r.[Your reactivation timestamp column] < [End date parameter]
  AND r.[Your previous status value] = [Your resolved status value]
  AND r.[Your new status value] = [Your active status value]

UNION ALL

SELECT
    CAST(v.[Your case identifier column] AS nvarchar(100)),
    CAST('Case Resolved' AS nvarchar(100)),
    CAST(v.[Your resolution timestamp column] AS datetime2),
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)),
    CAST([Last data update expression] AS datetime2),
    CAST(v.[Your resolving agent name column] AS nvarchar(200)),
    CAST(i.[Your service request type column] AS nvarchar(200)),
    CAST(i.[Your priority column] AS nvarchar(100)),
    CAST(i.[Your channel column] AS nvarchar(100)),
    CAST(i.[Your SLA target resolution time column] AS datetime2),
    CAST(i.[Your status reason column] AS nvarchar(200)),
    CAST(i.[Your customer name column] AS nvarchar(200))
FROM [Your case resolution source] v
JOIN [Your case table] i ON i.[Your case identifier column] = v.[Your case identifier column]
WHERE v.[Your resolution timestamp column] >= [Start date parameter]
  AND v.[Your resolution timestamp column] < [End date parameter]

UNION ALL

SELECT
    CAST(sv.[Your case identifier column] AS nvarchar(100)),
    CAST('Satisfaction Survey Sent' AS nvarchar(100)),
    CAST(sv.[Your survey sent timestamp column] AS datetime2),
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)),
    CAST([Last data update expression] AS datetime2),
    CAST(sv.[Your survey sender or owner name column] AS nvarchar(200)),
    CAST(i.[Your service request type column] AS nvarchar(200)),
    CAST(i.[Your priority column] AS nvarchar(100)),
    CAST(i.[Your channel column] AS nvarchar(100)),
    CAST(i.[Your SLA target resolution time column] AS datetime2),
    CAST(i.[Your status reason column] AS nvarchar(200)),
    CAST(i.[Your customer name column] AS nvarchar(200))
FROM [Your satisfaction survey source] sv
JOIN [Your case table] i ON i.[Your case identifier column] = sv.[Your case identifier column]
WHERE sv.[Your survey sent timestamp column] >= [Start date parameter]
  AND sv.[Your survey sent timestamp column] < [End date parameter]
  AND sv.[Your survey sent condition] = [Your survey sent value]

UNION ALL

SELECT
    CAST(cl.[Your case identifier column] AS nvarchar(100)),
    CAST('Case Closed' AS nvarchar(100)),
    CAST(cl.[Your closed status timestamp column] AS datetime2),
    CAST('Microsoft Dynamics 365 Customer Service' AS nvarchar(200)),
    CAST([Last data update expression] AS datetime2),
    CAST(cl.[Your closing user name column] AS nvarchar(200)),
    CAST(i.[Your service request type column] AS nvarchar(200)),
    CAST(i.[Your priority column] AS nvarchar(100)),
    CAST(i.[Your channel column] AS nvarchar(100)),
    CAST(i.[Your SLA target resolution time column] AS datetime2),
    CAST(cl.[Your status reason column] AS nvarchar(200)),
    CAST(i.[Your customer name column] AS nvarchar(200))
FROM [Your case closure audit source] cl
JOIN [Your case table] i ON i.[Your case identifier column] = cl.[Your case identifier column]
WHERE cl.[Your closed status timestamp column] >= [Start date parameter]
  AND cl.[Your closed status timestamp column] < [End date parameter]
  AND cl.[Your closed status condition] = [Your closed status value]

ORDER BY ServiceRequest, EventTime, ActivityName;

步骤

前提条件：确保已安装Python 3.6或更高版本，以及requests、msal和pandas库。您可以使用pip安装：pip install requests msal pandas。
在Azure AD中注册应用：访问Azure Active Directory管理中心。注册新应用以获取Client ID和Tenant ID。在“Certificates & secrets”部分创建Client Secret。
授予API权限：在已注册应用的“API permissions”设置中添加“Dynamics CRM”权限。选择“Delegated permissions”，勾选user_impersonation，并代表组织授予管理员同意。
在Power Platform中创建应用程序用户：进入Power Platform管理中心，选择您的Dynamics 365环境。创建新的应用程序用户，并使用已注册Azure AD应用的Client ID将其关联。
分配安全角色：在Dynamics 365环境中为新建的应用程序用户分配安全角色。初始设置可使用“System Administrator”角色，但生产环境建议使用自定义角色，并授予对Incident（Case）、Audit、QueueItem、Knowledge Article Incident、Email、SLA KPI Instance和Incident Resolution实体的读取权限。
配置Python脚本：将提供的Python脚本复制到文件中，例如extract_d365_events.py。在脚本顶部填写环境URL、客户端ID、客户端密钥和租户ID等占位变量。
定义提取周期：修改脚本中的start_date_str和end_date_str变量，定义数据提取的时间窗口。建议先使用较短周期，例如一个月，以测试连接和逻辑。
执行脚本：在终端中运行命令：python extract_d365_events.py。脚本会通过Dataverse API进行身份验证，执行多个查询以收集各项活动的数据，并汇总结果。
处理分页和数据转换：脚本会通过跟随响应中的@odata.nextLink处理API分页。脚本将处理原始JSON数据，选择必需字段、重命名字段，并将数据整理为统一格式。
生成事件日志：成功执行后，脚本会在同一目录下创建名为event_log.csv的文件。该文件包含提取的事件日志，以及所有活动和属性。
检查并上传：打开event_log.csv，检查数据的完整性和准确性。确认无误后，即可将文件上传到ProcessMind进行分析。

配置

身份验证信息：脚本连接Dataverse API需要提供以下信息：
- ENVIRONMENT_URL：Dynamics 365环境的基础URL，例如https://[yourorg].api.crm.dynamics.com。
- CLIENT_ID：Azure AD应用注册中的应用程序（客户端）ID。
- CLIENT_SECRET：Azure AD生成的客户端密钥值。
- TENANT_ID：Azure AD概览页中的目录（租户）ID。
日期范围：start_date_str和end_date_str变量控制提取周期。初次分析建议使用3到6个月的范围，以平衡数据量和性能。
OData筛选器：脚本使用OData $filter子句选择数据。您可以添加更多筛选条件来缩小范围，例如提取特定服务请求类型或业务部门的数据。
性能注意事项：脚本会发起多次API调用。对于超大型数据集，执行可能耗时较长，并可能接近API限流阈值。建议在业务低峰期执行大型提取任务。
审核日志前提条件：提取“Case Assigned”和“Case Escalated”等活动，依赖于已为Incident实体及其相关字段（ownerid、statuscode、subjectid）启用Dynamics 365审核历史。

a 示例查询 python

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()

步骤

前提条件：请确保您拥有包含高级连接器的Power Automate许可证，并对Dynamics 365 Customer Service环境中的以下Dataverse表具有适当权限（至少具备读取权限）：Incident、Audit、SLAKPIInstance、QueueItem、KnowledgeArticleIncident、IncidentResolution、Email和SystemUser。
创建工作流：进入Power Automate门户（make.powerautomate.com），选择正确的Dynamics 365环境，然后创建新的“Scheduled cloud flow”。
配置计划：为工作流设置名称，例如“Dynamics 365 Case Event Log Extraction”。根据需要配置运行频率，例如每天在业务时间之外运行，以尽量减少对性能的影响。
初始化变量：创建一个用于保存运行时间戳的变量，该变量将用于LastDataUpdate属性。使用表达式utcNow()获取当前UTC时间。
设置日期范围：对于增量加载，保存上次成功运行的时间戳。对于全量加载，定义开始日期和结束日期变量。本示例使用表达式addDays(utcNow(), -1)查询过去24小时内修改的记录。
使用并行分支：为提高性能，添加“Parallel branch”控件。为提取所需的13项活动分别创建一个分支。
获取每项活动的数据：在每个并行分支中添加Dataverse“List rows”操作。配置每个操作以查询特定表和事件。例如，对于“Case Created”，查询“Incidents”表，并使用类似createdon ge [your_start_date_variable]的OData筛选器。
整理数据：在每个“List rows”操作后添加“Select”数据操作。在“From”字段中，选择前一个“List rows”操作输出的值。在“Map”字段中创建所需列（ServiceRequest、ActivityName、EventTime等），并使用表达式将其映射到相应的Dataverse字段。例如，ActivityName可以是“Case Created”这样的静态字符串。
合并活动数据：在所有并行分支之后添加“Compose”操作。使用union()表达式，将所有“Select”操作的数组输出合并为一个数组。表达式类似于union(outputs('Select_Case_Created')?['body'], outputs('Select_Case_Assigned')?['body'], ...)。
创建CSV文件：添加“Create CSV table”数据操作。在“From”属性中，使用包含合并数据的“Compose”操作输出。
存储文件：在所需存储位置添加文件创建操作，例如SharePoint的“Create file”或OneDrive的“Create file”。使用“Create CSV table”操作的输出作为文件内容。
保存并测试：保存工作流并执行测试运行。确认工作流成功完成，并在指定位置生成包含预期数据的CSV文件。

配置

前提条件：需要拥有包含高级连接器的Power Automate许可证。配置流的用户需要对目标Dynamics 365环境中的所有相关Dataverse表拥有读取权限。
审核历史：工单分配、重新激活和分类变更等关键活动依赖Dynamics 365审核历史。确保已为“Incident”表以及ownerid、statuscode、subjectid和statecode字段启用审核。
日期范围筛选：必须在所有“List rows”操作中使用OData筛选查询，以限制数据量。初次提取建议使用3到6个月的周期。对于持续运行的计划任务，应筛选自上次运行以来创建或修改的数据（例如过去24小时内的数据）。
性能和API限制：Power Automate和Dataverse均存在API调用限制。提取大型数据集可能触发限流。请使用高效筛选条件，在业务低峰期安排流运行，并在“List rows”操作设置中启用分页，以处理超过默认数量的记录。
流的复杂度：由于包含超过13个并行分支，此提取流可能变得复杂。请使用作用域将每项活动的相关操作分组，以便保持流的结构清晰、易于管理。

a 示例查询 sql

/*
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'

准备开始了吗？

使用此数据模板开启您的流程挖掘之旅，发掘客户服务运营中的新效率。立即开始优化工作流，改善客户体验。

返回改进指南下载模板

优化客户服务：提升FCR，立即降低成本

识别瓶颈，实现80%的首次联系解决率，让客户更满意。

开始免费试用

无需信用卡，几分钟即可完成设置。

您的客户服务数据模板

您的客户服务数据模板

客户服务属性

客户服务活动

提取指南

Dynamics 365 Azure Synapse Link for Dataverse导出

步骤

配置

a 示例查询 sql

通过TDS终结点直接查询Dataverse（SQL）

步骤

配置

a 示例查询 sql

使用Dynamics 365 Dataverse Web API的Python脚本

步骤

配置

a 示例查询 python

使用Microsoft Power Automate自动导出数据

步骤

配置

a 示例查询 sql

准备开始了吗？

优化客户服务：提升FCR，立即降低成本

您的客户服务数据模板

您的客户服务数据模板

客户服务属性

客户服务活动

提取指南

Dynamics 365 Azure Synapse Link for Dataverse导出

步骤

配置

a 示例查询 sql

通过TDS终结点直接查询Dataverse（SQL）

步骤

配置

a 示例查询 sql

使用Dynamics 365 Dataverse Web API的Python脚本

步骤

配置

a 示例查询 python

使用Microsoft Power Automate自动导出数据

步骤

配置

a 示例查询 sql

准备开始了吗？

优化客户服务：提升FCR，立即降低成本

我们重视您的隐私