Get audit history records of any object record in accordance with the CRM view

Question

I want to display all audit history data in accordance with the MS CRM format.

I imported all the records from the AuditBase table from CRM into another database server table.

I need records of this table using an SQL query in Dynamics CRM format (as shown in the figure above).

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

My problem is that AttributeMask is a comma-separated value that I need to compare with the column field of the MetadataSchema.Attribute table. And how to get a new meaning from this entity.

I already checked this link: Sql request to get data from the audit history for an opportunity object , but it does not give me [New value].

: "RetrieveRecordChangeHistoryResponse", - SQL ( CRM).

Answer 1

, Dynamics CRM ( CRM) SQL-, , , Microsoft, , , , :)

, , SQL ( - ), .NET. , , SQL - "select", - ( , CRM , ), (, , , - T-SQL ).

, -: RetrieveRecordChangeHistoryRequest ( ), ( ) - .NET- (, CRM ..) . , .

:

"RetrieveRecordChangeHistoryResponse", - sql ( CRM)

( , CRM-), , ? , -, ( CRM SDK ) . , , , - -...

Answer 2

AuditBase. .

, , , , RetrieveRecordChangeHistoryRequest .  (. FetchXML SO.)

, , , RetrieveRecordChangeHistoryRequest .

Answer 3

, & , . .

ab.AttributeMask , ",", AB.changeData , "~".

, "~" Old Value, New Value. , . Attribute mask CRM AttributeView, ColumnNumber & .

. , , SQL # . "~" .

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

:
SP , "select into", .

, . sql & , .

& , . PowerBI 3 .

/. , . Prod & Adhoc . , & CRM . & . Microsoft.