locked
CRM 2011 - Reporting on Audits

    Question

  • Hello,

    I've got a custom entity that shouldn't change much, so my client has request an audit report that tracks which records are modified, whats changed, by who and when.

    I used the built it auditing to records this, but I can't see a possibility to run an Advanced Find on these results, and I can't find a view called "FilteredAudit" to build an SRSS report.

    Am I missing the FilteredAudit view  because I see mentions of this on some blogs. Maybe it was just available in Beta? Or Maybe it's renamed and right in front of me?

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

    I guess my other options are to either

    • use the base tables for reporting (but my users can read this)
    • or create the report in Silverlight/Javascript and use the web services? (More effort than I'd hoped)

    Any suggestions?

    Thanks,

    JdZ 

    Friday, May 27, 2011 7:51 AM

Answers

  • Hi Jdz,

    1) There is no 'FilteredAudit' view, there is only a 'Audit' view that has no security trimming and is not accessible by standard users via the ReportingGroup (as the Filtered views are). This means you cannot allow users to query this view directly via a Reporting Services Report without using a custom security principle (using Execute As, or hard coding Report credentials). You need to be careful doing this in case you allow users access to data that they shouldn't be.

    2) The Audit view only provides you easy access to the transaction details (user, record, change type, date etc) - to get the attribute change data, you must 'unpack' the 'ChangeData' field using the 'AttributeMask' field. The ChangeData is a ~ separated list of *previous* values. The Attribute Mask is a comma separated list of attribute numbers (as defined by the ColumnNumber field of the [MetadataSchema].[Attribute] table). The difficult bit is that although you can get the 'previous value' by parsing these fields, to get the 'new value' you must get the next audit entry for the particular record or if there is no next audit entry, you must get the values that are currently stored in the Entity Record. Further more, if it references option set values, you need to lookup the option set value that was valid on that date, and any entity references you will need to lookup the display name since the values only give you the type and id. The SDK Webservices does all this hard work in code rather through SQL and so it will be the easiest to use, however, it isn't optimized for reporting.

    Hope this helps.

    Scott


    www.develop1.net
    • Marked as answer by JdZ Friday, May 27, 2011 8:24 AM
    Friday, May 27, 2011 8:08 AM
    Answerer

All replies

  • Hi Jdz,

    1) There is no 'FilteredAudit' view, there is only a 'Audit' view that has no security trimming and is not accessible by standard users via the ReportingGroup (as the Filtered views are). This means you cannot allow users to query this view directly via a Reporting Services Report without using a custom security principle (using Execute As, or hard coding Report credentials). You need to be careful doing this in case you allow users access to data that they shouldn't be.

    2) The Audit view only provides you easy access to the transaction details (user, record, change type, date etc) - to get the attribute change data, you must 'unpack' the 'ChangeData' field using the 'AttributeMask' field. The ChangeData is a ~ separated list of *previous* values. The Attribute Mask is a comma separated list of attribute numbers (as defined by the ColumnNumber field of the [MetadataSchema].[Attribute] table). The difficult bit is that although you can get the 'previous value' by parsing these fields, to get the 'new value' you must get the next audit entry for the particular record or if there is no next audit entry, you must get the values that are currently stored in the Entity Record. Further more, if it references option set values, you need to lookup the option set value that was valid on that date, and any entity references you will need to lookup the display name since the values only give you the type and id. The SDK Webservices does all this hard work in code rather through SQL and so it will be the easiest to use, however, it isn't optimized for reporting.

    Hope this helps.

    Scott


    www.develop1.net
    • Marked as answer by JdZ Friday, May 27, 2011 8:24 AM
    Friday, May 27, 2011 8:08 AM
    Answerer
  • That helps lots, thanks for the explaination!
    Friday, May 27, 2011 8:25 AM
  • Have you solved your problem  can you please shared solution ..

    Alpa

    Monday, March 10, 2014 2:59 PM