Dynamics CRM Audit Query

Due to a large number of records in our system we find that many times trying to open an audit log from a record results in a sql timeout error in CRM. Below I finally spent some time in coming up with a SQL query to get similar data. Replace the @objectid value with the record that you are attempting to view the audit for and you should get a returned table with the specified data.

NOTE: dates/times in the database are stored UTC, so some additional care should be taken with any dates returned in below query.


--drop table #audit

declare @objectid uniqueidentifier = '{AA2B1B39-02E7-E611-80C5-0050569E2CDA}'
select auditid, useridname, createdon, operation, AttributeMask, '~' + ChangeData + '~' ChangeData, objectid
into #audit
from audit a
where a.objectid = @objectid

declare @objecttypecode int = (select top 1 objecttypecode from audit where objectid = @objectid)

;with #tmp(auditid, useridname, createdon, operation, Attribute, AttributeMask, ChangeDataStr, ChangeData) as (
select auditid, useridname, createdon, operation,
LEFT(AttributeMask, CHARINDEX(',',AttributeMask+',')-1), STUFF(AttributeMask, 1, CHARINDEX(',',AttributeMask+','), ''),
LEFT(ChangeData, CHARINDEX('~',ChangeData+'~')-1), STUFF(ChangeData, 1, CHARINDEX('~',ChangeData+'~'), '')
from #audit
union all
select auditid, useridname, createdon, operation,
LEFT(AttributeMask, CHARINDEX(',',AttributeMask+',')-1), STUFF(AttributeMask, 1, CHARINDEX(',',AttributeMask+','), ''),
LEFT(ChangeData, CHARINDEX('~',ChangeData+'~')-1), STUFF(ChangeData, 1, CHARINDEX('~',ChangeData+'~'), '')
from #tmp
where ChangeData > ''
)


select auditid, useridname, createdon, operation, a.TableColumnName, ChangeDataStr
from #tmp t
join attribute a on t.attribute = a.ColumnNumber
join entity e on a.entityid = e.entityid and e.ObjectTypeCode = @objecttypecode
order by createdon desc
OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option