根据CRM视图获取任何实体记录的审核历史记录 [英] Get audit history records of any entity record as per CRM view

问题描述

我想按照MS CRM格式显示所有审核历史记录数据。

I want to display all audit history data as per MS CRM format.

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

I have imported all records from AuditBase table from CRM to another Database server table.

我希望此表中使用Dynamics CRM中的SQL查询进行记录格式（如上图所示）。

I want this table records using SQL query in Dynamics CRM format (as per above image).

到目前为止，我已经完成

I have done so far

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字段进行比较的值。以及如何从该实体获得新价值。

My problem is AttributeMask is a comma separated value that i need to compare with MetadataSchema.Attribute table's columnnumber field. And how to get New value from that entity.

我已经检查了此链接：通过SQL查询获取数据机会实体的审计历史记录中，但它没有给我[新值]。

I have already checked this link : Sql query to get data from audit history for opportunity entity, but its not giving me the [New Value].

注意：我不能使用 RetrieveRecordChangeHistoryResponse，因为我需要在sql表（不是CRM数据库）的外部网页中显示这些数据。

NOTE : I can not use "RetrieveRecordChangeHistoryResponse", because i need to show these data in external webpage from sql table(Not CRM database).

推荐答案

基本上Dynamics CRM不会使用SQL查询来创建此审核视图（即在CRM中的查看方式），因此，如果成功完成此操作，Microsoft可能会向您购买，因为它比当前处理的速度要快得多：

Well, basically Dynamics CRM does not create this Audit View (the way you see it in CRM) using SQL query, so if you succeed in doing it, Microsoft will probably buy it from you as it would be much faster than the way it's currently handled :)

但实际上-当前的工作方式，SQL仅用于获取所有相关的审计视图记录（不包含任何与属性元数据或任何其他属性匹配），然后所有与元数据的解析和匹配都在.NET应用程序中完成。逻辑非常复杂，有许多不同的情况要处理，我相信在SQL中重新创建它不仅需要一些简单的选择查询，而且实际上还需要一些非常复杂的过程（而且可能还不够，因为不是CRM中的所有内容都保存在数据库中，有些东西只是被编译到应用程序库中），一个人要花几周甚至几个月才能完成（当然，这是我的观点，也许某些T-SQL专家会证明我错了）

But really - the way it works currently, SQL is used only for obtaining all relevant Audit view records (without any matching with attributes metadata or whatever) and then, all the parsing and matching with metadata is done in .NET application. The logic is quite complex and there are so many different cases to handle, that I believe that recreating this in SQL would require not just some simple "select" query, but in fact some really complex procedure (and still that might be not enough, because not everything in CRM is kept in database, some things are simply compiled into the libraries of application) and weeks or maybe even months for one person to accomplish (of course that's my opinion, maybe some T-SQL guru will prove me wrong).

所以，我会做不同的事情-使用 RetrieveRecordChangeHistoryRequest （在某些答案中已经提到）来获取使用某种.NET应用程序（可能已定期运行，或者可能由CRM中的插件触发），将所有审核详细信息（已解析并可以使用），并以用户友好的格式将其放入某些数据库中。然后，您可以使用所需的任何外部应用程序使用此数据库。

So, I would do it differently - use RetrieveRecordChangeHistoryRequest (which was already mentioned in some answers) to get all the Audit Details (already parsed and ready to use) using some kind of .NET application (probably running periodically, or maybe triggered by a plugin in CRM etc.) and put them in some Database in user-friendly format. You can then consume this database with whatever external application you want.

我也不理解您的评论：

我不能使用 RetrieveRecordChangeHistoryResponse，因为我需要
在sql表（不是CRM数据库）的外部网页中显示这些数据

I can not use "RetrieveRecordChangeHistoryResponse", because i need to show these data in external webpage from sql table(Not CRM database)

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

What kind of application cannot call external service (you can create a custom service, don't have to use CRM service) to get some data, but can access external database? You should not read from the db directly, better approach would be to prepare a web service returning the audit you want (using CRM SDK under the hood) and calling this service by external application. Unless of course your external app is only capable of reading databases, not running any custom web services...

这篇关于根据CRM视图获取任何实体记录的审核历史记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！