Thursday 28 August 2014

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 

No comments :

Post a Comment