Using Audit History in SSRS Reports

In this post, we will discuss about how AuditHistory data can be used in the reports; AttributeMask, ChangeData columns of AuditHistory table, and how it should be used inside a function and a Sql query.

Recently, we ran into a requirement to access Audit history data for one of the reports.
The Audit History data can't be accessed using filtered views, hence you would need to access the data using the AuditHistory database table itself.

Few things you should note about the Audit History database table, and columns.
https://community.dynamics.com/crm/b/mscrmcustomization/archive/2015/02/09/ms-crm-audit-database-table-details

In this post, we will concentrate on two such columns, AttributeMask and ChangeData
AttributeMask: Attribute field code of the entity. The type is nvarchar, and is separated by commas.
Example: ,10015,7, 21,

ChangeData: The data that has been changed. This is always the old value of the attribute that has been changed. The new data can be accessed from the entity record.
The type of this field is nvarchar, the changed data is seperated by a tilde (~).
Example: systemuser,0000000-0000-0000-0000-000000000000~11~False

Our requirement was to see what data has been changed on the Account entity's RelationshipType attribute. More specifically, If the relationship type (a picklist) attribute value has been changed from a Prospect (11) to a Customer-Active (1).

We had used a function to Split the attribute mask and a another funtion to extract only the old values of the data for the RealtionshipType field (the Attribute mask code of this field is 7).

CREATE FUNCTION
dbo.SplitAttributeValue
(
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
    WHILE @start < LEN(@string) + 1
    BEGIN
        IF @end = 0
            SET @end = LEN(@string) + 1
     
        INSERT INTO @output (splitdata)
        VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
      
    END
    RETURN
END
GO
select * from dbo.SplitAttributeValue('systemuser,0000000-0000-0000-0000-000000000000~11','~')

CREATE FUNCTION
dbo.GetAttributeMaskOldValue
(
    @maskstring NVARCHAR(MAX),   
    @maskdelimiter CHAR(1),
    @valuestring NVARCHAR(MAX),   
    @valuedelimiter CHAR(1)
)
RETURNS @output TABLE(mask NVARCHAR(MAX), oldvalue NVARCHAR(MAX)
)
BEGIN
IF((@maskstring IS NOT NULL) AND (@valuestring IS NOT NULL))
BEGIN
IF @maskstring like (@maskdelimiter + '%')
BEGIN
select @maskstring = SUBSTRING(@maskstring,2, LEN(@maskstring)+1)
END
DECLARE @maskstart INT, @maskend INT
    SELECT @maskstart = 1, @maskend = CHARINDEX(@maskdelimiter, @maskstring)
    DECLARE @valuestart INT, @valueend INT
    SELECT @valuestart = 1, @valueend = CHARINDEX(@valuedelimiter, @valuestring)
    WHILE @maskstart < LEN(@maskstring) + 1
    BEGIN
       IF @maskend = 0
            SET @maskend = LEN(@maskstring) + 1
       IF @valueend = 0
            SET @valueend = LEN(@valuestring) + 1
        INSERT INTO @output (mask, oldvalue)
        VALUES(SUBSTRING(@maskstring, @maskstart, @maskend - @maskstart),
        SUBSTRING(@valuestring, @valuestart, @valueend - @valuestart))
        SET @maskstart = @maskend + 1
        SET @maskend = CHARINDEX(@maskdelimiter, @maskstring, @maskstart)
        SET @valuestart = @valueend + 1
        SET @valueend = CHARINDEX(@valuedelimiter, @valuestring, @valuestart)
    END
    END
   
    RETURN
END
GO
select oldvalue from dbo.GetAttributeMaskOldValue(',10015,7,21,',',','systemuser,0000000-0000-0000-0000-000000000000~11~False','~')
where mask=7

We use these functions in our following query:

Declare @AuditTemp Table
(
    SystemUserId UniqueIdentifier,
    AccountId UniqueIdentifier,
    AttributeMask nvarchar(max),
    ChangeData nvarchar(max)
)
INSERT INTO @AuditTemp
    select a.UserId, a.ObjectId, a.AttributeMask,a.ChangeData
    from dbo.Audit a
        INNER JOIN dbo.SystemUser su on (su.SystemUserId = a.UserId)
        INNER join dbo.Account ac on a.ObjectId = ac.AccountId
        where 7 IN (select * from dbo.SplitAttributeMask(a.AttributeMask,','))
        AND ac.CustomerTypeCode = 1
    Update @AuditTemp set ChangeData = NULL
    from @AuditTemp
    where ChangeData='' OR AttributeMask = ''

    Declare @QualifyingAccountTable Table
    (      
        AccountId UniqueIdentifier      
    )
    INSERT INTO @QualifyingAccountTable
    select distinct at.AccountId
    from @AuditTemp at    
    where (select oldvalue from dbo.GetAttributeMaskOldValue(at.AttributeMask,',',at.ChangeData,'~')
    where mask=7) = 11

Note: The update statement above is to avoid the following error:
Invalid length parameter passed to the LEFT or SUBSTRING function.
You will get this error when, for some AttributeMask columns in the AuditHistory table, there were no values (empty column) in the ChangeData.
The update statement will replace such values with null in the temporary table, and the condition is checked in the function, dbo.GetAttributeMaskOldValue, to filter out NULL values and allow the function to run only for NOT NULL values.