Map Audit Object ID to RECORD NAME

All replies

• 

  

  0

  Sign in to vote

  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

  
  

  Hope this helps,
  

  ---

  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
• 

  

  0

  Sign in to vote

  This query does not get the Record NAME. I need the record name associated with the object ID

  Wednesday, February 17, 2016 9:06 AM
• 

  

  1

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  1

  Sign in to vote

  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 PM

  Moderator
• 

  

  0

  Sign in to vote

  I was looking for a more dynamic approach to handle the table name and the field name

  Wednesday, February 17, 2016 4:05 PM
• 

  

  0

  Sign in to vote

  Plus it is very expensive to do it on all tables 

  Wednesday, February 17, 2016 4:41 PM
• 

  

  0

  Sign in to vote

  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 AM

  Moderator
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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 PM

  Moderator