CRM 2011 Queue History Report

While working with a recent prospect, there was a very interesting report that they wanted to recreate in Dynamics CRM. The report would give details of the time the object (in this case incident) spent in each queue. For example, if incident "Incident_xyz" was in queue "A" for 5 minutes, and then was moved to queue B 20 minutes ago, then the report should show the time spend in each queue.
If we look at the queue item details, we can get the amount of time spent in the queue by getting the current date time and subtracting that from the time entered into queue value.

Getting the time the record spent in queue(s) earlier is the tricky part. Here is one way to resolve the issue.

Since we need historical data for this report, the first step is to turn on auditing for the Queue Item entity. Remember to turn on the global audit setting, as well as the entity level one. Now every time the queue is updated for the incident, an entry is made to the audit logs which gives us the details of the previous state.

If you take a look at the audit table, the data is in denormalized state, which is not very conductive to the queries I want to run. Before I could get fancy, I was able to find this awesome post by Andrii Butenko, which lays out the details of transforming the data in the audit table. 

Using that as the starting point, here is the modified sql query that I came up with. Notice that I am filtering the query by selecting only the changes to entity Queue item, and also only the changes to the attribute "queueid".

Another change I had to make to the query was to add a filter statement while selecting into the DataAuditCursor cursor. The reason is that if there are no audit entries in place, the query will result in a error as we are using string functions on the ChangeData value.

From Audit a where len(a.ChangeData) >0

Declare @attributes VarChar(Max), @values VarChar(Max), @ObjectTypeCode int, @LogDateTime DateTime, @RecordId uniqueidentifier, @UserId Uniqueidentifier, @ActionId int

Declare @Result Table(AttributeId int, Value VarChar(Max), enteredOn VarChar(Max), ObjectTypeCode int, ExitedOn DateTime, RecordId uniqueidentifier, UserId uniqueidentifier, ActionId int)
Declare @CurrentAttribute VarChar(max), @CurrentValue VarChar(Max), @cData VarChar(max)

DECLARE DataAuditCursor CURSOR FOR 
Select 
    Case When IsNull(a.AttributeMask, '') = '' Then '' Else Substring(a.AttributeMask, 2, Len(a.AttributeMask) - 2) End
    ,a.ChangeData
    ,a.ObjectTypeCode
    ,a.CreatedOn
    ,a.ObjectId
    ,a.UserId
    ,a.[Action]
From Audit a where len(a.ChangeData) >0
OPEN DataAuditCursor

FETCH NEXT FROM DataAuditCursor 
INTO @attributes, @values,@ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId

WHILE @@FETCH_STATUS = 0
BEGIN
    WHILE CHARINDEX(',',@attributes,0) <> 0
    BEGIN
        SELECT
            @CurrentAttribute=RTRIM(LTRIM(SUBSTRING(@attributes,1,CHARINDEX(',',@attributes,0)-1))),
            @attributes=RTRIM(LTRIM(SUBSTRING(@attributes,CHARINDEX(',',@attributes,0)+1,LEN(@attributes)))),
   --@cData = CHARINDEX('~',@values,0),
   --LTRIM(SUBSTRING(@values,0,CHARINDEX('~',@values,0))),--@values,
            @CurrentValue=RTRIM(LTRIM(SUBSTRING(@values,1,CHARINDEX('~',@values,0)-1))),
   
            @values=--RTRIM(LTRIM(SUBSTRING(@values,CHARINDEX('~',@values,0)+1)))
   RTRIM(LTRIM(SUBSTRING(@values,CHARINDEX('~',@values,0)+1,LEN(@values))))
     
        IF LEN(@CurrentAttribute) > 0
            INSERT INTO @Result Values(CAST(@CurrentAttribute as int), @CurrentValue, @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId)
    END

    INSERT INTO @Result Values((Case When IsNull(@attributes, '') = '' Then Null Else CAST(@attributes as int) End), @values, @cData, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId) 

    FETCH NEXT FROM DataAuditCursor 
    INTO @attributes, @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId
END

CLOSE DataAuditCursor;
DEALLOCATE DataAuditCursor;

select 
 r.AttributeId, Right(r.Value, 36) as queueid,r.enteredon, --Left(r.enteredOn,CHARINDEX('~', r.enteredOn, 0)),--19), 
 r.ExitedOn, datediff(n, Left(r.enteredOn, 19), exitedon) as diff,
 cast(datediff(n, Left(r.enteredOn, 19),exitedon)/1440 as varchar(12)) +' days(s) '+CONVERT(char(8),dateadd(minute, datediff(n, Left(r.enteredOn, 19),exitedon) %1440,'00:00'),108) as Time_in_Q,
 r.RecordId from @Result r
where ObjectTypeCode = '2029' and attributeid = '2' 

The result of the query is shown in the screenshot below.

Here is a quick rundown of the row names and what they mean:

Attribute id: Refers to the attribute we are filtering on, which is the queueid

Queue id: The guid of the queue. You can join with the queue table to get the right queue name

EnteredOn: Date time when the incident entered the queue

ExitedOn: Date time when the incident left the queue

Diff: Difference between the time the incident entered the queue and when it left the queue, in minutes

Time_in_Q: Difference between the time the incident entered the queue and when it left the queue, formatted to show days, hours and minutes

RecordId: The guid of the queue item record. You can join with the FilteredQueueItem view to get the objectid (incidentid) and objectidname (incident name)