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

11 comments:

  1. Great Post Andriy!
    I tried your SQL and found that both the old and new value for a field are displayed as separate records returned by this query.
    Is there any way I can see them in one record?

    My need is to find out how many opportunity records got their "SalesStageCode" set equal to "Quote Sent" between two given dates.

    Thank you,

    ReplyDelete
  2. Display users that did not access the system in x days

    DECLARE @NoOfDays INT
    DECLARE @Today DATETIME = GETDATE()
    SET @NoOfDays = 10

    SELECT FirsAccess = MIN(dbo.fn_UTCToTzSpecificLocalTime(Audit.CreatedOn, us.TimeZoneBias,us.TimeZoneDaylightBias,us.TimeZoneDaylightYear,us.TimeZoneDaylightMonth,us.TimeZoneDaylightDay,us.TimeZoneDaylightHour,us.TimeZoneDaylightMinute,us.TimeZoneDaylightSecond,0,us.TimeZoneDaylightDayOfWeek,us.TimeZoneStandardBias,us.TimeZoneStandardYear,us.TimeZoneStandardMonth,us.TimeZoneStandardDay,us.TimeZoneStandardHour,us.TimeZoneStandardMinute,us.TimeZoneStandardSecond,0,us.TimeZoneStandardDayOfWeek)),
    LastAccess = MAX(dbo.fn_UTCToTzSpecificLocalTime(Audit.CreatedOn, us.TimeZoneBias,us.TimeZoneDaylightBias,us.TimeZoneDaylightYear,us.TimeZoneDaylightMonth,us.TimeZoneDaylightDay,us.TimeZoneDaylightHour,us.TimeZoneDaylightMinute,us.TimeZoneDaylightSecond,0,us.TimeZoneDaylightDayOfWeek,us.TimeZoneStandardBias,us.TimeZoneStandardYear,us.TimeZoneStandardMonth,us.TimeZoneStandardDay,us.TimeZoneStandardHour,us.TimeZoneStandardMinute,us.TimeZoneStandardSecond,0,us.TimeZoneStandardDayOfWeek)),
    UserFullName = FilteredSystemUser.fullname
    FROM FilteredSystemUser
    LEFT OUTER JOIN Audit ON (FilteredSystemUser.systemuserid = audit.ObjectId)
    LEFT OUTER JOIN SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid() and u.IsDisabled = 0)
    LEFT OUTER JOIN UserSettingsBase us on us.SystemUserId = u.SystemUserId
    WHERE Audit.AuditId IS NULL OR (Audit.ObjectTypeCode = 8 AND
    Audit.userIDName = 'SYSTEM' AND
    Audit.[Action] IN (64, 65))
    GROUP BY FilteredSystemUser.fullname
    HAVING DATEDIFF(d, MAX(dbo.fn_UTCToTzSpecificLocalTime(Audit.CreatedOn, us.TimeZoneBias,us.TimeZoneDaylightBias,us.TimeZoneDaylightYear,us.TimeZoneDaylightMonth,us.TimeZoneDaylightDay,us.TimeZoneDaylightHour,us.TimeZoneDaylightMinute,us.TimeZoneDaylightSecond,0,us.TimeZoneDaylightDayOfWeek,us.TimeZoneStandardBias,us.TimeZoneStandardYear,us.TimeZoneStandardMonth,us.TimeZoneStandardDay,us.TimeZoneStandardHour,us.TimeZoneStandardMinute,us.TimeZoneStandardSecond,0,us.TimeZoneStandardDayOfWeek)), @Today) >= @NoOfDays

    ReplyDelete
  3. Hello,
    Do you expect some reaction from my side?

    ReplyDelete
  4. I got error when executing the query above

    Msg 537, Level 16, State 5, Line 26
    Invalid length parameter passed to the LEFT or SUBSTRING function.

    Would you able to assist me on this?

    ReplyDelete
  5. AttributeMask is the field that is causing this error. Recognised that I did not actually need it. So I decide to remove it. I will post my complete Query when I am done with it.

    ReplyDelete
  6. Good post Andrii,

    I want to know the differnce between "Action" and "Operation" Columns in Audit table.

    Thanks and Ragards,
    Yusuf

    ReplyDelete
  7. Thansk for the post. I'm getting an error -
    Invalid length parameter passed to the LEFT or SUBSTRING function.

    Am I missing anything?

    ReplyDelete
  8. 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
    Replies
    1. How should I react on your comment?

      Delete
    2. Andrii thanks for ur SQl Query, it's perfect, I only fixed a little bit.

      Delete
  9. This comment has been removed by the author.

    ReplyDelete