Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
CRM 2011- Auditing Report RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    We need to develop a report which shows Phone Number and Address Changes made in last week to Account records. CRM 2011 has new feature of Auditing. We have enabled auditing, but how can we create an SSRS report for the same? Any idea.

     

    Thanks

    Thursday, September 15, 2011 3:39 AM

Answers

  • Question
    Sign in to vote
    4
    Sign in to vote

    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)

    Hope this helps. Good luck!

    Wim


    Thursday, September 29, 2011 4:42 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    you can make use of FilteredAudit view to write report for audit.

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

     

    Mahain : MS CRM Freelancer

    Thursday, September 15, 2011 5:43 AM
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote

    use the following sql query, you can get the changes about the name field of account entity.


    declare
     @NameFieldColumnNumber nvarchar(10)

    Select
     @NameFieldColumnNumber = ColumnNumber
    From
     Attribute
    Where
      entityid in (select entityid from entity where objecttypecode = 1)
      and name = 'name'

    select
     ChangeData
    from
     AuditBase
    where
     ObjectTypeCode = 1
     AND Operation = 2
     AND CharIndex(@NameFieldColumnNumber, AttributeMask) > 0

    daemon lin
    Thursday, September 15, 2011 6:04 AM
  • Question
    Sign in to vote
    4
    Sign in to vote

    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)

    Hope this helps. Good luck!

    Wim


    Thursday, September 29, 2011 4:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    My crm is Online NOT on Premise, thanks
    Russ
    Monday, November 28, 2011 12:11 PM