您的事件管理数据模板

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	事件发生时的当前或历史状态。
说明此属性表示事件所处状态，例如“新建”“处理中”“待处理”“已解决”或“已关闭”，反映事件在生命周期中的进展。分析状态变更是了解事件管理流程的基础。状态数据可用于定义活动、衡量事件处于不同状态的时长（例如处于“待处理”状态的时间），并识别可能停滞或未处理的事件。重要性跟踪状态变更是了解事件进展的关键，也有助于衡量事件处于“待处理”或“处理中”等特定状态的时长。获取途径这是“HPD:Help Desk”表单中的“Status”字段（字段ID：7）。示例新建已分配处理中待处理已解决已关闭
	事件类别 IncidentCategory	事件的分类，通常采用分层结构。
说明事件分类通过结构化方式对事件进行归类，通常采用多级层次结构（例如：一级：硬件，二级：笔记本电脑，三级：电池）。这些数据对于路由、报告和趋势分析至关重要。在流程挖掘中，分类可用于分别分析不同类型的事件。通过比较初始类别和最终类别，它支持“事件分类准确率”仪表板；同时也有助于发现趋势，为“根因趋势”仪表板提供依据。重要性分类有助于准确路由、分析趋势，并比较不同类型事件的处理绩效。获取途径这些字段对应“HPD:Help Desk”表单中的“Operational Categorization Tier 1/2/3”字段。示例硬件 > 笔记本电脑 > 电池软件 > 企业应用 > 登录错误网络 > 连接 > Wi-Fi
	优先级 Priority	分配给事件的优先级，用于确定处理紧急程度。
说明优先级通常根据影响程度和紧急程度确定，并决定事件解决的顺序和速度。常见取值从“严重”到“低”。在流程挖掘中，按优先级分析事件对于绩效分析至关重要。它有助于回答“高优先级事件是否达到SLA目标？”和“低优先级事件是否等待更久？”等问题。按优先级筛选，可聚焦分析对业务影响最大的事件。重要性此属性对于细分分析至关重要，可确保高优先级事件得到更快处理，并达到相应的服务级别目标。获取途径这是“HPD:Help Desk”表单中的“Priority”字段（字段ID：1000000164）。示例严重高中低
	分配组 AssignedGroup	负责处理事件的支持组。
说明此属性用于标识负责处理事件的团队或部门，例如“服务台”、“网络团队”或“数据库管理员”。跟踪分配情况有助于了解团队之间的工作流转。此属性可用于分析团队间交接、识别特定团队造成的瓶颈，并衡量事件重新分配率。它有助于呈现事件在组织内的流转路径，并指出路由效率低下或知识缺口所在。重要性跟踪事件分配给哪个组，有助于分析交接情况、识别反复重新分配的情况，并找出特定团队中的瓶颈。获取途径这是“HPD:Help Desk”表单中的“Assigned Group”字段（字段ID：1000000217）。示例服务台网络运维应用支持二线基础设施服务
	受派人 Assignee	被指派处理事件的个人用户。
说明受派人是指在特定时间负责处理事件的具体支持人员或技术人员。与分配组相比，此属性提供了更细致的信息。按受派人分析绩效，有助于识别表现突出的人员、可能需要额外培训的人员，以及工作量分配不均的情况。它还可用于追踪个人处理复杂事件时采取的具体操作顺序。重要性细致呈现工作量分配和个人绩效，帮助识别表现突出的人员或需要支持的人员。获取途径这是“HPD:Help Desk”表单中的“Assignee”字段（字段ID：1000000218）。示例鲍勃·史密斯艾丽丝·约翰逊查理·布朗
	是否违反SLA IsSlaBreached	用于标记事件是否在SLA目标日期之后才解决。
说明如果事件解决时间超过规定的SLA期限，此布尔属性的值为true。它以明确的二元结果记录每个事件的SLA绩效。此标记对于创建“事件SLA绩效概览”仪表板和计算“事件SLA达标率”KPI至关重要。它支持直接筛选和汇总所有未达到服务级别目标的事件，便于调查超期原因。重要性此标记简化了SLA达标情况分析，可轻松筛选所有超期事件并调查根因。获取途径通过比较“Incident Resolved”事件的时间戳与“SlaTargetDate”计算得出。如果解决时间戳晚于目标日期，此标记的值为true。示例 truefalse
	是否重新打开 IsReopened	用于标记事件状态设为“已解决”后是否重新打开。
说明如果事件已标记为“已解决”，之后状态又变回“处理中”等活跃状态，此布尔属性的值为true。这表明初次修复无效或未完成。此属性可直接衡量返工情况，并用于计算“事件返工率”KPI。分析重新打开的事件，有助于发现修复质量不佳、测试不足或未妥善处理的重复问题，为“返工与升级路径”仪表板提供依据。重要性直接衡量返工情况和解决质量。重新打开率偏高，说明修复措施可能无效，流程也可能存在薄弱环节。获取途径通过分析事件的活动顺序计算得出。如果“Incident Reopened”或类似活动出现在“Resolution Implemented”之后，此标记的值为true。示例 truefalse
	服务 Service	受事件影响的业务或技术服务。
说明此属性将事件关联到配置管理数据库（CMDB）中定义的特定服务，例如“电子邮件服务”、“VPN访问”或“SAP财务系统”。这种关联对于了解事件的业务影响至关重要。按服务分析事件，有助于识别产生大量事件的问题服务，发现与特定技术相关的重复问题，并为问题管理提供趋势分析依据。重要性将事件关联到业务服务，对于分析影响以及识别最容易出现问题的服务至关重要。获取途径这是“HPD:Help Desk”表单中的“ServiceCI”字段，或表示受影响配置项（CI）的类似字段。示例企业电子邮件SAP ERP远程访问VPN人力资源门户
	SLA目标日期 SlaTargetDate	根据SLA，预计解决事件的日期和时间。
说明此属性记录适用服务级别协议（SLA）规定的事件解决期限。期限根据事件优先级和规定的服务时间计算。此时间戳是衡量实际解决时间的基准，对于计算“事件SLA达标率”KPI以及构建展示SLA绩效的仪表板至关重要。它还可用于主动监控即将到期的事件。重要性这是衡量SLA达标情况的基准，可用于判断事件是否按时解决或是否超出约定期限。获取途径此数据通常存储在“SLM:Measurement”表单中，并与事件关联，不属于“HPD:Help Desk”表单的直接字段。示例 2023-10-26T14:00:00Z2023-10-27T09:00:00Z2023-11-01T17:00:00Z
	关闭代码 CloseCode	关闭事件时选择的代码，用于说明解决结果。
说明关闭代码以结构化方式概述事件的解决情况。示例包括“用户已解决”、“未发现故障”、“重复事件”或“已实施永久修复”。此属性有助于分析解决方案的有效性和处理结果。它可以帮助识别未真正修复便已关闭的事件，或发现用户经常自行解决问题的类别。这些情况可能意味着需要完善知识库文章或自助服务工具。重要性以结构化数据记录解决结果，帮助分析修复措施的有效性，并发现事件关闭方式的趋势。获取途径此字段通常属于“HPD:Help Desk”表单中的解决或关闭信息。示例远程解决重复问题用户操作错误无需采取措施
	影响 Impact	衡量事件对业务流程造成的影响。
说明影响用于评估事件对业务造成的不利影响程度，通常按等级划分，例如“广泛/大范围”、“显著/较大”、“中等/有限”或“轻微/局部”。影响与紧急程度共同决定事件优先级。按影响分析有助于了解哪些事件造成的业务中断最大，而不受其技术复杂程度影响。这对于确定流程改进工作的优先级至关重要。重要性帮助量化事件对业务造成的严重程度，这是确定优先级和聚焦高影响问题的重要依据。获取途径这是“HPD:Help Desk”表单中的“Impact”字段（字段ID：1000000163）。示例 1-广泛/大范围2-显著/较大3-中等/有限4-轻微/局部
	提交人 Submitter	最初报告事件的人员。
说明提交人是遇到问题并报告问题的最终用户或客户，与负责处理事件的受派人不同。按提交人或其所属部门分析数据，有助于识别需要更多培训的用户群体，或受特定问题影响的群体。这能从客户视角呈现事件管理流程。重要性标识报告问题的用户，可按用户部门、地点或角色分析，从而发现与特定用户相关的趋势。获取途径此信息记录在“HPD:Help Desk”表单的“Submitter”字段或客户信息相关字段中。示例约翰·多伊简·史密斯彼得·琼斯
	数据最近更新时间 LastDataUpdate	表示此事件数据最近一次从源系统刷新时间的时间戳。
说明此属性记录最近一次提取数据的日期和时间，帮助您了解所分析数据的新旧程度，以及流程洞察反映当前情况的程度。了解最近更新时间对于报告和仪表板至关重要，可让用户掌握数据的时效性，并了解最新事件活动是否已纳入数据。重要性显示数据的时效性，帮助用户了解流程分析及相关洞察反映当前情况的程度。获取途径此值通常在数据提取（ETL）过程中生成并写入数据集。示例 2023-11-01T02:00:00Z2023-11-02T02:00:00Z2023-11-03T02:00:00Z
	根因 RootCause	事件发生的深层原因或最终原因。
说明根因是导致事件发生的根本问题。解决该问题即可防止事件再次发生，通常通过相关问题调查流程来确定。并非所有事件都有记录在案的根因，但分析此属性对于主动开展问题管理至关重要。它支持“根因识别率”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”字段发生变化的次数。次数较多可能表明初始路由不当、首次联系未能解决问题，或问题复杂，需要多个团队参与。此属性直接支持“事件重新分配率”KPI和“事件重新分配周期分析”仪表板。它有助于量化工单在团队间来回转派的情况，这会造成明显延误并降低流程效率。重要性量化低效路由和交接情况，帮助识别在团队间反复转派、迟迟无法推进的事件。获取途径通过统计事件日志中给定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”推断。
重要性此节点表示事件从队列等待转入主动诊断。分析调查开始前的等待时间，有助于发现资源瓶颈，并支持“诊断与调查瓶颈”仪表板。获取途径根据“HPD:Help Desk”表单中的状态变更推断。“Status”字段变为“In Progress”时触发此事件，时间戳取自审计日志。采集根据HPD:HelpDesk_AuditLogSystem中状态变为“In Progress”的记录推断。事件类型 inferred
	SLA违约	这是一个计算得出的事件：当事件解决时间超过规定的服务级别协议目标时发生。通过比较解决时间戳与SLA到期日期得出。
重要性此活动是“SLA绩效概览”仪表板和“事件SLA达成率”KPI的基础，可直接标记未达到服务承诺的事件。获取途径通过比较“HPD:Help Desk”表单中的“Last Resolved Date”和“Target Date”（SLA到期日期）计算得出。若事件尚未解决，则可与当前时间进行比较。采集计算得出的事件：当“Last Resolved Date”晚于“Target Date”时发生。事件类型 calculated
	事件已分类	表示事件已完成运营和产品分类，并已设置优先级。通常根据分类字段的填写或最后修改时间推断此活动。
重要性准确、及时地分类对于高效分派和报告至关重要。分析此活动有助于发现初始分诊中的延误或错误，并支持“事件分类准确性”仪表板。获取途径根据审计日志（“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”等活动状态推断。
重要性此活动可直接衡量返工情况和初次解决方案的有效性。重新打开的事件数量较多，通常表明修复质量不佳，也是“事件返工率”KPI的重要依据。获取途径针对特定事件ID，检测审计日志（“HPD:HelpDesk_AuditLogSystem”）中“Status”从“Resolved”变为“In Progress”或“Assigned”的记录，据此推断。采集根据状态从“Resolved”变为活动状态的记录推断。事件类型 inferred
	已实施解决方案	此活动表示支持团队已应用修复方案或临时变通措施。通常根据事件状态变为“Resolved”推断。
重要性这是表明解决方案已交付的关键节点，也是衡量调查完成后应用修复方案所需时间的重要数据点。获取途径通常根据“HPD:Help Desk”表单中的“Status”变为“Resolved”推断。时间戳记录在“Last Resolved Date”字段和审计日志中。采集取自“Last Resolved Date”时间戳，或状态变为“Resolved”时的时间戳。事件类型 inferred
	已收到用户确认	表示用户主动确认所提供的解决方案已解决问题。此确认可能作为明确事件记录，也可能根据关闭前的备注或相关系统操作推断。
重要性跟踪此活动比单纯等待自动关闭更能准确反映用户验证过程。它有助于衡量“用户平均确认时间”KPI并发现沟通缺口。获取途径此信息可能难以可靠采集。可根据工作日志条目，或事件变为“Closed”前对状态原因的特定更新进行推断。可能需要分析“HPD:WorkLog”表单。采集根据关闭前的特定工作日志条目或状态原因更新推断。事件类型 inferred
	已转至其他支持组	当事件从一个支持组重新分配至另一个支持组时，会发生此活动。根据首次分派后“Assigned Group”字段发生变更推断。
重要性频繁转派可能表明初始分派有误或存在知识缺口。跟踪此活动对于“事件转派周期分析”仪表板和“事件转派率”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”状态表示，此时自动关闭计时开始。
重要性此活动对于“用户确认与验证延迟”仪表板至关重要。它有助于量化提供修复方案到获得用户验证之间的延迟，避免事件生命周期被不必要地延长。获取途径当“HPD:Help Desk”表单中的“Status”变为“Resolved”时，此状态开始。时长从“Last Resolved Date”起算，直到事件变为“Closed”或“Reopened”。采集状态变为“Resolved”时开始，变为“Closed”或“In Progress”时结束。事件类型 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及查询事件管理数据源的权限。
报告类型：需要使用Freehand SQL功能创建高级报告，通过UNION ALL语句构建事件日志结构。
数据源视图：使用标准的事件管理视图。该视图基于HPD:Help Desk表单及其相关联接。
日期范围筛选：设置日期范围对控制性能至关重要。提供的查询包含[START_DATE]和[END_DATE]占位符。建议提取3至6个月的数据，以便开展有效分析，同时避免系统负载过高。
公司筛选：查询包含[YOUR_COMPANY_NAME]占位符，用于将数据限定到特定运营公司。如果您使用多租户环境，此筛选对于确保报告准确性至关重要。
性能注意事项：事件日志查询较为复杂，需要联接多个表。处理大型数据集可能会占用大量系统资源。请尽量在非高峰时段安排大型数据导出。

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

查询示例 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”字段时，会生成“已分配小组”事件；状态从“Resolved”变更为“In Progress”时，会生成“事件重新打开”事件。
处理计算得出的事件：脚本还会计算日志中未直接记录的事件。“事件已报告”根据“Submit Date”生成；“SLA超时”则通过比较“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表单的读取权限。缺少任一表单的访问权限都会导致数据提取不完整。

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

步骤

配置

查询示例 sql

直接查询ITSM数据库中的事件表

步骤

配置

查询示例 sql

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

步骤

配置

查询示例 python

准备好开始了吗？

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