CRM 2011: Get list of audited attributes from AuditBase table

Recently, I was asked to get all the fields and their entities which were audited in a day so that we can decide if we really need to audit all of those fields and disable auditing where its not required.

As we know there is no Filtered view for audit entity and even advance find don't support Audit search, so it is bit difficult to get the list of fields audited. But Kelvin's blog put me on right path to write this query and get desired result.

-- Declaring Variable and Temp Tables

DECLARE @AttributeMask VARCHAR(MAX), @ObjectTypeCode INT, @EntityName VARCHAR(100), @LogDATETIME DATETIME

DECLARE @CurrentAttribute VARCHAR(MAX)

DECLARE @Audit Table(AttributeMask VARCHAR(MAX), ObjectTypeCode INT, EntityName VARCHAR(100), LogDATETIME DATETIME);                               

DECLARE @Result Table (AttributeId INT, ObjectTypeCode INT, EntityName VARCHAR(100), LogDATETIME DATETIME);

DECLARE @todaysdate DATETIME;

-- Set the date to bring all the fields Audited today

SET @todaysdate = CAST(GETDATE() AS DATE);

-- Get all todays records from AuditBase; You can remove where clause to get everything

INSERT INTO @Audit

SELECT a.AttributeMask, a.ObjectTypeCode, e.Name, a.CreatedOn FROM Audit AS a

INNER JOIN MetadataSchema.Entity e on a.ObjectTypeCode = e.ObjectTypeCode

WHERE CAST(a.CreatedOn AS DATE) = @todaysdate;

-- Using Cursor to go through each and every record in @Audit Table

DECLARE DataAuditCursor CURSOR FOR

SELECT * FROM @Audit

OPEN DataAuditCursor

FETCH NEXT FROM DataAuditCursor

INTO @AttributeMask, @ObjectTypeCode, @EntityName, @LogDATETIME

WHILE @@FETCH_STATUS = 0

BEGIN

      -- Run while Attribute mask have comma(s) in it

      WHILE CHARINDEX(',',@AttributeMask,0) <> 0

    BEGIN

            SELECT

            @CurrentAttribute=RTRIM(LTRIM(SUBSTRING(@AttributeMask,1,CHARINDEX(',',@AttributeMask,0)-1))),

            @AttributeMask=RTRIM(LTRIM(SUBSTRING(@AttributeMask,CHARINDEX(',',@AttributeMask,0)+1,LEN(@AttributeMask))))

           

        IF LEN(@CurrentAttribute) > 0

        INSERT INTO @Result Values(@CurrentAttribute, @ObjectTypeCode, @EntityName, @LogDATETIME)

    END

   

    INSERT INTO @Result VALUES((CASE WHEN ISNULL(@AttributeMask, '') = '' THEN NULL ELSE @AttributeMask END),

                                                @ObjectTypeCode, @EntityName, @LogDATETIME)

   

    FETCH NEXT FROM DataAuditCursor

    INTO @AttributeMask, @ObjectTypeCode, @EntityName, @LogDATETIME

END

CLOSE DataAuditCursor;

DEALLOCATE DataAuditCursor;

-- Select Distinct to get all the Attributes and their entities

SELECT DISTINCT

    r.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'

FROM @Result r;

Happy Coding

P. S. Hayer
(ਪ੍ਰੇਮਜੀਤ ਸਿੰਘ ਹੇਰ)

Please check my other (non-CRM) blog here: Programming Blogs