A client of mine had a requirement to report on the auditing of the Filtered Opportunity table, dbo.FilteredOpportunity. Here is one way to create a report of audit data in Dynamics CRM 2011. Since there is not a filtered view of the Audit table in CRM, one would need to create a query of the audit table and create a custom SSRS report in CRM.
For starters, the client’s report, need’s to look like this:
The custom fields that are required to be audited are (these are setup in CRM):
These are the fields required by the client to be audited. So anytime an update action took place on the dbo.FilteredOpportunity view, there would be an occurence of an audit in the dbo.Audit view.
The audit table is a uniqe format, similar to CDC, the AttributeMask is also in numeric format, it doesn’t just list the column names in a comma separated string, making things even more fun to query. In my case 3 records show up in the audit log for 1/11/2012:
As you can see, it’s not as simple as selecting out the 3 records and “plopping” the results into a table in the RDL layout. There’s much more to it. For starters, the AttributeMask field is comma seperated but has a leading and trailing comma. The ChangeData field is ~ seperated and does not have any trailing delimiters. Further down in the stored procedure you will see the way I’m handling both of these fields in lines 50-65.
Ideally we want to get our data into a format that is easy for an RDL to consume. So I have written the stored procedure below, I am leveraging a standard split funtion (lines 50-65) to create multiple rows out of the contents of the AttributeMask and ChangeData columns in the Audit table:
USE [Prod_MSCRM]
GO
/****** Object: StoredProcedure [dbo].[custom_p_auditreport_filteredopportunity] Script Date: 01/14/2012 03:40:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[custom_p_auditreport_filteredopportunity]--'1/10/2012'
(@pEndDate datetime)
as
BEGIN
declare @table table (id int identity(1,1), username varchar(max), attributemask varchar(max) null, changeddata varchar(max), objectid uniqueidentifier not null, CreatedOn datetime, OpportunityName varchar(max), AccountIDName varchar(max), SolicitationNumber varchar(max))
declare @endresult table (id int identity(1,1), v_num_cursor int, ColumnKey int, ColumnName varchar(max), DataValue varchar(max), objectid uniqueidentifier, username varchar(max), createdon datetime, OpportunityName varchar(max), AccountIDName varchar(max), SolicitationNumber varchar(max))
declare @reporttable table (id int identity(1,1), username varchar(max), v_num_cursor int, OpportunityName varchar(max), AccountIDName varchar(max), ColumnName varchar(max), NewValue varchar(max), OldValue varchar(max), SolicitationNumber varchar(max))
--daily audit records
insert into @table(username, attributemask, changeddata, objectid, CreatedOn, OpportunityName, accountidname, SolicitationNumber)
select a.UserIdName, a.AttributeMask, a.changedata, a.ObjectId, a.CreatedOn, fo.name as OpportunityName, fo.accountidname, fo.new_solicitation
from dbo.Audit a with(nolock)
INNER JOIN dbo.FilteredOpportunity fo
ON a.ObjectId = fo.opportunityid
WHERE 1=1
and cast(a.CreatedOn as DATE) = @pEndDate
and a.AttributeMask <> ''
and fo.new_opptypename = 'Strategic'
order by a.CreatedOn asc
--ids and loop values for table variable style cursor
declare @v_max_cursor int, @v_num_cursor int
select @v_max_cursor = COUNT(*) from @table
select @v_num_cursor = 1
while @v_num_cursor <= @v_max_cursor
BEGIN
--cursor fields
declare @v_attributemask varchar(max), @v_changedata varchar(max), @v_objectid uniqueidentifier, @v_CreatedOn datetime, @v_UserName varchar(max)
declare @v_opportunityName varchar(max), @v_accountidname varchar(max), @v_new_solicitation varchar(max)
select @v_attributemask = a.AttributeMask, @v_changedata = a.changeddata, @v_objectid = a.ObjectId
, @v_CreatedOn = a.CreatedOn, @v_UserName = a.username, @v_opportunityName = a.OpportunityName, @v_accountidname = a.AccountIDName, @v_new_solicitation = a.SolicitationNumber
from @table a
where id = @v_num_cursor
--use split function to create rows out of individual audit rows.
insert into @endresult(v_num_cursor, ColumnKey, ColumnName, DataValue, ObjectID, UserName, CreatedOn, AccountIDName, OpportunityName, SolicitationNumber)
SELECT iq.rowbatch, iq.ColumnKey, av.Name, iq.DataValue, iq.ObjectID, iq.UserName, iq.CreatedOn, iq.Accountidname, iq.OpportunityName, iq.SolicitationNumber
FROM (
SELECT @v_num_cursor as rowbatch, rowid, @v_accountidname as Accountidname, @v_opportunityName as OpportunityName, MAX(ColumnKey) as ColumnKey, MAX(DataValue) as DataValue, @v_objectid as ObjectID, @V_username as UserName, @v_CreatedOn as CreatedOn, @v_new_solicitation as SolicitationNumber
from (
select ID as rowid, DATA as ColumnKey, null as DataValue
from dbo.Split(',',LEFT(RIGHT(@v_attributemask, (LEN(@v_attributemask)-1)), (LEN(RIGHT(@v_attributemask, (LEN(@v_attributemask)-1)))-1)))
union all
select ID as rowid, NULL as ColumnKey, DATA as DataValue
from dbo.Split('~',@v_changedata)
) iq
group by rowid
) iq
inner join dbo.AttributeView av
on iq.ColumnKey = av.ColumnNumber
where av.EntityId = '30B0CD7E-0081-42E1-9A48-688442277FAE'
--custom lookup, translate surrogate keys and GUIDs to string values or human legible values.
update @endresult set DataValue = case when ColumnName = 'new_businessgroup' then [dbo].[fnBusinessGroupLookup_FilteredOpportunity](DataValue)
when ColumnName = 'ownerid' then [dbo].[fnOwnerIDLookup_SystemUserBase](LTRIM(RTRIM(Replace(case when DataValue = '' then NULL else DataValue end, 'systemuser,',''))))
when ColumnName = 'new_2ndownerid' then [dbo].[fnOwnerIDLookup_SystemUserBase](LTRIM(RTRIM(Replace(case when DataValue = '' then NULL else DataValue end, 'systemuser,',''))))
when ColumnName = 'new_3rdownerid' then [dbo].[fnOwnerIDLookup_SystemUserBase](LTRIM(RTRIM(Replace(case when DataValue = '' then NULL else DataValue end, 'systemuser,',''))))
when ColumnName = 'new_4thownerid' then [dbo].[fnOwnerIDLookup_SystemUserBase](LTRIM(RTRIM(Replace(case when DataValue = '' then NULL else DataValue end, 'systemuser,',''))))
when ColumnName = 'new_5thownerid' then [dbo].[fnOwnerIDLookup_SystemUserBase](LTRIM(RTRIM(Replace(case when DataValue = '' then NULL else DataValue end, 'systemuser,',''))))
when ColumnName = 'new_strategicphase' then [dbo].[fnStrategicPhaseNameLookup_FilteredOpportunity](LTRIM(RTRIM(DataValue)))
when ColumnName = 'customerid' then [dbo].[fnCustomerIDLookup_FilteredOpportunity](LTRIM(RTRIM(Replace(case when DataValue = '' then NULL else DataValue end, 'systemuser,',''))))
when ColumnName = 'new_naicsid' then [dbo].[fnNAICSIDLookup_FilteredOpportunity](LTRIM(RTRIM(Replace(case when DataValue = '' then NULL else DataValue end, 'systemuser,',''))))
when ColumnName = 'new_typeofbid' then dbo.fnTypeOfBidNameLookup_FilteredOpportunity(LTRIM(RTRIM(DataValue)))
when ColumnName = 'new_role' then [dbo].[fnRoleNameLookup_FilteredOpportunity](LTRIM(RTRIM(DataValue)))
when ColumnName = 'new_teaming' then [dbo].[fnTeamingNameLookup_FilteredOpportunity](LTRIM(RTRIM(DataValue)))
when ColumnName = 'new_nda' then [dbo].[fnNDALookup_FilteredOpportunity](LTRIM(RTRIM(DataValue)))
else DataValue end ,
ColumnName = case when ColumnName = 'new_businessgroup' then 'new_businessgroupname'
when ColumnName = 'ownerid' then 'owneridname'
when ColumnName = 'new_2ndownerid' then 'new_2ndowneridname'
when ColumnName = 'new_3rdownerid' then 'new_3rdowneridname'
when ColumnName = 'new_4thownerid' then 'new_4thowneridname'
when ColumnName = 'new_5thownerid' then 'new_5thowneridname'
when ColumnName = 'new_strategicphase' then 'new_strategicphasename'
when ColumnName = 'customerid' then 'customeridname'
when ColumnName = 'new_naicsid' then 'new_naicsidname'
when ColumnName = 'new_typeofbid' then 'new_typeofbidname'
when ColumnName = 'new_role' then 'new_rolename'
when ColumnName = 'new_teaming' then 'new_teamingname'
when ColumnName = 'new_nda' then 'new_ndaname'
else ColumnName end
from @endresult
--unpivot FilteredOpportunity data to be in the same format as the audit records after split. Join on audit.objectid = filteredopportunity.opportunityid
insert into @reporttable ( username, OpportunityName, AccountIDName, SolicitationNumber, v_num_cursor, ColumnName, NewValue, OldValue )
select distinct er.username, er.OpportunityName, er.AccountIDName, er.SolicitationNumber, er.v_num_cursor, cast(unpvt.ColumnName as varchar(Max)) as NewColumn, cast(unpvt.DataValue as varchar(max)) as NewValue, er.DataValue as OldValue
from (
select opportunityid, cast(accountidname as varchar(max)) as accountidname, cast(name as varchar(max)) as name, cast(new_strategicphasename as varchar(max)) new_strategicphasename,
cast(new_solicitation as varchar(max)) new_solicitation, cast(customeridname as varchar(max)) customeridname,
cast(owneridname as varchar(max)) owneridname, cast(new_2ndowneridname as varchar(max)) new_2ndowneridname,
cast(new_3rdowneridname as varchar(max)) new_3rdowneridname, cast(new_4thowneridname as varchar(max))new_4thowneridname,
cast(new_5thowneridname as varchar(max)) new_5thowneridname, cast(new_estrfpreleasedate as varchar(max)) new_estrfpreleasedate,
cast(new_rfprelease as varchar(max)) new_rfprelease, cast(new_nextaction as varchar(max)) new_nextaction,
cast(new_naicsidname as varchar(max)) new_naicsidname, cast(new_setaside_s as varchar(max)) new_setaside_s,
cast(new_contracttype_s as varchar(max)) new_contracttype_s, cast(new_awardtype_s as varchar(max)) new_awardtype_s,
cast(new_typeofbidname as varchar(max)) new_typeofbidname, cast(estimatedvalue as varchar(max)) estimatedvalue,
cast(new_share as varchar(max)) new_share, cast(new_estpropduedate as varchar(max)) new_estpropduedate,
cast(new_proposalduedate as varchar(max)) new_proposalduedate, cast(estimatedclosedate as varchar(max)) estimatedclosedate,
cast(new_awarddate as varchar(max)) new_awarddate, cast(new_rolename as varchar(max)) as new_rolename,
cast(new_teamingname as varchar(max)) new_teamingname, cast(new_teamingntext as varchar(max)) new_teamingntext,
cast(new_ndaname as varchar(max)) new_ndaname, cast(new_ndantext as varchar(max)) new_ndantext,
cast(new_bidproposal as varchar(max)) new_bidproposal, cast(new_centurian as varchar(max)) new_centurian,
cast(new_fedbizops as varchar(max)) new_fedbizops, cast(new_businessgroupname as varchar(max)) new_businessgroupname
from dbo.FilteredOpportunity where opportunityid = @v_objectid) p
UNPIVOT
(DataValue for ColumnName in (new_strategicphasename, new_solicitation ,customeridname, owneridname, new_2ndowneridname,
new_3rdowneridname, new_4thowneridname ,new_5thowneridname, new_estrfpreleasedate , new_rfprelease, new_nextaction ,new_naicsidname,
new_setaside_s ,new_contracttype_s, new_awardtype_s,new_typeofbidname, estimatedvalue, new_share, new_estpropduedate, new_proposalduedate,
estimatedclosedate , new_awarddate, new_rolename,new_teamingname, new_teamingntext , new_ndaname, new_ndantext, new_bidproposal,
new_centurian , new_fedbizops, new_businessgroupname)
) As unpvt
inner join @endresult er
on unpvt.ColumnName = er.ColumnName
and unpvt.opportunityid = er.objectid
select @v_num_cursor = 1 + @v_num_cursor
END
--return results for report
select distinct username as Modifiedbyname, OpportunityName as new_solicitation, SolicitationNumber, AccountIDName as Name, [dbo].[fnFriendlyColumnName_AuditReport](ColumnName) as Item, NewValue, OldValue
from @reporttable
order by 2 asc, Item asc
END
This stored procedure will return the data in a tabular format. As I mentioned it uses the standard split table function found on the web, then based on the objectid (one of the fields in the audit view) looks up the FilteredOpportunity record and then unpivots to support a row like view of the data. If you copy this stored procedure make sure to either comment out lines 66-114 as these have additional custom lookup functions that help me return a string value as in many cases, the audit is tracking the surrogate key or the uniqueidentifier and not the string value in the FilteredOpportunity view.
The operator UNPIVOT helps us format the FilteredOpportunity records similarly to how we’ve transformed the Audit records so the UNPIVOT in combination with the audit data split to rows, it’s a simple join from the audit to the current record, here is one of the audit records returned in its final state:
Don’t forget to grant the “NETWORK SERVICE” execute permissions to the stored procedure once you’re done! Also remember that this report only looks at the day’s audit and what’s currently in the FilteredOpportunity view. If you need to do accurate historical reporting there will need to be a few modifications, and the query will become further complex. In my case the ReportServer is caching this report daily so that there is a snapshot of the report for every day.
