Answered by:
Map Audit Object ID to RECORD NAME

Question
-
Hello,
I am trying to query the Audit table and I can't figure out how to map the object ID to it's equivalent Record Name if the audit operation was anything but delete. I am trying to develop an SSRS Report.
Any help is appreciated.
Tuesday, February 16, 2016 11:59 AM
Answers
-
Your last 2 posts contradict each other. If you want a dynamic approach, it would be possible to build a dynamic SQL string from the metadata, but this would be for all entities, and relatively inefficient. If you're more concerned about performance, and only need certain entities, then there's no point having a fully dynamic approach.
If you want to build the dynamics SQL, you'd need a cursor to query the metadataschema.entity table (or one of the views on it), to get the logicalname, primaryidattribute and primaryname, and iterate through the records to build something like the following:
fetch next from cur into @entityName, @IdAttribute, @NameAttribute set @sql = @sql + ' union select ' + @NameAttribute + ' as RecordName, ' + @IdAttribute + ' as objectId from ' + @entityName
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
- Marked as answer by Donna EdwardsMVP Thursday, March 3, 2016 11:06 PM
Thursday, February 18, 2016 9:43 AMModerator
All replies
-
Hi,
Refer below blog for the audit table details.
http://mahadeomatre.blogspot.in/2015/02/ms-crm-audit-database-table-details.html
Below is the sample SQL query to get the Audit details.
Note : Replace "ObjectId" with your record ID.(a.ObjectId = '65B9FF38-AC96-E211-A743-0050569F538A')
Below query is not considering "Delete" operation (a.[Action] NOT IN (3,100,101))
Declare @attributes VarChar(Max), @values VarChar(Max), @ObjectTypeCode int, @LogDateTime DateTime, @RecordId uniqueidentifier, @UserId Uniqueidentifier, @ActionId int Declare @Result Table(AttributeId VarChar(Max), Value VarChar(Max),CurrentValue VarChar(Max), ObjectTypeCode int, LogDateTime DateTime, RecordId uniqueidentifier, UserId uniqueidentifier, ActionId int) Declare @CurrentAttribute VarChar(max), @CurrentValue VarChar(Max) DECLARE DataAuditCursor CURSOR FOR Select Case When IsNull(a.AttributeMask, '') = '' Then '' Else Substring(a.AttributeMask, 2, Len(a.AttributeMask) - 2) End ,a.ChangeData ,a.ObjectTypeCode ,a.CreatedOn ,a.ObjectId ,a.UserId ,a.[Action] From Audit a where a.ObjectId = '65B9FF38-AC96-E211-A743-0050569F538A' and a.[Action] NOT IN (3,100,101) OPEN DataAuditCursor FETCH NEXT FROM DataAuditCursor INTO @attributes, @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId WHILE @@FETCH_STATUS = 0 BEGIN WHILE CHARINDEX(',',@attributes,0) <> 0 BEGIN SELECT @CurrentAttribute=RTRIM(LTRIM(SUBSTRING(@attributes,1,CHARINDEX(',',@attributes,0)-1))), @attributes=RTRIM(LTRIM(SUBSTRING(@attributes,CHARINDEX(',',@attributes,0)+1,LEN(@attributes)))), @CurrentValue=RTRIM(LTRIM(SUBSTRING(@values,1,CHARINDEX('~',@values,0)-(case when CHARINDEX('~',@values,0)<=0 then 0 else 1 End)))), @values=RTRIM(LTRIM(SUBSTRING(@values,CHARINDEX('~',@values,0)+1,LEN(IsNull(@values,0))))) IF LEN(@CurrentAttribute) > 0 INSERT INTO @Result Values(CAST(@CurrentAttribute as nvarchar), @CurrentValue,@CurrentValue, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId) END INSERT INTO @Result Values((Case When IsNull(@attributes, '') = '' Then Null Else CAST(@attributes as nvarchar) End), @values,@CurrentValue, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId) FETCH NEXT FROM DataAuditCursor INTO @attributes, @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId END CLOSE DataAuditCursor; DEALLOCATE DataAuditCursor; Select (Select Top 1 Name From MetadataSchema.Entity e Where r.ObjectTypeCode = e.ObjectTypeCode) EntityName ,(Select Top 1 a.Name From MetadataSchema.Attribute a Inner Join MetadataSchema.Entity e On a.EntityId = e.EntityId and a.ColumnNumber = r.AttributeId and e.ObjectTypeCode = r.ObjectTypeCode ) AttributeName ,u.fullname UserName ,r.Value OldFieldValue ,r.RecordId ModifiedRecordId From @Result r Left Join FilteredSystemUser u On r.UserId = u.systemuserid
If you find this post helpful then please "Vote as Helpful" and "Mark As Answer". Thanks and Regards, Mohammad Yusuf Ansari http://microxrm.blogspot.in
Tuesday, February 16, 2016 1:16 PM -
This query does not get the Record NAME. I need the record name associated with the object IDWednesday, February 17, 2016 9:06 AM
-
Hi
I have updated the SQL query to get the record name.update your code accordingly.
Declare @objectname VarChar(Max), @attributes VarChar(Max), @values VarChar(Max), @ObjectTypeCode int, @LogDateTime DateTime, @RecordId uniqueidentifier, @UserId Uniqueidentifier, @ActionId int Declare @Result Table(objectname VarChar(Max),AttributeId VarChar(Max), Value VarChar(Max),CurrentValue VarChar(Max), ObjectTypeCode int, LogDateTime DateTime, RecordId uniqueidentifier, UserId uniqueidentifier, ActionId int) Declare @CurrentAttribute VarChar(max), @CurrentValue VarChar(Max) DECLARE DataAuditCursor CURSOR FOR Select acc.Name ,Case When IsNull(a.AttributeMask, '') = '' Then '' Else Substring(a.AttributeMask, 2, Len(a.AttributeMask) - 2) End ,a.ChangeData ,a.ObjectTypeCode ,a.CreatedOn ,a.ObjectId ,a.UserId ,a.[Action] From Audit a INNER JOIN Account acc ON acc.AccountId = a.ObjectId where a.ObjectId = '65B9FF38-AC96-E211-A743-0050569F538A' and a.[Action] NOT IN (3,100,101) OPEN DataAuditCursor FETCH NEXT FROM DataAuditCursor INTO @objectname, @attributes, @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId WHILE @@FETCH_STATUS = 0 BEGIN WHILE CHARINDEX(',',@attributes,0) <> 0 BEGIN SELECT @CurrentAttribute=RTRIM(LTRIM(SUBSTRING(@attributes,1,CHARINDEX(',',@attributes,0)-1))), @attributes=RTRIM(LTRIM(SUBSTRING(@attributes,CHARINDEX(',',@attributes,0)+1,LEN(@attributes)))), @CurrentValue=RTRIM(LTRIM(SUBSTRING(@values,1,CHARINDEX('~',@values,0)-(case when CHARINDEX('~',@values,0)<=0 then 0 else 1 End)))), @values=RTRIM(LTRIM(SUBSTRING(@values,CHARINDEX('~',@values,0)+1,LEN(IsNull(@values,0))))) IF LEN(@CurrentAttribute) > 0 INSERT INTO @Result Values(@objectname,CAST(@CurrentAttribute as nvarchar), @CurrentValue,@CurrentValue, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId) END INSERT INTO @Result Values(@objectname, (Case When IsNull(@attributes, '') = '' Then Null Else CAST(@attributes as nvarchar) End), @values,@CurrentValue, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId) FETCH NEXT FROM DataAuditCursor INTO @objectname, @attributes, @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId END CLOSE DataAuditCursor; DEALLOCATE DataAuditCursor; Select r.objectname, (Select Top 1 Name From MetadataSchema.Entity e Where r.ObjectTypeCode = e.ObjectTypeCode) EntityName ,(Select Top 1 a.Name From MetadataSchema.Attribute a Inner Join MetadataSchema.Entity e On a.EntityId = e.EntityId and a.ColumnNumber = r.AttributeId and e.ObjectTypeCode = r.ObjectTypeCode ) AttributeName ,u.fullname UserName ,r.Value OldFieldValue ,r.RecordId ModifiedRecordId From @Result r Left Join FilteredSystemUser u On r.UserId = u.systemuserid
If you find this post helpful then please "Vote as Helpful" and "Mark As Answer". Thanks and Regards, Mohammad Yusuf Ansari http://microxrm.blogspot.in
Wednesday, February 17, 2016 12:29 PM -
The Audit Table doesn't necessarily include only the Account table. It could include custom entities and other system entities. This query is only for the Account table.Wednesday, February 17, 2016 1:34 PM
-
If you want other entities, then you could include them via a UNION, but you'd necessarily have to add each entity that you're interested in to the query. One way to do this would be to change the query in the cursor to:
Select e.RecordName ,Case When IsNull(a.AttributeMask, '') = '' Then '' Else Substring(a.AttributeMask, 2, Len(a.AttributeMask) - 2) End ,a.ChangeData ,a.ObjectTypeCode ,a.CreatedOn ,a.ObjectId ,a.UserId ,a.[Action] From Audit a INNER JOIN (select name as RecordName, accountid as objectId from account union select fullname as RecordName, contactid as objectId from contact) as e ON e.objectId = a.ObjectId where a.[Action] NOT IN (3,100,101)
This handles accounts and contacts, and you can add other entities as you wish
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
Wednesday, February 17, 2016 1:57 PMModerator -
I was looking for a more dynamic approach to handle the table name and the field nameWednesday, February 17, 2016 4:05 PM
-
Plus it is very expensive to do it on all tablesWednesday, February 17, 2016 4:41 PM
-
Your last 2 posts contradict each other. If you want a dynamic approach, it would be possible to build a dynamic SQL string from the metadata, but this would be for all entities, and relatively inefficient. If you're more concerned about performance, and only need certain entities, then there's no point having a fully dynamic approach.
If you want to build the dynamics SQL, you'd need a cursor to query the metadataschema.entity table (or one of the views on it), to get the logicalname, primaryidattribute and primaryname, and iterate through the records to build something like the following:
fetch next from cur into @entityName, @IdAttribute, @NameAttribute set @sql = @sql + ' union select ' + @NameAttribute + ' as RecordName, ' + @IdAttribute + ' as objectId from ' + @entityName
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
- Marked as answer by Donna EdwardsMVP Thursday, March 3, 2016 11:06 PM
Thursday, February 18, 2016 9:43 AMModerator -
Hi,
i have the same issue, i think i can do better on the query without doing the cursor and running something like this:
SELECT distinct top 5000 A.[AuditId] ,A.[ObjectId] ,A.[ObjectTypeCode] , E.name ,A.[CreatedOnUTC] ,A.[CreatedOn] ,A.[Action] ,'Action' = SM.Value ,A.[AttributeMask] ,'Field Name' = At.name ,A.[OldValue] ,A.[NewValue] --, 'Select * from ' + E.name + ' where ' +E.name+'id = ''' + Convert(nvarchar(100), A.[ObjectId]) + '''' FROM [FCMT_MSCRM].[dbo].[cs_FilteredAudit] A inner join [MetadataSchema].[Entity] E on A.ObjectTypeCode = E.ObjectTypeCode inner join StringMap SM ON AttributeName='Action' and AttributeValue = A.action INNER JOIN [MetadataSchema].[Attribute] At on At.EntityId=E.EntityId and At.ColumnNumber = A.AttributeMask
as you can see if you uncomment the code line in the code, and you run that query you can have the name of the record of the entity.
Now my problem is how do i know the name of the record, if the record is a Contact, then the name is FullName
if the entity belongs to publisher AAA_ then the Name is AAA_name, and if it is publisher BBB, then the name is BBB_Name. (99.99999% of the times)
so how do i dynamically make that call and put it all together.
I have think about as well to left join all the tables i want to get the name
left join Contact A0 on FA.objectid = A0.Contactid left join SystemUser AA on FA.objectid = AA.SystemUserId left join CustomerAddress AB on FA.objectid = AB.CustomerAddressId left join Task AC on FA.objectid = AC.ActivityId
and then do something like this for the name:
,ISNULL( A0.FullName, ISNULL(AA.FullName ,ISNULL(AB.name, ISNULL(AC.Description, ISNULL(AD.AAA_Name, ISNULL(AE.AAA_name, ISNULL(AF.AAA_name, ISNULL(AG.BBB_name, ISNULL(AH.BBB_name, ''))))))))) as [name]
and it kinds of look fine on the report, but if you have 50 custom entities, you have to do 50 left joins and 50 isnulls. Which is doable, but if you create new entities, you need to modify the report query every time there is a new one.
any given idea of how to improve this?
- Edited by Guillermo Estevez Tuesday, August 29, 2017 2:36 AM forgot to add the last sentence
Tuesday, August 29, 2017 2:35 AM -
I'd still say my most recent post is as good a way as any to make this fully dynamic, as you'd need to dynamically build the SQL to accommodate any new entities. There are other ways (apart from a cursor) to dynamically build the SQL from the metadata, but the performance overhead is not with building the SQL, it's with executing it. There may be a performance difference between using unions or outer joins, but if there is a difference, my money would be on the unions being faster
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
Tuesday, August 29, 2017 1:46 PMModerator