Sunday 26 February 2012

Crm 2011 Auditing Report

Unfortunately the Audit entity does not not have a Filtered view in the database. Also, there is no support for advanced find and the like.
Therefore there is no supported way to create a report that runs on the audit entity.

If you are working with Dynamics CRM 2011 On Premise you could go for the unsupported solution provided by daemon lin in this thread:
Query the table AuditBase (or the Audit view) in combination with the [MetadataSchema].[Attribute] table and the [MetadataSchema].[Entity] table to translate the AttributeMask (separated with ',') in AuditBase using Attribute.ColumnNumber = (the number in AuditBase.ChangeData), Attribute.EntityId = Entity.EntityId and Entity.ObjectTypeCode = AuditBase.ObjectTypeCode into the attribute names (You can get the display name using [MetadataSchema].[LocalizedLabel]). The corresponding changed values are in AuditBase.ChangeData seperated by ~. Operation is an optionset (1=Create, 2=Update, 3=Delete)

Note that security will NOT be applied on the data. Security Roles are not taken into account in this query. (In contrast, Filtered views always apply security for the current user)

Remember that in this case you are working in an unsupported mode and that a CRM update may break your report. Make sure to document that you did this.

If you are working with CRM 2011 Online: there is no way to create a complete report using SSRS. SSRS in CRM Online cannot directly access the database and the Audit Entity is not entirely exposed through web services which is required if you want to create a FetchXml based Report. (e.g. the ChangeData column that contains the changed data is not valid for read; you would have no easy option to translate the attributemask into corresponding field names.)

In All Cases: you can always work with filtering on the Audit Summary View (Settings > Auditing > Audit Summary View)

http://social.microsoft.com/Forums/en/crmdevelopment/thread/b3bcc68b-88e4-4c1e-af96-dc645b34bbe7

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

http://www.avanadeblog.com/xrm/2010/09/crm-2011-feature-of-the-week-9132010-auditing.html

http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/a11cf7b2-3f5a-4f2b-b97d-9ca56a0c5aab

9 comments:

  1. Try
    ---------------------------
    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
    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

    ReplyDelete
  2. There are 2 ways to evaluate how many users logged in to CRM:
    ■Analyse IIS Logs
    ■Audit User Access (Available since CRM 2011 RU5)

    Read this post : http://dynamics.co.il/auditing-users-access-crm-2011/

    ReplyDelete
  3. Thank you for the post. Our organization is running CRM 2011 on-premise and I am trying to see if 2 custom date attributes change within when we renew their contracts.

    ReplyDelete
  4. Hi,

    How can i see old value ,new value and cahngedate together?

    I want to see the same audit history view of CRM with using query.

    Please help!

    Thanks

    ReplyDelete
  5. Hi,
    When I query the Audit table it gives me following error:
    The SELECT permission was denied on the object 'Audit'

    What would be causing this? My user have system admin role in CRM. We are using CRM2011 On-Premise UR16.

    Thanks.

    ReplyDelete
  6. Thanks for sharing your info. I really appreciate your efforts and I will be waiting for your further write ups thanks once again.
    Audit Reporting

    ReplyDelete
  7. Prudent Chartered Accountants is a leading provider of accounting, auditing and management consultancy in the Dubai, UAE. We provide accountancy and auditing services through Middle East. Our professional and qualified team has a wealth of knowledge and experience in areas such as taxation, corporate finance, litigation support, audit assurance and accounting.

    ReplyDelete
  8. Intelepos offering business solution.
    EPOS system for your shop
    EPOS system for your Restaurants
    EPOS system for your Bakery
    EPOS system for your Takeaway
    EPOS system for your retail store
    intelepos.com
    Website: https://intelepos.com/takeaways
    Email: info@intelepos.com
    Contact No: 0330 1134 157

    ReplyDelete
  9. Your post is extremely helpful and excellent information. pos system dubai I really enjoyed reading this post.

    ReplyDelete