按照CRM视图获取任何实体记录的审计历史记录

Question

我希望按照微软客户关系管理的格式显示所有审计历史数据。

我已将所有来自CRM的AuditBase表中的记录导入到另一个数据库服务器表中。

我想使用SQL查询以Dynamics CRM格式检索此表中的记录（如上图所示）。

目前为止，我已经完成了以下工作

select 
AB.CreatedOn as [Created On],SUB.FullName [Changed By],
Value as Event,ab.AttributeMask [Changed Field],
AB.changeData [Old Value],'' [New Value] from Auditbase AB

inner join StringMap SM on SM.AttributeValue=AB.Action and SM.AttributeName='action'
inner join SystemUserBase SUB on SUB.SystemUserId=AB.UserId

--inner join MetadataSchema.Attribute ar on ab.AttributeMask = ar.ColumnNumber
--INNER JOIN MetadataSchema.Entity en ON ar.EntityId = en.EntityId and en.ObjectTypeCode=AB.ObjectTypeCode

--inner join Contact C on C.ContactId=AB.ObjectId
where objectid='00000000-0000-0000-000-000000000000' 
Order by AB.CreatedOn desc

我的问题是AttributeMask是一个逗号分隔的值，我需要将其与MetadataSchema.Attribute表的columnnumber字段进行比较。如何从该实体中获取新值。

我已经查看了此链接：Sql query to get data from audit history for opportunity entity，但它没有给我[New Value]。

注意：我不能使用"RetrieveRecordChangeHistoryResponse"，因为我需要将这些数据显示在外部网页中，从SQL表中获取（而不是CRM数据库）。

- Chirag

---

你可能想要发布一下你到目前为止尝试过的SQL代码... - Aron

审计表将包含旧值和新值，但不包含当前值。当下一次更新发生时，当前值将被推送为新值。 - Arun Vinoth-Precog Tech - MVP

为了帮助您，以下是有关编程的内容：https://marcuscrast.wordpress.com/2012/01/14/dynamics-crm-2011-audit-report-in-ssrs/ - Arun Vinoth-Precog Tech - MVP

正如Arun Vinoth所解释的那样，新的值可以在后续的审计条目中找到，或者 - 如果不存在 - 作为记录字段的当前值。更详细的说明可在https://dev59.com/BI7da4cB1Zd3GeqP9zKh上找到。 - Henk van Boeijen

Answer 1

基本上，Dynamics CRM不能使用SQL查询创建审核视图（就像您在CRM中看到的那样），因此如果您成功执行此操作，则Microsoft可能会从您购买它，因为这种方式比当前处理方式快得多：）

但是实际上-目前的工作方式仅使用SQL来获取所有相关的审核视图记录（不匹配任何属性元数据或其他内容），然后，所有解析和与元数据匹配的操作都在.NET应用程序中完成。逻辑相当复杂，有许多不同的情况需要处理，我认为在SQL中重新创建这个过程不仅需要一些简单的“select”查询，而且需要一些真正复杂的过程（甚至还可能不够，因为CRM中的不是所有东西都保存在数据库中，某些东西只是编译到应用程序库中），需要一个人几周甚至几个月才能完成（当然这是我的意见，也许某个T-SQL大师会证明我错了）。

因此，我会采取不同的方法-使用RetrieveRecordChangeHistoryRequest（已在某些答案中提到）使用某种.NET应用程序（可能定期运行，或者可能由CRM插件触发等）获取所有审核详细信息（已解析并准备好使用），并将它们放入用户友好格式的某个数据库中。然后，您可以使用任何外部应用程序消耗此数据库。

此外，我不明白您的评论：

“我无法使用”RetrieveRecordChangeHistoryResponse“，因为我需要从sql表格（而不是CRM数据库）中显示这些数据”

什么样的应用程序无法调用外部服务（您可以创建自定义服务，不必使用CRM服务）来获取一些数据，但可以访问外部数据库？最好的方法不是直接从数据库中读取，而是准备一个返回所需审核的Web服务（在幕后使用CRM SDK），并由外部应用程序调用此服务。除非当然您的外部应用程序只能读取数据库，而不能运行任何自定义Web服务...

- Pawel Gradecki

---

我的CRM 2013实例已经崩溃了，我该如何使用CRM服务及其记录？ - Chirag

Answer 2

仅从AuditBase表中无法重建完整的审计历史。要获得当前值，您仍需要被审计的表。

构造所需的查询非常复杂，如果RetrieveRecordChangeHistoryRequest也是合适的选项，则可以避免编写这些查询。（请参阅SO上的如何使用FetchXML获取审核记录详细信息）。

注意

此答案是在原始问题被扩展之前提交的，指出RetrieveRecordChangeHistoryRequest无法使用。

- Henk van Boeijen

---

我同意Henk的观点，你最好使用RetrieveRecordChangeRequest来去规范化AuditBase信息，然后将结果推送到SQL（如果需要）。这个项目的最终目标是什么？无论如何，如果你正在考虑使用.NET方法，你可能会发现这个MSDN示例有帮助：https://msdn.microsoft.com/en-us/library/gg309735(v=crm.8).aspx - Aron

Answer 3

如我在评论中所说，审计表将有旧值和新值，但没有当前值。下一次更新时，当前值将作为新值推送。

在您的 OP 查询中，ab.AttributeMask 将返回逗号 "," 分隔的值，而 AB.changeData 将返回波浪线 "~" 分隔的值。阅读更多

我假设您对以 "~" 分隔的值作为 旧值 列感到满意，并希望在 新值 列中显示字段的当前值。当启用了多个字段进行审计时，这种方法将无法工作。您必须使用 ColumnNumber 从 AttributeView 中的 Attribute mask 字段值中拆分 CRM 字段并获取所需结果。

我建议您从以下参考博客开始，一旦获得预期结果，就可以使用额外的查询（在 SQL 中或使用前端的 C#）来提取当前字段值。但您应该再次使用 "~" 连接值以保持格式。

https://marcuscrast.wordpress.com/2012/01/14/dynamics-crm-2011-audit-report-in-ssrs/

更新：
从上面的博客中，您可以使用自己的字段调整SP查询，然后将最后的选择语句转换为“select into”以创建新表进行存储。

修改存储过程以根据上次运行获取增量。配置SQL作业和计划每天或其他时间运行，以填充表格。

然后按您想要的方式选择和显示数据。我在3天内在PowerBI中完成了相同的操作。

优缺点：显然，此要求是为报告目的而设置的。全球报告要求将通过复制或其他手段镜像数据库，并且不会通过注入插件或任何按需Adhoc服务调用来打断Prod用户和Async服务器。此外，您可以访问数据库而不是CRM在线。最好不要重新发明轮子，而是采用现有解决方案。这是我的谦虚意见，基于Microsoft内部项目实施。

- Arun Vinoth-Precog Tech - MVP