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
Try
ReplyDelete---------------------------
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
There are 2 ways to evaluate how many users logged in to CRM:
ReplyDelete■Analyse IIS Logs
■Audit User Access (Available since CRM 2011 RU5)
Read this post : http://dynamics.co.il/auditing-users-access-crm-2011/
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.
ReplyDeleteHi,
ReplyDeleteHow 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
Hi,
ReplyDeleteWhen 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.
Thanks for sharing your info. I really appreciate your efforts and I will be waiting for your further write ups thanks once again.
ReplyDeleteAudit Reporting
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.
ReplyDeleteIntelepos offering business solution.
ReplyDeleteEPOS 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
Your post is extremely helpful and excellent information. pos system dubai I really enjoyed reading this post.
ReplyDelete