locked
Map Audit Object ID to RECORD NAME RRS feed

  • 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

    Thursday, February 18, 2016 9:43 AM
    Moderator

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

    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
  • 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
  • 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 PM
    Moderator
  • I was looking for a more dynamic approach to handle the table name and the field name
    Wednesday, February 17, 2016 4:05 PM
  • Plus it is very expensive to do it on all tables 
    Wednesday, 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

    Thursday, February 18, 2016 9:43 AM
    Moderator
  • 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 PM
    Moderator