Monday, November 07, 2011

CRM 2011 - reports on data auditing

One of forum visitor asked about building reports based on Audited data. I have never worked with it in CRM 2011 so I decided to recheck how does it store information in DB. Confusing is wrong word for the feelings I've felt... Denormalized data which is impossible to analyse with SQL statements. Anyway following query can help you to build your own reports based on this information:

Declare @attributes VarChar(Max), @values VarChar(Max), @ObjectTypeCode int, @LogDateTime DateTime, @RecordId uniqueidentifier, @UserId Uniqueidentifier, @ActionId int

Declare @Result Table(AttributeId int, Value 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)-1))),
@values=RTRIM(LTRIM(SUBSTRING(@values,CHARINDEX('~',@values,0)+1,LEN(@values))))

IF LEN(@CurrentAttribute) > 0
INSERT INTO @Result Values(CAST(@CurrentAttribute as int), @CurrentValue, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId)
END

INSERT INTO @Result Values((Case When IsNull(@attributes, '') = '' Then Null Else CAST(@attributes as int) End), @values, @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