您的事件管理数据模板

BMC Helix ITSM

← 返回改进指南

事件管理属性 (21) 事件管理活动 (14) 提取指南 (3)

您的事件管理数据模板

此模板全面介绍如何收集优化事件管理流程所需的关键数据，涵盖需要跟踪的重要属性和活动，并提供数据提取的实用指导。借助此资源，您可以简化数据准备，加快流程分析进程。

建议收集的属性
流程中需要跟踪的关键活动
数据系统提取指南

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

下载模板

事件管理属性

以下是建议纳入事件日志的数据字段，用于深入分析您的事件管理流程。

3 必需 8 建议 10 可选

	名称	说明
	事件ID IncidentId	每条事件记录的唯一标识符。
说明事件ID是每条事件的主键，从创建到关闭始终唯一标识该事件。它关联所有相关活动、日志和变更，帮助您完整、端到端地查看事件生命周期。在流程挖掘中，此属性至关重要，因为它定义了案例。具有相同事件ID的每个事件都被视为同一流程实例的一部分，从而可以还原并分析单个事件的处理过程。为什么重要这是连接事件生命周期中所有事件的核心案例标识符，使端到端流程分析成为可能。获取位置这是“HPD:Help Desk”表单中的“Incident Number”字段（字段ID：1000000161）。示例 INC000001234567INC000002345678INC000003456789
	事件时间戳 EventTimestamp	活动发生的准确日期和时间。
说明此时间戳记录事件生命周期中特定事件发生的时间，为根据原始数据重建流程路径提供所需的时间顺序。事件时间戳是所有基于时间的分析的基础，包括计算活动之间的周期时间、识别事件长时间等待的瓶颈，以及衡量整体解决时间。它构成流程分析的时间基础。为什么重要此时间戳提供事件的时间顺序，对于计算时长、识别瓶颈和了解流程时间线至关重要。获取位置 “HPD:Help Desk”表单中的“Last Modified Date”（字段ID：6）或特定日期字段。对于历史事件，取自审计日志的时间戳字段。示例 2023-10-26T10:00:00Z2023-10-26T10:15:32Z2023-10-27T14:22:05Z
	活动名称 ActivityName	事件生命周期中发生的具体事件或任务名称。
说明此属性描述事件在特定时间点执行的活动，例如“事件已报告”“已分配组”或“事件已解决”。这些活动构成流程图的基本要素。分析这些活动的顺序和频率，可以揭示实际流程路径、识别常见路径，并突出偏离标准流程的情况。这对于了解解决事件所采取的操作至关重要。为什么重要活动定义流程图中的各个步骤，用于可视化和分析事件管理工作流。获取位置通常根据“HPD:Help Desk”表单中“Status”（字段ID：7）和“Status_Reason”字段的变更，或根据“HPD:Help Desk Audit Log”表单得出。示例事件已报告已分配支持组解决方案已实施事件已关闭
	事件状态 IncidentStatus	事件发生时该事件的当前状态或历史状态。
说明此属性表示事件所处状态，例如“New”、“In Progress”、“Pending”、“Resolved”或“Closed”，用于呈现事件在生命周期中的位置。分析状态变更是了解事件管理流程的基础。它可用于定义活动、衡量事件在不同状态下的停留时间（例如处于“Pending”状态的时长），并识别可能卡住或处于非活动状态的事件。为什么重要跟踪状态变更是了解事件进展的关键，也有助于衡量事件在“Pending”或“In Progress”等特定状态下的停留时间。获取位置这是“HPD:Help Desk”表单中的“Status”字段（字段ID：7）。示例新建已分配处理中待处理已解决已关闭
	事件类别 IncidentCategory	事件的分类方式，通常采用分层结构。
说明事件分类提供了结构化的分类方式，通常采用多级层次结构（例如：第1层：硬件，第2层：笔记本电脑，第3层：电池）。这些数据对于路由、报告和趋势分析至关重要。在流程挖掘中，分类用于分别分析不同类型的事件。通过比较初始类别和最终类别，它支持Incident Categorization Accuracy仪表板，并帮助识别Root Cause Trends仪表板中的趋势。为什么重要分类有助于准确路由、分析趋势，并比较不同类型事件的处理绩效。获取位置这是“HPD:Help Desk”表单中的“Operational Categorization Tier 1/2/3”字段。示例硬件 > 笔记本电脑 > 电池软件 > 企业应用 > 登录错误网络 > 连接 > Wi-Fi
	优先级 Priority	分配给事件的优先级，用于确定处理紧迫程度。
说明优先级通常根据影响和紧急程度综合确定，并决定事件解决的顺序和速度。常见取值范围从“Critical”到“Low”。在流程挖掘中，按优先级分析事件对于性能分析至关重要。它有助于回答“高优先级事件是否达到SLA要求？”以及“低优先级事件是否经历了更长延迟？”等问题。按优先级筛选，可以聚焦最关键的业务问题。为什么重要此属性对于细分分析至关重要，可确保高优先级事件得到更快处理，并达到相应的服务级别目标。获取位置这是“HPD:Help Desk”表单中的“Priority”字段（字段ID：1000000164）。示例严重高中低
	分配组 AssignedGroup	负责处理事件的支持组。
说明此属性用于标识负责事件的团队或部门，例如“Service Desk”、“Network Team”或“Database Administrators”。跟踪分配情况是了解团队间工作流转的关键。此属性可用于分析团队间交接、识别由特定团队造成的瓶颈，以及衡量Incident Reassignment Rate。它有助于可视化事件在组织中的流转路径，并突出低效路由或知识缺口。为什么重要跟踪负责处理事件的组，有助于分析交接、识别重新分配循环，并定位特定团队中的瓶颈。获取位置这是“HPD:Help Desk”表单中的“Assigned Group”字段（字段ID：1000000217）。示例服务台网络运营应用支持二线基础设施服务
	受理人 Assignee	负责处理事件的个人用户。
说明受理人是特定时间段内负责处理事件的支持人员或技术人员。与分配组相比，它提供了更细粒度的信息。按受理人分析绩效，有助于识别高绩效人员、可能需要额外培训的人员，以及工作量分配不均的情况。对于复杂事件，还可以借此追踪相关人员采取的具体操作顺序。为什么重要提供工作量分配和个人绩效的细粒度视图，帮助识别高绩效人员或需要支持的人员。获取位置这是“HPD:Help Desk”表单中的“Assignee”字段（字段ID：1000000218）。示例 Bob SmithAlice JohnsonCharlie Brown
	是否违反SLA IsSlaBreached	用于标记事件是否在超过SLA目标日期后才解决。
说明如果事件的解决时间超过规定的SLA，该布尔属性为true。它为每个事件的SLA绩效提供明确的二元结果。该标记对于创建Incident SLA Performance Overview仪表板和计算“Incident SLA Compliance Rate”KPI至关重要。通过直接筛选和汇总所有未达到服务级别目标的事件，它简化了分析，也便于调查违反SLA的原因。为什么重要该标记简化了SLA合规分析，便于筛选所有违反SLA的事件并调查其根因。获取位置通过比较“Incident Resolved”事件时间戳与“SlaTargetDate”计算。如果解决时间戳晚于目标日期，该标记为true。示例 truefalse
	是否重新打开 IsReopened	用于标记事件在设置为“Resolved”状态后是否被重新打开。
说明如果事件状态在标记为“Resolved”后又变回活动状态（例如“In Progress”），该布尔属性为true。这表示初始修复无效或不完整。该指标可直接衡量返工，并用于计算“Incident Rework Rate”KPI。分析重新打开的事件，有助于识别低质量修复、测试不足或未得到妥善处理的重复问题，并支持Rework and Escalation Paths仪表板。为什么重要直接衡量返工情况和解决质量。重新打开率较高，通常表明修复措施无效或流程存在薄弱环节。获取位置通过分析事件的活动顺序计算。如果在“Resolution Implemented”活动之后出现“Incident Reopened”或类似活动，则该标记为true。示例 truefalse
	服务 Service	受事件影响的业务或技术服务。
说明此属性将事件关联到配置管理数据库（CMDB）中定义的特定服务，例如“Email Service”、“VPN Access”或“SAP Financials”。这种关联对于了解事件的业务影响至关重要。按服务分析事件，有助于识别产生大量事件的问题服务，突出与特定技术相关的重复问题，并支持问题管理中的趋势分析。为什么重要将事件关联到业务服务，对于分析影响以及识别最容易出现问题的服务至关重要。获取位置这是“HPD:Help Desk”表单中的“ServiceCI”字段，或表示受影响配置项（CI）的类似字段。示例企业电子邮件SAP ERP远程访问VPN人力资源门户
	SLA目标日期 SlaTargetDate	根据SLA，预计应完成事件解决的日期和时间。
说明该属性存储适用的服务级别协议（SLA）规定的事件解决期限。期限根据事件的优先级和规定的服务时间计算。该时间戳是衡量实际解决时间的基准，对于计算“Incident SLA Compliance Rate”KPI和构建可视化SLA绩效的仪表板至关重要。它还支持主动监控即将到期的事件。为什么重要这是衡量SLA合规情况的基准，可用于判断事件是否按时解决或违反协议。获取位置这些数据通常存储在“SLM:Measurement”表单中，并与事件关联，而不是直接存储在“HPD:Help Desk”字段中。示例 2023-10-26T14:00:00Z2023-10-27T09:00:00Z2023-11-01T17:00:00Z
	关闭代码 CloseCode	关闭事件时选择的代码，用于表示解决结果。
说明 Close Code以结构化方式概括事件的解决情况。示例包括“Resolved by User”“No Fault Found”“Duplicate Incident”和“Permanent Fix Applied”。该属性对于分析解决效果和结果非常有价值。它可以帮助识别未真正解决问题就关闭的事件，或突出显示用户经常自行解决问题的类别，从而发现改进知识库文章或自助服务工具的机会。为什么重要提供有关解决结果的结构化数据，帮助分析修复措施的有效性，并识别事件关闭方式的趋势。获取位置通常属于“HPD:Help Desk”表单中的解决或关闭信息。示例已远程解决重复问题用户操作错误无需采取措施
	最后数据更新时间 LastDataUpdate	表示该事件数据最近一次从源系统刷新时间的时间戳。
说明此属性记录最近一次数据提取的日期和时间，说明当前分析数据的新鲜度，有助于了解流程洞察的时效性。了解最后更新时间对于报表和仪表板至关重要，因为它可以告知用户数据的及时程度，并帮助管理对最新事件活动是否已纳入的预期。为什么重要表示数据的新鲜度，确保用户了解流程分析及其产生的洞察截至何时。获取位置此值通常在数据提取（ETL）过程中生成并写入数据集。示例 2023-11-01T02:00:00Z2023-11-02T02:00:00Z2023-11-03T02:00:00Z
	影响 Impact	衡量事件对业务流程影响程度的指标。
说明 Impact用于评估事件对业务造成不利影响的程度。通常采用分级标准，例如“Extensive/Widespread”“Significant/Large”“Moderate/Limited”或“Minor/Localized”。 Impact与Urgency结合后决定事件的Priority。按影响程度分析，有助于了解哪些事件造成了最大的业务中断，而不受其技术复杂度影响。这对于确定流程改进工作的优先级至关重要。为什么重要帮助量化事件对业务造成的严重程度，是确定优先级和聚焦高影响问题分析的关键因素。获取位置这是“HPD:Help Desk”表单中的“Impact”字段（字段ID：1000000163）。示例 1-广泛/大范围2-重大/大规模3-中等/有限范围4-轻微/局部
	提交人 Submitter	最初报告事件的人员。
说明 Submitter是遇到问题并报告问题的最终用户或客户，与负责处理事件的受派人不同。按提交人或其部门分析数据，有助于识别需要更多培训的特定用户群体，或受某类问题影响的群体。这为事件管理流程提供了以客户为中心的视角。为什么重要识别报告问题的用户，以便按用户部门、地点或角色进行分析，发现与用户相关的趋势。获取位置该信息记录在“HPD:Help Desk”表单的“Submitter”字段或客户信息相关字段中。示例 John DoeJane SmithPeter Jones
	根因 RootCause	导致事件发生的根本原因或最终原因。
说明 Root Cause是导致问题的根本因素。解决该因素后，事件便可避免再次发生。它通常在相关问题调查流程中确定。并非所有事件都有记录在案的根因，但分析该属性对于主动开展问题管理至关重要。它支持“Root Cause Identification Rate”KPI，并有助于创建跟踪重复问题趋势的仪表板，从而指导实施永久性解决方案。为什么重要识别根因对于问题管理以及减少重复事件数量的分析至关重要。获取位置该信息可能记录在事件专用的“Root Cause”字段中，也更常见于关联的“PBI:Problem Investigation”表单中。示例服务器磁盘空间不足网络配置错误2.1版本中的软件缺陷安全证书已过期
	渠道 Channel	用于报告事件的方式。
说明该属性说明事件通过何种方式提交，例如电话、电子邮件、自助服务门户或现场直接报障。它表示事件进入支持流程的入口。按渠道分析事件，可以发现哪些渠道最有效，以及哪些渠道产生的事件更容易或更难解决。这也有助于决定自动化或用户培训的投入方向，例如推广能够采集更完整初始数据的自助服务门户。为什么重要了解提交渠道有助于分析不同受理方式的效率，并指导自助服务或自动化方面的投入。获取位置这是“HPD:Help Desk”表单中的“Reported Source”字段（字段ID：1000000215）。示例电子邮件电话自助服务直接录入
	源系统 SourceSystem	提取事件数据的系统。
说明此属性用于标识数据来源，在包含多个ITSM工具或集成系统的环境中尤其有用。它可以确认数据来自预期来源，例如特定的BMC Helix ITSM实例。在分析中，它有助于区分生产、开发或旧系统之间可能存在差异的流程或数据特征，确保数据血缘清晰可信。为什么重要标识数据来源，对于数据验证以及管理多个集成系统中的分析至关重要。获取位置通常是在数据提取、转换和加载（ETL）过程中添加的静态值。示例 BMCHelixITSM_ProdITSM-EU-InstanceServiceManagement-APAC
	解决说明 Resolution	对解决事件所采取步骤的自由文本描述。
说明此字段包含人工编写的最终解决方案详细摘要，说明采取了哪些措施来修复问题并恢复用户服务。虽然这类文本数据缺乏结构，但可以使用文本挖掘技术进行分析，以识别常见的解决模式，提取与特定问题相关的关键词，或补充根因分析。它提供了结构化数据字段通常缺少的定性背景。为什么重要提供解决方案的定性细节，可通过文本挖掘发现结构化数据中不可见的模式。获取位置这是“HPD:Help Desk”表单中的“Resolution”字段（字段ID：1000000156）。示例已通过Active Directory重置用户密码。已清除浏览器缓存和Cookie，登录问题已解决。已重启3B机房弱电间的网络交换机。
	重新分配次数 ReassignmentCount	事件被重新分配给其他组的总次数。
说明该指标统计事件生命周期内“AssignedGroup”字段发生变化的次数。次数较多通常表明初始路由存在问题、首次接触未能解决，或问题复杂，需要多个团队参与。该属性直接支持“Incident Reassignment Rate”KPI和“Incident Reassignment Cycle Analysis”仪表板。它有助于量化工单在团队之间来回传递的“乒乓效应”，这种情况会造成明显延迟和流程低效。为什么重要量化低效路由和交接，帮助识别在团队间反复重新分配、陷入循环的事件。获取位置通过统计事件日志中指定Incident ID对应的“AssignedGroup”值变化次数计算。示例 0135

必需建议可选

事件管理活动

以下是应纳入事件日志的关键流程步骤和里程碑，用于准确发现流程并可视化流程。

5 建议 9 可选

	活动	说明
	事件已关闭	这是最后一个活动，表示解决方案已确认或确认期已结束后，事件记录正式关闭。当状态设置为“Closed”时记录。
为什么重要这是事件生命周期的最终结束事件。“Resolved”到“Closed”之间的时间代表用户确认和管理收尾阶段。获取位置此事件对应于“HPD:Help Desk”表单中的“Status”字段设置为“Closed”。时间戳记录在“Closed Date”字段和审计日志中。采集取自“Closed Date”时间戳或状态变更为“Closed”的时间。事件类型 inferred
	事件已报告	此活动表示系统中首次创建事件记录。它直接取自核心事件管理表单中的事件创建时间戳。
为什么重要这是事件生命周期的主要开始事件，对于计算整体解决时长和了解事件到达率至关重要。获取位置此事件对应于“HPD:Help Desk”表单中的记录创建。时间戳通常取自“Submit Date”或“Reported Date”字段。采集取自HPD:Help Desk表单中的“Submit Date”时间戳。事件类型 explicit
	事件已解决	此活动表示从服务台角度看事件已正式解决，但尚未最终关闭。当事件状态设置为“Resolved”时记录。
为什么重要这是衡量SLA合规性和解决时长最重要的里程碑，表示用户的服务已恢复。获取位置此事件对应于“HPD:Help Desk”表单中的“Status”字段设置为“Resolved”。时间戳记录在“Last Resolved Date”字段和审计日志中。采集取自审计日志中状态变更为“Resolved”的时间戳。事件类型 inferred
	已分配支持组	此活动表示事件首次被分配给特定支持组进行调查。通常根据事件创建后“Assigned Group”字段首次填充的时间推断。
为什么重要这是表示主动处理开始的关键里程碑。跟踪首次分配所需时间，对于评估响应时长和初始路由效率至关重要。获取位置根据“HPD:Help Desk”表单的审计日志（“HPD:HelpDesk_AuditLogSystem”）推断，记录“Assigned Group”字段首次填充的时间。采集取自“Assigned Group”字段首次填充时的时间戳。事件类型 inferred
	调查已开始	表示支持人员已开始主动处理事件。通常根据状态从“Assigned”变更为“In Progress”来推断。
为什么重要这一里程碑标志着事件从队列等待转入主动诊断。分析调查开始前的等待时间，有助于发现资源瓶颈，并支持“Diagnosis & Investigation Bottlenecks”仪表板。获取位置根据“HPD:Help Desk”表单中的状态变更推断。当“Status”字段变为“In Progress”时触发该事件，时间戳取自审计日志。采集根据HPD:HelpDesk_AuditLogSystem中状态变更为“In Progress”推断。事件类型 inferred
	SLA违约	这是一个计算得出的事件，当事件解决时间超过规定的服务级别协议目标时发生。通过比较解决时间戳与SLA到期日期得出。
为什么重要此活动是“Incident SLA Performance Overview”仪表板和“Incident SLA Compliance Rate”KPI的基础，可直接标记未达到服务承诺的事件。获取位置通过比较“HPD:Help Desk”表单中的“Last Resolved Date”和“Target Date”（SLA到期日期）计算。如果事件尚未解决，则可以根据当前时间计算。采集计算得出的事件：当“Last Resolved Date”>“Target Date”时发生。事件类型 calculated
	事件已分类	表示事件已完成运营分类和产品分类，并已设置优先级。通常根据分类字段首次填充或最后修改的时间推断。
为什么重要准确、及时的分类对于高效路由和报表分析至关重要。分析此活动有助于发现初始分诊中的延迟或错误，并支持“Incident Categorization Accuracy”仪表板。获取位置根据审计日志（“HPD:HelpDesk_AuditLogSystem”）推断，该日志记录了“HPD:Help Desk”表单中“Operational Categorization Tier 1-3”、“Product Categorization Tier 1-3”和“Priority”等字段的变更。采集取自创建后分类或优先级字段最后一次更新的时间戳。事件类型 inferred
	事件已取消	此活动表示因事件误创建或已不再相关而终止该事件。当事件状态设置为“Cancelled”时记录。
为什么重要这是事件的终止状态，与成功解决不同。分析已取消事件，有助于发现事件创建渠道或重复报告方面的问题。获取位置当“HPD:Help Desk”表单中的“Status”字段更新为“Cancelled”时推断。时间戳记录在审计日志中。采集根据审计日志中状态变更为“Cancelled”推断。事件类型 inferred
	事件已重新打开	表示事件此前已标记为已解决，但因问题仍然存在而被重新激活。通常根据状态从“Resolved”变更回“In Progress”或“Assigned”等活动状态来推断。
为什么重要此活动直接衡量返工情况和初始解决方案的有效性。重新打开的事件数量较多，通常表明修复质量不佳，并支持“Incident Rework Rate”KPI。获取位置根据审计日志（“HPD:HelpDesk_AuditLogSystem”），检测指定事件ID的“Status”从“Resolved”变更为“In Progress”或“Assigned”来推断。采集根据状态从“Resolved”变更为活动状态推断。事件类型 inferred
	已收到用户确认	表示用户主动确认所提供的解决方案已解决其问题。它可能是一个明确记录的事件，也可能根据关闭前的备注或相关系统操作推断。
为什么重要跟踪此活动比单纯等待自动关闭更能准确反映用户验证过程。它有助于衡量“Average User Confirmation Time”KPI并发现沟通缺口。获取位置此活动可能难以可靠捕获。可以根据事件变为“Closed”前的工作日志条目或特定状态原因更新进行推断，可能需要分析“HPD:WorkLog”表单。采集根据关闭前的特定工作日志条目或状态原因更新推断。事件类型 inferred
	已转移至其他组	当事件从一个支持组重新分配至另一个支持组时，会发生此活动。通常通过检测首次分配后“Assigned Group”字段的变更来推断。
为什么重要频繁转派通常表明初始路由错误或知识存在缺口。跟踪此活动对于“Incident Reassignment Cycle Analysis”仪表板和“Incident Reassignment Rate”KPI至关重要。获取位置根据审计日志（“HPD:HelpDesk_AuditLogSystem”）识别“HPD:Help Desk”表单中“Assigned Group”字段在首次填充后的后续变更，从而推断该活动。采集识别初始分配后“Assigned Group”字段的变更。事件类型 inferred
	等待客户输入	表示事件处理因等待用户提供信息或采取行动而暂停。通常根据状态变更为“Pending”来推断。
为什么重要此活动有助于区分支持人员工作时间和客户等待时间。分析处于该状态的时长，对于了解用户响应延迟如何影响整体解决时长至关重要。获取位置当“HPD:Help Desk”表单中的“Status”字段更新为“Pending”时推断。具体原因通常记录在“Status_Reason”字段中。采集根据HPD:HelpDesk_AuditLogSystem中状态变更为“Pending”推断。事件类型 inferred
	等待用户确认	解决方案实施后，支持团队等待用户确认修复是否成功时，会发生此活动。通常由“Resolved”状态表示，此时自动关闭计时开始。
为什么重要此活动对于“User Confirmation & Verification Delays”仪表板至关重要。它有助于量化提供修复方案到获得用户验证之间的延迟，这种延迟可能会人为延长事件生命周期。获取位置当“HPD:Help Desk”表单中的“Status”变为“Resolved”时开始。时长从“Last Resolved Date”计算，直到事件变为“Closed”或“Reopened”。采集状态变更为“Resolved”时开始，状态变更为“Closed”或“In Progress”时结束。事件类型 inferred
	解决方案已实施	此活动表示支持团队已应用修复方案或变通方案。通常在事件状态变为“Resolved”时推断。
为什么重要这是表明解决方案已交付的关键里程碑，也是衡量调查完成后应用修复方案所需时间的重要数据点。获取位置通常在“HPD:Help Desk”表单中的“Status”变更为“Resolved”时推断。时间戳记录在“Last Resolved Date”字段和审计日志中。采集取自“Last Resolved Date”时间戳或状态变更为“Resolved”的时间。事件类型 inferred

建议可选

提取指南

如何从BMC Helix ITSM获取数据

步骤

访问Smart Reporting：登录您的BMC Helix ITSM环境。在主应用菜单中转到报告，启动Smart Reporting。
创建新报告：进入Smart Reporting后，点击创建按钮并选择报告。选择高级报告选项，以使用Freehand SQL等更复杂的功能。
选择数据源：出现提示时，选择主要ITSM数据源。选择事件管理视图，该视图是构建在核心事件表单之上的语义层。
使用Freehand SQL：在报告生成器中，不要拖放字段，而是转到数据源选项并选择Freehand SQL。系统会打开一个编辑器，您可以在其中编写自定义SQL查询。
输入SQL查询：复制本文档查询部分提供的完整SQL查询，并粘贴到Freehand SQL编辑器中。您必须根据具体需求替换[START_DATE]、[END_DATE]和[YOUR_COMPANY_NAME]的占位值。
验证并预览数据：使用SQL编辑器中的验证或预览功能检查语法错误并查看输出示例。这样可以在构建完整报告前，确认查询能够在您的环境中正常运行。
设置报告格式：查询验证通过后，进入报告设计步骤。SQL查询中定义的列将作为字段提供。确保报告表中包含所有列，包括IncidentId、ActivityName、EventTimestamp等。
应用报告筛选器（可选）：虽然主要日期筛选器已包含在SQL查询中，但您仍可根据需要在报告级别添加交互式筛选器，例如按Priority或AssignedGroup筛选。
运行并保存报告：执行报告以生成完整数据集。确认结果符合要求后，以描述性名称保存报告，例如“事件管理事件日志”。
导出CSV：在屏幕上显示报告结果后，使用导出功能，选择CSV作为导出格式。系统会将完整事件日志文件下载到本地计算机。
安排导出（可选）：如需持续分析，您可以安排报告自动运行。在报告的分发设置中配置计划，例如每日或每周运行，并将目标设置为文件共享或电子邮件，同时确保格式为CSV。

配置

前提条件：您必须拥有BMC Helix账户，并具备访问Smart Reporting、创建报告、使用Freehand SQL以及查询Incident Management数据源的权限。
报告类型：必须使用包含Freehand SQL功能的Advanced Report，通过UNION ALL语句构建事件日志结构。
数据源视图：使用标准Incident Management视图，该视图基于HPD:Help Desk表单及其相关连接。
日期范围筛选：设置日期范围对控制性能至关重要。所提供的查询包含[START_DATE]和[END_DATE]占位符。建议提取3至6个月的数据，以获得有意义的分析结果，同时避免系统负载过高。
公司筛选：查询包含[YOUR_COMPANY_NAME]占位符，用于将数据限定到特定运营公司。在多租户环境中，这是确保报告准确性的必要条件。
性能注意事项：事件日志查询较为复杂，需要连接多个表。在大型数据集上运行可能消耗大量系统资源。请始终安排在业务低峰期执行大型导出。

a 示例查询 sql

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Incident Reported' AS "ActivityName",
  hpd."Submit Date" AS "EventTimestamp",
  hpd."Status" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Incident Categorized' AS "ActivityName",
  hpd."Last Modified Date" AS "EventTimestamp",
  hpd."Status" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."First Assigned Date" IS NOT NULL AND hpd."Operational Categ Tier 1" IS NOT NULL AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT 
  hpd."Incident Number" AS "IncidentId",
  'Group Assigned' AS "ActivityName",
  hpd."First Assigned Date" AS "EventTimestamp",
  hpd."Status" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."First Assigned Date" IS NOT NULL AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  aud."New_Value" AS "ActivityName",
  aud."Audit_Date" AS "EventTimestamp",
  aud."New_Value" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
JOIN HPD_HelpDesk_AuditLogSystem aud ON hpd."Incident Number" = aud."Original_Request_ID"
WHERE aud."Fields_Changed" LIKE '%Status%' AND aud.New_Value IN ('In Progress', 'Pending')
  AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'
  AND aud.New_Value = 'In Progress' AND aud."Audit_Date" = (SELECT MIN(a2."Audit_Date") FROM HPD_HelpDesk_AuditLogSystem a2 WHERE a2."Original_Request_ID" = hpd."Incident Number" AND a2."New_Value" = 'In Progress' AND a2."Fields_Changed" LIKE '%Status%')

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Transferred To Another Group' AS "ActivityName",
  aud."Audit_Date" AS "EventTimestamp",
  hpd."Status" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  aud."New_Value" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
JOIN HPD_HelpDesk_AuditLogSystem aud ON hpd."Incident Number" = aud."Original_Request_ID"
WHERE aud."Fields_Changed" LIKE '%Assigned Group%' AND aud."Audit_Date" > hpd."First Assigned Date"
  AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Pending Customer Input' AS "ActivityName",
  aud."Audit_Date" AS "EventTimestamp",
  'Pending' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
JOIN HPD_HelpDesk_AuditLogSystem aud ON hpd."Incident Number" = aud."Original_Request_ID"
WHERE aud."Fields_Changed" LIKE '%Status%' AND aud."New_Value" = 'Pending'
  AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Resolution Implemented' AS "ActivityName",
  hpd."Last Resolved Date" AS "EventTimestamp",
  'Resolved' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Last Resolved Date" IS NOT NULL AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Awaiting User Confirmation' AS "ActivityName",
  hpd."Last Resolved Date" AS "EventTimestamp",
  'Resolved' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Status" = 'Resolved' AND hpd."Last Resolved Date" IS NOT NULL AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Incident Reopened' AS "ActivityName",
  hpd."Re-Opened Date" AS "EventTimestamp",
  hpd."Status" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  'Yes' AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Re-Opened Date" IS NOT NULL AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Incident Resolved' AS "ActivityName",
  hpd."Last Resolved Date" AS "EventTimestamp",
  'Resolved' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Status" >= 'Resolved' AND hpd."Last Resolved Date" IS NOT NULL AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'User Confirmation Received' AS "ActivityName",
  hpd."Closed Date" AS "EventTimestamp",
  'Closed' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Status" = 'Closed' AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'SLA Breach' AS "ActivityName",
  hpd."Estimated Resolution Date" AS "EventTimestamp", -- Represents the SLA due date
  hpd."Status" AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  'Yes' AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."SLM Status" = 'Service Target Breached' AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Incident Cancelled' AS "ActivityName",
  hpd."Last Modified Date" AS "EventTimestamp",
  'Cancelled' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Status" = 'Cancelled' AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

UNION ALL

SELECT
  hpd."Incident Number" AS "IncidentId",
  'Incident Closed' AS "ActivityName",
  hpd."Closed Date" AS "EventTimestamp",
  'Closed' AS "IncidentStatus",
  hpd."Priority" AS "Priority",
  hpd."Assigned Group" AS "AssignedGroup",
  hpd."Assignee" AS "Assignee",
  hpd."ServiceCI" AS "Service",
  CONCAT(CONCAT(hpd."Operational Categ Tier 1", '/'), CONCAT(hpd."Operational Categ Tier 2", CONCAT('/', hpd."Operational Categ Tier 3"))) AS "IncidentCategory",
  (CASE WHEN hpd."Last Resolved Date" IS NOT NULL THEN (hpd."Last Resolved Date" - hpd."Submit Date") * 86400 ELSE NULL END) AS "IncidentResolutionTime",
  (CASE WHEN hpd."SLM Status" = 'Service Target Breached' THEN 'Yes' ELSE 'No' END) AS "IsSlaBreached",
  (CASE WHEN hpd."Re-Opened Date" IS NOT NULL THEN 'Yes' ELSE 'No' END) AS "IsReopened"
FROM HPD_Help_Desk hpd
WHERE hpd."Status" = 'Closed' AND hpd."Submit Date" >= '[START_DATE]' AND hpd."Submit Date" < '[END_DATE]' AND hpd.Company = '[YOUR_COMPANY_NAME]'

步骤

确认数据库凭据和架构：获取底层BMC Helix ITSM AR System数据库（例如SQL Server、Oracle）的只读凭据。同时确认事件管理表单（HPD:Help Desk）及其审计日志对应的架构和表名。主表通常命名为T<form_id>（例如T1234），审计日志通常命名为H<form_id>或[form_name]_AuditLogSystem。
连接数据库：使用DBeaver、SQL Server Management Studio（SSMS）或Oracle SQL Developer等标准SQL客户端，利用上一步获取的凭据连接数据库。
检查并自定义SQL查询：将提供的SQL查询复制到客户端中。仔细检查占位符，并根据您的环境替换为正确值：
- [Your_HPD_Help_Desk_Table]：替换为HPD:Help Desk表单对应的实际表名。
- [Your_HPD_Help_Desk_Audit_Table]：替换为HPD:Help Desk表单关联审计日志的实际表名。
- 'YYYY-MM-DD'和'YYYY-MM-DD'：设置数据提取的开始日期和结束日期。
处理时间戳转换：所提供的查询使用DATEADD(s, [TimestampColumn], '1970-01-01')转换Unix时间戳，该函数适用于SQL Server。如果使用Oracle或其他数据库，必须相应调整函数。Oracle中的等效写法为TO_DATE('1970-01-01', 'YYYY-MM-DD') + ( [TimestampColumn] / 86400 )。
执行查询：在数据库中运行修改后的查询。根据日期范围和事件数据量，查询可能需要几分钟才能完成。
检查结果：查询完成后，快速检查输出结果。确认IncidentId、ActivityName和EventTimestamp列已按预期填充，并查看活动是否多样，以确保UNION ALL语句正常工作。
将数据导出为CSV：通过SQL客户端将完整结果集导出为CSV文件。确保文件使用UTF-8编码，并包含带列名的表头行。
准备上传：上传到流程挖掘工具前，确保EventTimestamp列采用统一格式，例如YYYY-MM-DD HH:MI:SS。如果SQL查询运行正确，则无需进一步转换数据。

配置

前提条件：您需要对BMC Helix ITSM应用数据库拥有只读用户访问权限，并了解HPD:Help Desk表单及其审计日志对应的具体表名。不同系统安装环境中的表名可能有所不同。
日期范围筛选：查询在BaseIncidents CTE中包含WHERE子句，用于按Submit_Date筛选。强烈建议先使用有限的日期范围，例如3至6个月，以控制查询性能和数据量。例如：WHERE hpd.Submit_Date >= '2023-01-01' AND hpd.Submit_Date < '2023-07-01'。
公司筛选：如果您的ITSM实例为多租户环境，请在BaseIncidents CTE的WHERE子句中添加要分析公司的筛选条件，例如AND hpd.Company = '[Your Company Name]'。
性能注意事项：直接查询数据库，尤其是连接大型审计日志表并执行多次UNION ALL操作时，可能消耗大量资源。建议在业务低峰期执行大型数据提取，避免影响系统性能。
时间戳转换：查询假设时间戳以Unix时间戳格式存储，即自1970-01-01起的秒数。请确认您的系统采用此格式，并验证查询中的转换函数是否适用于具体数据库技术，例如SQL Server或Oracle。

a 示例查询 sql

WITH BaseIncidents AS (
    -- Select the base set of incidents within the desired date range
    SELECT
        hpd.Incident_Number AS IncidentId,
        hpd.Status AS FinalStatus,
        hpd.Priority,
        hpd.Assigned_Group AS FinalAssignedGroup,
        hpd.Assignee AS FinalAssignee,
        hpd.Service_CI AS Service,
        hpd.Categorization_Tier_1 + ' | ' + hpd.Categorization_Tier_2 + ' | ' + hpd.Categorization_Tier_3 AS IncidentCategory,
        DATEDIFF(SECOND, hpd.Submit_Date, hpd.Last_Resolved_Date) AS IncidentResolutionTime,
        CASE WHEN hpd.Last_Resolved_Date > hpd.Target_Date THEN 'true' ELSE 'false' END AS IsSlaBreached,
        hpd.Submit_Date,
        hpd.Last_Resolved_Date,
        hpd.Closed_Date,
        hpd.Target_Date,
        hpd.Entry_ID -- Required for joining with the audit log
    FROM [Your_HPD_Help_Desk_Table] hpd
    WHERE hpd.Submit_Date BETWEEN '[START_DATE_YYYY-MM-DD]' AND '[END_DATE_YYYY-MM-DD]'
),
AuditChanges AS (
    -- Process the audit log to identify key changes in Status and Assigned Group
    SELECT 
        audit.Entry_ID,
        audit.Log_Key_Value,
        audit.Fields_Changed,
        DATEADD(s, audit.Log_Key_Value, '1970-01-01') AS EventTimestamp,
        LAG(audit.Fields_Changed, 1, '') OVER (PARTITION BY audit.Entry_ID, SUBSTRING(audit.Fields_Changed, 1, 8) ORDER BY audit.Log_Key_Value) AS Previous_Value
    FROM [Your_HPD_Help_Desk_Audit_Table] audit
    WHERE 
        -- Field ID 7 is for Status, Field ID 1000000217 is for Assigned Group
        (SUBSTRING(audit.Fields_Changed, 1, 8) = '7       ' OR SUBSTRING(audit.Fields_Changed, 1, 8) = '1000000217')
        AND audit.Entry_ID IN (SELECT Entry_ID FROM BaseIncidents)
),
ReopenedFlag AS (
    -- Identify incidents that have been reopened
    SELECT DISTINCT Entry_ID, 'true' AS IsReopened
    FROM AuditChanges
    WHERE 
        SUBSTRING(Fields_Changed, 1, 8) = '7       ' -- Status field
        AND CHARINDEX('Resolved', Previous_Value) > 0
        AND CHARINDEX('Resolved', Fields_Changed) = 0
)
-- Combine all activities into a single event log
SELECT
    b.IncidentId,
    'Incident Reported' AS ActivityName,
    b.Submit_Date AS EventTimestamp,
    'New' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Incident Categorized' AS ActivityName,
    a.EventTimestamp,
    'Assigned' AS IncidentStatus, -- Inferred status at time of categorization/assignment
    b.Priority,
    LTRIM(RTRIM(SUBSTRING(a.Fields_Changed, 9, 255))) AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN (
    -- Find the first assignment event, used as a proxy for categorization
    SELECT Entry_ID, MIN(EventTimestamp) AS FirstAssignmentTime
    FROM AuditChanges
    WHERE SUBSTRING(Fields_Changed, 1, 8) = '1000000217'
    GROUP BY Entry_ID
) first_assign ON b.Entry_ID = first_assign.Entry_ID
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID AND a.EventTimestamp = first_assign.FirstAssignmentTime AND SUBSTRING(a.Fields_Changed, 1, 8) = '1000000217'
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Group Assigned' AS ActivityName,
    a.EventTimestamp,
    'Assigned' AS IncidentStatus,
    b.Priority,
    LTRIM(RTRIM(SUBSTRING(a.Fields_Changed, 9, 255))) AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE SUBSTRING(a.Fields_Changed, 1, 8) = '1000000217' AND a.Previous_Value = ''
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Investigation Initiated' AS ActivityName,
    a.EventTimestamp,
    'In Progress' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE 
    SUBSTRING(a.Fields_Changed, 1, 8) = '7       '
    AND CHARINDEX('Assigned', a.Previous_Value) > 0
    AND CHARINDEX('In Progress', a.Fields_Changed) > 0
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Transferred To Another Group' AS ActivityName,
    a.EventTimestamp,
    'Assigned' AS IncidentStatus,
    b.Priority,
    LTRIM(RTRIM(SUBSTRING(a.Fields_Changed, 9, 255))) AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE SUBSTRING(a.Fields_Changed, 1, 8) = '1000000217' AND a.Previous_Value <> ''
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Pending Customer Input' AS ActivityName,
    a.EventTimestamp,
    'Pending' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE SUBSTRING(a.Fields_Changed, 1, 8) = '7       ' AND CHARINDEX('Pending', a.Fields_Changed) > 0
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

-- Capturing this as 'Resolution Implemented' based on status change
SELECT
    b.IncidentId,
    'Resolution Implemented' AS ActivityName,
    a.EventTimestamp,
    'Resolved' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE SUBSTRING(a.Fields_Changed, 1, 8) = '7       ' AND CHARINDEX('Resolved', a.Fields_Changed) > 0
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

-- Capturing 'Awaiting User Confirmation' from the same status change event
SELECT
    b.IncidentId,
    'Awaiting User Confirmation' AS ActivityName,
    a.EventTimestamp,
    'Resolved' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE SUBSTRING(a.Fields_Changed, 1, 8) = '7       ' AND CHARINDEX('Resolved', a.Fields_Changed) > 0
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Incident Reopened' AS ActivityName,
    a.EventTimestamp,
    LTRIM(RTRIM(SUBSTRING(a.Fields_Changed, 9, 255))) AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    'true' AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE 
    SUBSTRING(a.Fields_Changed, 1, 8) = '7       ' 
    AND CHARINDEX('Resolved', a.Previous_Value) > 0 
    AND CHARINDEX('Resolved', a.Fields_Changed) = 0
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

-- Using Last_Resolved_Date for the official 'Incident Resolved' activity
SELECT
    b.IncidentId,
    'Incident Resolved' AS ActivityName,
    b.Last_Resolved_Date AS EventTimestamp,
    'Resolved' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID
WHERE b.Last_Resolved_Date IS NOT NULL

UNION ALL

-- Inferring 'User Confirmation Received' from the closure event
SELECT
    b.IncidentId,
    'User Confirmation Received' AS ActivityName,
    b.Closed_Date AS EventTimestamp,
    'Closed' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID
WHERE b.Closed_Date IS NOT NULL

UNION ALL

SELECT
    b.IncidentId,
    'SLA Breach' AS ActivityName,
    b.Target_Date AS EventTimestamp,
    b.FinalStatus AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    'true' AS IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID
WHERE b.IsSlaBreached = 'true' AND b.Target_Date IS NOT NULL

UNION ALL

SELECT
    b.IncidentId,
    'Incident Cancelled' AS ActivityName,
    a.EventTimestamp,
    'Cancelled' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
JOIN AuditChanges a ON b.Entry_ID = a.Entry_ID
WHERE SUBSTRING(a.Fields_Changed, 1, 8) = '7       ' AND CHARINDEX('Cancelled', a.Fields_Changed) > 0
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID

UNION ALL

SELECT
    b.IncidentId,
    'Incident Closed' AS ActivityName,
    b.Closed_Date AS EventTimestamp,
    'Closed' AS IncidentStatus,
    b.Priority,
    b.FinalAssignedGroup AS AssignedGroup,
    b.FinalAssignee AS Assignee,
    b.Service,
    b.IncidentCategory,
    b.IncidentResolutionTime,
    b.IsSlaBreached,
    ISNULL(rf.IsReopened, 'false') AS IsReopened
FROM BaseIncidents b
LEFT JOIN ReopenedFlag rf ON b.Entry_ID = rf.Entry_ID
WHERE b.Closed_Date IS NOT NULL;

步骤

前提条件和环境设置：确保已安装Python 3.6或以上版本。在命令行或终端中运行pip install requests，安装必要的requests库。获取BMC Helix用户账户的API凭据（用户名和密码），该账户至少应对“HPD:Help Desk”和“HPD:Help Desk Audit Log”表单具有只读权限。
配置Python脚本：将提供的Python脚本复制到文件中，例如extract_incidents.py。打开文件，修改顶部的配置变量，包括HELIX_HOSTNAME、HELIX_USERNAME、HELIX_PASSWORD、START_DATE、END_DATE和COMPANY_NAME，使其符合您的环境和所需数据范围。
了解身份验证：脚本首先调用/api/jwt/login端点，与您的BMC Helix实例进行身份验证。脚本获取JSON Web Token（JWT），并将其作为后续所有数据请求的授权标头。
运行脚本：使用命令python extract_incidents.py从终端运行脚本。脚本将开始执行，并在控制台显示进度。
初始事件数据提取：脚本查询“HPD:Help Desk”REST API端点，获取指定日期范围内创建的所有事件列表。脚本使用分页（limit和offset参数）高效处理大量事件，避免对API造成过大压力。
提取审计日志：对于上一步获取的每个事件，脚本都会单独调用“HPD:Help Desk Audit Log”表单的API，获取每个事件的详细变更历史。这对于识别状态变更和重新分配等活动至关重要。
事件推导和逻辑处理：脚本在内存中处理数据，将主事件记录与对应的审计日志合并，然后遍历排序后的审计日志条目，按逻辑推导指定活动。例如，日志中首次出现“Assigned Group”字段时，创建“Group Assigned”事件；状态从“Resolved”变更为“In Progress”时，生成“Incident Reopened”事件。
处理计算事件：脚本会计算未直接记录在日志中的事件。“Incident Reported”根据“Submit Date”创建；“SLA Breach”通过比较“Last Resolved Date”和“Target Date”推导。
整理数据：识别每项活动后，脚本会创建一个字典，其中包含IncidentId、ActivityName、EventTimestamp以及其他所需和建议的属性。所有字典都会汇总到一个列表中。
生成CSV文件：处理完所有事件及其对应活动后，脚本使用Python内置的csv库，将最终事件日志写入名为bmc_helix_incident_log.csv的文件。该文件会创建在脚本运行所在的同一目录中。
最终检查和上传：使用电子表格程序打开生成的CSV文件并检查内容，确认列和数据正确。现在，该文件已准备好上传到ProcessMind进行分析。

配置

HELIX_HOSTNAME：您的BMC Helix ITSM环境的基础URL，例如“https://your-company.bmc.com”。
HELIX_USERNAME：用于身份验证的API账户用户名。
HELIX_PASSWORD：API账户密码。在生产环境中，建议使用环境变量或安全的密钥管理工具保存凭据。
START_DATE / END_DATE：数据提取的日期范围，格式为“YYYY-MM-DDTHH:MM:SS.sssZ”。首次分析建议选择3至6个月的数据，以获得具有代表性的样本，同时避免数据量过大。
COMPANY_NAME：用于筛选事件的公司名称。在多租户环境中，这对于正确限定数据范围至关重要。如不需要筛选，可将其设置为None。
PAGE_SIZE：每次API请求获取的事件数量。通常500至1000较为高效，但可能需要根据实例性能和API速率限制进行调整。
前提条件：脚本要求BMC Helix用户账户至少拥有HPD:Help Desk和HPD:Help Desk Audit Log表单的读取权限。缺少任一表单的访问权限都会导致提取不完整。

a 示例查询 python

import requests
import json
import csv
from datetime import datetime
import time

class=class="hl-string">"hl-comment"># --- Configuration Settings ---
HELIX_HOSTNAME = class="hl-string">"https://[your-helix-instance].bmc.com"
HELIX_USERNAME = class="hl-string">"[your_api_username]"
HELIX_PASSWORD = class="hl-string">"[your_api_password]"

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

class=class="hl-string">"hl-comment"># Optional: Filter by company for multi-tenant environments
COMPANY_NAME = class="hl-string">"[Your Company Name]"  class=class="hl-string">"hl-comment"># Set to None if not needed

PAGE_SIZE = 1000
OUTPUT_CSV_FILE = class="hl-string">'bmc_helix_incident_log.csv'

class=class="hl-string">"hl-comment"># --- End of Configuration ---

def get_jwt_token(session):
    class="hl-string">"""Authenticates with the BMC Helix API and returns a JWT token."""
    login_url = fclass="hl-string">"{HELIX_HOSTNAME}/api/jwt/login"
    auth_data = {class="hl-string">'username': HELIX_USERNAME, class="hl-string">'password': HELIX_PASSWORD}
    try:
        response = session.post(login_url, data=auth_data, headers={class="hl-string">'Content-Type': class="hl-string">'application/x-www-form-urlencoded'})
        response.raise_for_status()
        return response.text
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Error during authentication: {e}")
        return None

def get_all_incidents(session, token):
    class="hl-string">"""Retrieves all incident records within the specified date range."""
    all_incidents = []
    offset = 0
    url = fclass="hl-string">"{HELIX_HOSTNAME}/api/arsys/v1/entry/HPD:Help Desk"
    headers = {class="hl-string">'Authorization': fclass="hl-string">'AR-JWT {token}', class="hl-string">'Content-Type': class="hl-string">'application/json'}
    
    qualification = fclass="hl-string">"'Submit Date' >= \"{START_DATE}\class="hl-string">" AND 'Submit Date' <= \"{END_DATE}\class="hl-string">""
    if COMPANY_NAME:
        qualification += fclass="hl-string">" AND 'Company' = \"{COMPANY_NAME}\class="hl-string">""

    fields = (
        class="hl-string">"Incident Number,Submit Date,Last Resolved Date,Closed Date,Status,Priority,Assigned Group,"
        class="hl-string">"Assignee,ServiceCI,Operational Categorization Tier 1,Operational Categorization Tier 2,"
        class="hl-string">"Product Categorization Tier 1,Product Categorization Tier 2,Target Date"
    )

    while True:
        params = {class="hl-string">'q': qualification, class="hl-string">'fields': fclass="hl-string">'values({fields})', class="hl-string">'limit': PAGE_SIZE, class="hl-string">'offset': offset}
        print(fclass="hl-string">"Fetching incidents, offset: {offset}...")
        try:
            response = session.get(url, headers=headers, params=params)
            response.raise_for_status()
            data = response.json()
            entries = data.get(class="hl-string">'entries', [])
            if not entries:
                break
            all_incidents.extend(entries)
            offset += len(entries)
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching incidents: {e}")
            break
    return all_incidents

def get_audit_log(session, token, incident_id):
    class="hl-string">"""Retrieves the audit log for a single incident."""
    url = fclass="hl-string">"{HELIX_HOSTNAME}/api/arsys/v1/entry/HPD:Help Desk Audit Log"
    headers = {class="hl-string">'Authorization': fclass="hl-string">'AR-JWT {token}', class="hl-string">'Content-Type': class="hl-string">'application/json'}
    qualification = fclass="hl-string">"'Incident Number' = \"{incident_id}\class="hl-string">""
    fields = class="hl-string">"Log,Fields Changed,Log Key,Audit Date"
    params = {class="hl-string">'q': qualification, class="hl-string">'fields': fclass="hl-string">'values({fields})'}
    try:
        response = session.get(url, headers=headers, params=params)
        response.raise_for_status()
        data = response.json()
        return sorted(data.get(class="hl-string">'entries', []), key=lambda x: x[class="hl-string">'values'][class="hl-string">'Audit Date'])
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Error fetching audit log for {incident_id}: {e}")
        return []

def parse_timestamp(ts):
    if not ts: return None
    try:
        return datetime.strptime(ts, class="hl-string">'%Y-%m-%dT%H:%M:%S.%f%z').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
    except ValueError:
        try:
            return datetime.strptime(ts, class="hl-string">'%Y-%m-%dT%H:%M:%S%z').strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
        except ValueError:
            return ts

def main():
    event_log = []
    with requests.Session() as session:
        token = get_jwt_token(session)
        if not token:
            return

        incidents = get_all_incidents(session, token)
        total = len(incidents)
        print(fclass="hl-string">"Found {total} incidents to process.")

        for index, incident_entry in enumerate(incidents):
            incident = incident_entry[class="hl-string">'values']
            incident_id = incident.get(class="hl-string">'Incident Number')
            print(fclass="hl-string">"Processing incident {index + 1}/{total}: {incident_id}")

            base_attributes = {
                class="hl-string">'IncidentId': incident_id,
                class="hl-string">'Priority': incident.get(class="hl-string">'Priority'),
                class="hl-string">'Service': incident.get(class="hl-string">'ServiceCI'),
                class="hl-string">'IncidentCategory': fclass="hl-string">"{incident.get('Product Categorization Tier 1', '')} / {incident.get('Product Categorization Tier 2', '')}",
            }
            
            class=class="hl-string">"hl-comment"># 1. Incident Reported
            event_log.append({
                **base_attributes,
                class="hl-string">'ActivityName': class="hl-string">'Incident Reported',
                class="hl-string">'EventTimestamp': parse_timestamp(incident.get(class="hl-string">'Submit Date')),
                class="hl-string">'IncidentStatus': class="hl-string">'New', class=class="hl-string">"hl-comment"># Typically starts as New
                class="hl-string">'AssignedGroup': incident.get(class="hl-string">'Assigned Group'),
                class="hl-string">'Assignee': incident.get(class="hl-string">'Assignee'),
            })

            audit_entries = get_audit_log(session, token, incident_id)

            current_group = None
            last_status = class="hl-string">'New'
            is_reopened = False
            group_assigned = False

            for audit in audit_entries:
                audit_values = audit[class="hl-string">'values']
                event_ts = parse_timestamp(audit_values.get(class="hl-string">'Audit Date'))
                log_key = audit_values.get(class="hl-string">'Log Key')
                fields_changed = audit_values.get(class="hl-string">'Fields Changed', class="hl-string">'')

                class=class="hl-string">"hl-comment"># 2. Group Assigned & 3. Transferred To Another Group
                if class="hl-string">'Assigned Group' in fields_changed:
                    new_group = audit_values.get(class="hl-string">'Log').split(class="hl-string">'\n')[0]
                    if new_group and not group_assigned:
                        activity = class="hl-string">'Group Assigned'
                        group_assigned = True
                    elif new_group and new_group != current_group:
                        activity = class="hl-string">'Transferred To Another Group'
                    else:
                        continue
                    current_group = new_group
                    event_log.append({**base_attributes, class="hl-string">'ActivityName': activity, class="hl-string">'EventTimestamp': event_ts, class="hl-string">'IncidentStatus': last_status, class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})

                class=class="hl-string">"hl-comment"># Status-based activities
                if class="hl-string">'Status' in fields_changed:
                    new_status_str = audit_values.get(class="hl-string">'Log').split(class="hl-string">'\n')[0]
                    new_status_map = {
                        class="hl-string">'In Progress': class="hl-string">'Investigation Initiated',
                        class="hl-string">'Pending': class="hl-string">'Pending Customer Input',
                        class="hl-string">'Resolved': class="hl-string">'Resolution Implemented', class=class="hl-string">"hl-comment"># First event is implementation
                        class="hl-string">'Cancelled': class="hl-string">'Incident Cancelled',
                        class="hl-string">'Closed': class="hl-string">'Incident Closed' class=class="hl-string">"hl-comment"># Final closure
                    }
                    
                    if last_status == class="hl-string">'Resolved' and new_status_str in [class="hl-string">'In Progress', class="hl-string">'Assigned']:
                        is_reopened = True
                        event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Incident Reopened', class="hl-string">'EventTimestamp': event_ts, class="hl-string">'IncidentStatus': new_status_str, class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})

                    if new_status_str in new_status_map:
                        activity = new_status_map[new_status_str]
                        event_log.append({**base_attributes, class="hl-string">'ActivityName': activity, class="hl-string">'EventTimestamp': event_ts, class="hl-string">'IncidentStatus': new_status_str, class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})
                        class=class="hl-string">"hl-comment"># Add specific Resolution/Confirmation events
                        if new_status_str == class="hl-string">'Resolved':
                             event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Incident Resolved', class="hl-string">'EventTimestamp': event_ts, class="hl-string">'IncidentStatus': new_status_str, class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})
                             event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Awaiting User Confirmation', class="hl-string">'EventTimestamp': event_ts, class="hl-string">'IncidentStatus': new_status_str, class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})
                    
                    last_status = new_status_str

            class=class="hl-string">"hl-comment"># 4. Incident Categorized (approximated as last categorization change before resolution)
            cat_events = [a for a in audit_entries if class="hl-string">'Operational Categorization Tier 1' in a[class="hl-string">'values'].get(class="hl-string">'Fields Changed', class="hl-string">'') or class="hl-string">'Product Categorization Tier 1' in a[class="hl-string">'values'].get(class="hl-string">'Fields Changed', class="hl-string">'')]
            if cat_events:
                last_cat_event = cat_events[-1]
                event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Incident Categorized', class="hl-string">'EventTimestamp': parse_timestamp(last_cat_event[class="hl-string">'values'][class="hl-string">'Audit Date']), class="hl-string">'IncidentStatus': last_status, class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})

            class=class="hl-string">"hl-comment"># Add placeholder for User Confirmation Received - often not explicit
            if incident.get(class="hl-string">'Status') == class="hl-string">'Closed' and incident.get(class="hl-string">'Last Resolved Date'):
                class=class="hl-string">"hl-comment"># This is an approximation, real confirmation might be earlier
                event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'User Confirmation Received', class="hl-string">'EventTimestamp': parse_timestamp(incident.get(class="hl-string">'Closed Date')), class="hl-string">'IncidentStatus': class="hl-string">'Closed', class="hl-string">'AssignedGroup': current_group, class="hl-string">'Assignee': None})

            class=class="hl-string">"hl-comment"># 5. SLA Breach (calculated)
            is_sla_breached = False
            target_date_str = incident.get(class="hl-string">'Target Date')
            resolved_date_str = incident.get(class="hl-string">'Last Resolved Date')
            if target_date_str and resolved_date_str:
                target_dt = datetime.strptime(target_date_str, class="hl-string">'%Y-%m-%dT%H:%M:%S%z')
                resolved_dt = datetime.strptime(resolved_date_str, class="hl-string">'%Y-%m-%dT%H:%M:%S%z')
                if resolved_dt > target_dt:
                    is_sla_breached = True
                    event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'SLA Breach', class="hl-string">'EventTimestamp': parse_timestamp(target_date_str), class="hl-string">'IncidentStatus': incident.get(class="hl-string">'Status'), class="hl-string">'AssignedGroup': incident.get(class="hl-string">'Assigned Group'), class="hl-string">'Assignee': incident.get(class="hl-string">'Assignee')})

            class=class="hl-string">"hl-comment"># Final attributes update
            resolution_time = None
            if incident.get(class="hl-string">'Submit Date') and incident.get(class="hl-string">'Last Resolved Date'):
                submit_dt = datetime.strptime(incident.get(class="hl-string">'Submit Date'), class="hl-string">'%Y-%m-%dT%H:%M:%S%z')
                resolved_dt = datetime.strptime(incident.get(class="hl-string">'Last Resolved Date'), class="hl-string">'%Y-%m-%dT%H:%M:%S%z')
                resolution_time = (resolved_dt - submit_dt).total_seconds()
            
            for event in event_log:
                if event[class="hl-string">'IncidentId'] == incident_id:
                    event[class="hl-string">'IsSlaBreached'] = is_sla_breached
                    event[class="hl-string">'IsReopened'] = is_reopened
                    event[class="hl-string">'IncidentResolutionTime'] = resolution_time
            
            time.sleep(0.1) class=class="hl-string">"hl-comment"># Small delay to be kind to the API

    class=class="hl-string">"hl-comment"># Write to CSV
    if event_log:
        fieldnames = [class="hl-string">'IncidentId', class="hl-string">'ActivityName', class="hl-string">'EventTimestamp', class="hl-string">'IncidentStatus', class="hl-string">'Priority', class="hl-string">'AssignedGroup', class="hl-string">'Assignee', class="hl-string">'Service', class="hl-string">'IncidentCategory', class="hl-string">'IncidentResolutionTime', class="hl-string">'IsSlaBreached', class="hl-string">'IsReopened']
        with open(OUTPUT_CSV_FILE, class="hl-string">'w', newline=class="hl-string">'', encoding=class="hl-string">'utf-8') as f:
            writer = csv.DictWriter(f, fieldnames=fieldnames, extrasaction=class="hl-string">'ignore')
            writer.writeheader()
            writer.writerows(sorted(event_log, key=lambda x: (x[class="hl-string">'IncidentId'], x[class="hl-string">'EventTimestamp'])))
        print(fclass="hl-string">"Successfully wrote {len(event_log)} events to {OUTPUT_CSV_FILE}")
    else:
        print(class="hl-string">"No events were generated.")

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

准备好开始了吗？

借助此模板启动流程挖掘工作，从事件管理数据中发现有价值的洞察。立即开始优化运营。

返回改进指南下载模板

立即解决BMC Helix ITSM中的重复事件！

将MTTR降低35%，消除SLA违约，提升用户满意度。

开始免费试用

无需信用卡。免费试用14天。

您的事件管理数据模板

您的事件管理数据模板

事件管理属性

事件管理活动

提取指南

BMC Helix Smart Reporting导出为CSV

步骤

配置

a 示例查询 sql

直接查询ITSM数据库事件表

步骤

配置

a 示例查询 sql

通过BMC Helix REST API使用Python脚本提取数据

步骤

配置

a 示例查询 python

准备好开始了吗？

立即解决BMC Helix ITSM中的重复事件！

您的事件管理数据模板

您的事件管理数据模板

事件管理属性

事件管理活动

提取指南

BMC Helix Smart Reporting导出为CSV

步骤

配置

a 示例查询 sql

直接查询ITSM数据库事件表

步骤

配置

a 示例查询 sql

通过BMC Helix REST API使用Python脚本提取数据

步骤

配置

a 示例查询 python

准备好开始了吗？

立即解决BMC Helix ITSM中的重复事件！

我们重视您的隐私