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.
Happy Coding
P. S. Hayer
(ਪ੍ਰੇਮਜੀਤ ਸਿੰਘ ਹੇਰ)
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
DECLAREDataAuditCursor CURSOR FOR
SELECT * FROM @Audit
OPENDataAuditCursor
FETCH NEXT FROMDataAuditCursor
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 FROMDataAuditCursor
INTO@AttributeMask, @ObjectTypeCode, @EntityName,@LogDATETIME
END
CLOSEDataAuditCursor;
DEALLOCATEDataAuditCursor;
-- Select Distinct to get all the Attributes and their entities
SELECT DISTINCT
r.EntityName
,(SELECT TOP 1 a.Name FROMMetadataSchema.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;
P. S. Hayer
(ਪ੍ਰੇਮਜੀਤ ਸਿੰਘ ਹੇਰ)
Please check my other (non-CRM) blog here: Programming Blogs
