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

客户服务数据模板

Microsoft Dynamics 365客户服务

← 返回改进指南

客户服务属性(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）或相关实体（如Task、Email或Phone Call）中的特定事件映射为标准化活动名称来得出。示例创建案例客服人员调查问题向客户提出解决方案关闭案例
	源系统 SourceSystem	标识提取数据的源系统。
说明此属性指定事件数据的来源。对于此流程，该属性始终标识Microsoft Dynamics 365 Customer Service为数据源。在包含多个系统的环境中，此字段对于区分数据来源和确保数据沿袭至关重要。重要性清晰呈现数据沿袭，这对于数据治理和排查数据不一致问题至关重要，尤其是在分析来自多个系统的数据时。获取途径通常在数据提取和转换过程中添加静态值，用于标记数据集的来源。示例 Microsoft Dynamics 365客户服务
	SLA目标解决时间 SlaTargetResolutionTime	合同约定的服务请求解决时限。
说明此属性指定有效服务级别协议（SLA）要求的服务请求解决时限，是衡量实际绩效的基准。此值对于SLA达成情况监控仪表板和计算SLA达成率KPI至关重要，可帮助发现流程未能履行服务承诺的环节。重要性这是衡量服务绩效是否达到承诺的主要基准，可直接用于分析SLA达成情况和违约情况。获取途径此值由Dynamics 365中的SLA配置确定，并通过SLA KPI Instances与案例关联。示例 2592008640014400
	优先级 Priority	分配给服务请求的优先级，用于表示其紧急程度。
说明此属性定义服务请求的紧急程度，通常分为低、普通、高或紧急。优先级用于确定案例的处理顺序，通常也决定SLA目标。分析优先级对流程、资源分配和解决时长的影响，是确保紧急问题得到及时处理的关键。重要性有助于了解高优先级请求是否处理得更快并达到目标，以及优先级如何影响整体流程绩效。获取途径对应于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	发起服务请求时使用的沟通渠道。
说明此属性用于识别客户互动的来源，例如电话、电子邮件、Web门户或聊天。不同渠道的流程、客户预期和解决难度往往各不相同。按渠道分析流程，有助于优化相应工作流和资源分配。重要性帮助了解不同客户联系渠道对流程效率、解决时长和客户满意度的影响。获取途径对应于Case（incident）实体中的“Case Origin”（caseorigincode）字段。示例电话电子邮件Web聊天
	状态原因 StatusReason	提供服务请求当前状态的详细原因。
说明案例可能处于“活跃”或“已解决”等概括性状态，而状态原因则提供更具体的信息，例如“已提供信息”或“问题已解决”。此属性对于了解案例在生命周期中的进展及其原因至关重要。例如，它可以区分成功解决的案例和客户取消的案例，这对于准确分析结果十分重要。重要性详细呈现案例结果及状态变更原因，从而更准确地分析解决路径和根本原因。获取途径对应于Case（incident）实体中的“Status Reason”（statuscode）字段。示例处理中暂停处理问题已解决已提供信息
	客户满意度评分 CustomerSatisfactionScore	客户在案例解决后提供的满意度评分。
说明此属性记录客户满意度（CSAT）调查中的数值或类别评分，通常在服务请求关闭后收集。它直接反映客户对服务质量的评价。这些数据对于“客户满意度趋势”仪表板和“解决后平均CSAT评分”KPI至关重要，可将流程绩效与客户体验联系起来。重要性直接衡量客户满意度，帮助组织将流程表现与客户评价联系起来，并据此推动改进。获取途径此数据通常来自相关调查实体（如Customer Voice），并关联回Case（incident）实体。示例 5341
	所属团队 OwnerTeam	当前负责该服务请求的团队。
说明此属性用于识别负责服务请求的团队。请求可以由单个客服人员或团队（队列）负责。按团队分析对于了解团队绩效、不同支持层级或专业团队之间的工作量分布，以及发现团队间的流程差异至关重要。重要性支持在团队层面分析绩效，对于有效管理支持层级和专业团队至关重要。获取途径当Case（incident）实体的“负责人”（ownerid）字段指向Team记录而非System User时，此属性由该字段得出。示例一级支持账单部门技术专家团队
	是否已升级 IsEscalated	用于标记服务请求是否已升级的标志。
说明此布尔属性用于标记服务请求是否经过升级。当一线支持无法解决问题，需要更高级别的团队或专家介入时，就会发生升级。跟踪此标志对于“内部升级路径”仪表板和“内部升级率”KPI至关重要，有助于找出升级的根本原因以及初级支持层级的不足。重要性直接衡量升级频率，揭示首次联系解决方面的问题，并指出需要改进流程或客服人员技能的领域。获取途径对应Case（incident）实体中的“是否已升级”（isescalated）字段。示例 truefalse
	是否返工 IsRework	用于标记案例是否包含返工活动的计算字段。
说明此布尔标志用于识别包含返工循环或重复活动的案例，例如多次出现“向客户请求信息”事件，或解决后出现“案例重新激活”事件。系统通过分析活动顺序，识别表明效率低下或首次未能正确解决问题的模式并计算此属性。它是“返工与重复联系分析”仪表板的关键数据。重要性帮助量化并定位流程低效之处，让分析人员能够聚焦重复工作和资源浪费的根本原因。获取途径通过流程挖掘分析检测特定活动顺序（例如，已解决→重新激活）或案例中的重复活动来计算。示例 truefalse
	是否违反SLA IsSlaBreached	用于标记服务请求是否超出SLA目标的计算字段。
说明此布尔标志通过比较服务请求的实际解决时间与“SLA目标解决时间”得出。实际用时超过目标时，标志设为true。此属性是“SLA合规监控”仪表板和“SLA达标率”KPI计算的基础，可清晰标记每个案例的SLA绩效是否达标。重要性清晰标记每个案例是否达到SLA要求，便于筛选、汇总和分析超时的根本原因。获取途径通过比较“ServiceRequestCycleTime”和“SlaTargetResolutionTime”计算得出。示例 truefalse
	是否首次联系解决 IsFirstContactResolution	用于标记请求是否在首次联系时解决。
说明此计算属性用于识别无需客户后续联系，且未因明显延误而重新分配客服人员便得到解决的服务请求。具体判定逻辑可能较复杂，通常会检查周期时间是否较短，以及首次互动后是否没有重新打开案例或客户再次咨询等活动。此属性是“首次联系解决率”KPI的基础。重要性这是衡量服务效率和客户满意度的关键指标，可反映问题能否快速、彻底地解决。获取途径根据每个案例事件日志中的活动顺序和时间计算得出。示例 truefalse
	涉及的产品 ProductInvolved	与客户服务请求相关的产品。
说明此属性用于识别客户问题涉及的具体产品或服务。按产品细分服务流程，有助于发现某些产品是否引发更多支持请求、问题是否更复杂，或是否需要客服人员具备专门技能。这些分析有助于改进产品和规划资源。重要性支持按产品分析流程，以发现反复出现的产品问题、完善支持文档并有效分配专家资源。获取途径对应Case（incident）实体中的“产品”（productid）查找字段。示例 Alpha-100打印机Zeta CRM软件Omega数据套餐
	知识文章ID KnowledgeArticleId	与服务请求关联的知识库文章标识符。
说明此属性记录在解决服务请求期间使用或关联的知识文章ID，可直接衡量客服人员利用知识库解决客户问题的情况。这些数据对于“知识文章使用情况”仪表板及相应KPI至关重要，有助于评估知识库的价值和完整性。重要性跟踪知识库的使用情况，帮助了解客服人员是否利用现有资源更快、更一致地解决问题。获取途径此信息可在Case（incident）实体与Knowledge Article（knowledgearticle）实体之间的关系中找到。示例 KA-01337KA-02048

必填建议填写选填

客户服务活动

以下是建议纳入事件日志的关键流程步骤和里程碑，可用于准确发现并优化客户服务流程。

6 建议填写 7 选填

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

建议填写选填

数据提取指南

如何从Microsoft Dynamics 365 Customer Service获取数据

步骤

准备条件和设置：确保您在Microsoft Power Platform中拥有相应权限（系统管理员），并在Microsoft Azure订阅中拥有所有者或参与者权限。为获得最佳性能，请在与Power Platform环境相同的Azure区域中创建Azure Synapse Analytics工作区和Azure Data Lake Storage Gen2账户。
配置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中访问数据：同步状态变为“活跃”后，打开Azure Synapse工作区。在Synapse Studio中进入“数据”中心，即可找到一个以Dataverse环境命名的新湖数据库。所选表可在此查询。
新建SQL脚本：在Synapse Studio的“开发”中心中新建SQL脚本，并确保脚本连接到“内置”无服务器SQL池。
编写事件日志查询：将本文档提供的完整SQL查询复制到新脚本窗口。此查询适用于无服务器SQL池，可将多个表中的数据整合为统一的事件日志格式。
自定义查询参数：执行前，请修改查询中的占位符。设置开始和结束日期（@StartDate、@EndDate）以确定分析时间范围，通常可选取6至12个月的数据。
执行并检查：运行查询。无服务器SQL池会直接处理数据湖中的数据。请在结果窗格中检查结果，确认数据正确且包含预期活动。
导出事件日志：确认结果无误后，导出数据。使用Synapse Studio内置功能将结果下载为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环境的管理员级访问权限。

查询示例 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语句。
使用[开始日期参数]和[结束日期参数]设置提取时间范围。建议先使用滚动的三至六个月时间范围进行验证。确认查询性能和源数据保留期限后，再扩大范围。
运行完整查询。每个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映射为事件时间戳。

配置

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

查询示例 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 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环境。创建新的应用程序用户，并使用注册应用程序时获得的Client ID将其关联。
分配安全角色：在Dynamics 365环境中为新建的应用程序用户分配安全角色。初始设置可使用“System Administrator”角色；生产环境建议创建自定义角色，并授予对Incident（案例）、“审计”、QueueItem、Knowledge Article Incident、“电子邮件”、SLA KPI Instance和Incident Resolution实体的读取权限。
配置Python脚本：将提供的Python脚本复制到文件中，例如extract_d365_events.py。在脚本顶部填写环境URL、客户端ID、客户端密钥和租户ID等占位变量。
设置提取时间段：修改脚本中的start_date_str和end_date_str变量，设置数据提取的时间范围。建议先选择较短的时间段（例如1个月），测试连接和脚本逻辑。
运行脚本：在终端中运行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限流阈值。建议在非高峰时段执行大型数据提取。
审计日志准备条件：提取“案例已分配”和“案例已升级”等活动，需为Incident实体及相关字段（ownerid、statuscode、subjectid）启用Dynamics 365审计历史记录。

查询示例 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、审计、SLAKPIInstance、QueueItem、KnowledgeArticleIncident、IncidentResolution、电子邮件和SystemUser。
创建工作流：前往Power Automate门户（make.powerautomate.com），选择正确的Dynamics 365环境，然后新建“计划云端工作流”。
配置计划：为工作流命名，例如“Dynamics 365案例事件日志提取”。根据需要设置运行频率，例如每天在非工作时间运行，以尽量减少对性能的影响。
初始化变量：创建一个变量，用于保存本次运行的时间戳，并将其用于LastDataUpdate属性。使用表达式utcNow()获取当前UTC时间。
设置日期范围：增量加载时，保存上次成功运行的时间戳。完整加载时，定义开始和结束日期变量。本示例使用表达式addDays(utcNow(), -1)查询过去24小时内修改的记录。
使用并行分支：添加“并行分支”控件以提升性能，并为13项必需活动分别创建分支。
获取各项活动的数据：在每个并行分支中添加Dataverse“列出行”操作。配置各操作以查询指定表和事件。例如，对于“案例已创建”，查询“Incidents”表，并使用类似createdon ge [your_start_date_variable]的OData筛选器。
整理数据：在每个“列出行”操作后添加“选择”数据操作。在“自”字段中，选择上一个“列出行”操作的value输出。在“映射”字段中创建所需列（ServiceRequest、ActivityName、EventTime等），并使用表达式将其映射到相应的Dataverse字段。例如，ActivityName可设为“案例已创建”这样的固定字符串。
合并活动数据：在所有并行分支之后添加“撰写”操作。使用union()表达式，将所有“选择”操作的数组输出合并为一个数组。表达式示例：union(outputs('Select_Case_Created')?['body'], outputs('Select_Case_Assigned')?['body'], ...)。
创建CSV文件：添加“创建CSV表”数据操作。在“自”属性中，使用包含合并数据的“撰写”操作输出。
保存文件：添加文件创建操作，将文件保存到所需位置，例如使用SharePoint“创建文件”或OneDrive“创建文件”。将“创建CSV表”操作的输出设为文件内容。
保存并测试：保存工作流并进行测试运行。确认工作流成功完成，并在指定位置生成包含预期数据的CSV文件。

配置

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

查询示例 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'

准备好开始了吗？

使用此数据模板，快速开启流程挖掘之旅，提升客户服务运营效率。立即优化工作流，改善客户体验。

返回改进指南

下载模板

优化客户服务：提升首次联系解决率，立即降低成本

识别瓶颈，将首次联系解决率提升至80%，让客户更满意。

开始免费试用

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

客户服务数据模板

客户服务数据模板

客户服务属性

客户服务活动

数据提取指南

使用Dynamics 365 Azure Synapse Link导出Dataverse数据

步骤

配置

查询示例 sql

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

步骤

配置

查询示例 sql

使用Dynamics 365 Dataverse Web API的Python脚本

步骤

配置

查询示例 python

使用Microsoft Power Automate自动导出数据

步骤

配置

查询示例 sql

准备好开始了吗？

优化客户服务：提升首次联系解决率，立即降低成本