MS SQL - Query to find ticket Group at Open status and Resolved status.
MS SQL versions will include 2008 R2, 2014, and 2016
The databases support the Broadcom Clarity Service Management (formerly CA Service Desk Manager) application.
Tickets are recorded in the Call_Req table and changes to certain fields are recorded in the Audit_Log table. They are joined from the Audit_Log.audobj_trkid field to Call_Req.ref_num field.
Dates are Epoch seconds. The sample DDL and data are at: SQLFiddle link
When a ticket is first created, the only record in the Audit_Log is the audobj_trkid (ref_num) and the date. Even though the ticket Group and Status are recorded at the time is created in the Call_Req table; they are not in the Audit_Log table until their values changed.
Take a look at the sample data and you will see the Group and Status are changed at different times. We know the initial values only if we find the first time it was changed and look at the attr_before_value. If the value was initially empty, then we have to look at the attr_after_value.
My requirement is to report on each ticket and show the Group value when the ticket was in the Open status and then also when it was in the Resolved status. This will allow management to measure transfers between Level 1 and Level 2/3/External groups and which are associated with resolving the ticket.
I have part of the solution. I am able to find the first time a Group changes. Here is my query:
////////////////////////
SELECT
CR.ref_num "Ticket",
Sorted."Change Date",
Sorted.attr_name,
Sorted.attr_before_val "Before Value",
Sorted.attr_after_val "After Value",
CASE WHEN (Sorted.attr_before_val IS NULL OR Sorted.attr_before_val = '') THEN Sorted.attr_after_val ELSE Sorted.attr_before_val END "FirstGroup"
FROM (
SELECT
audobj_trkid,
ROW_NUMBER()
OVER(PARTITION BY audobj_trkid
ORDER BY change_date) AS First,
DATEADD(ss, change_date - 25200, '1/1/1970') "Change Date",
attr_name,
attr_before_val,
attr_after_val
FROM
audit_log
WHERE
DATEADD(ss, change_date - 25200, '1/1/1970') >= '01/01/2019 00:00:00'
AND (attr_name = 'group')
) AS Sorted
LEFT JOIN call_req CR
ON Sorted.audobj_trkid = CR.ref_num
WHERE
Sorted.First = 1
ORDER BY
Sorted.audobj_trkid,
Sorted."Change Date"
////////////////////////
Results: <insert screenshot>
Now I know I can repeat this query and filter for Status, but there are are two issues.
The first is that there are many more tables and related fields which are captured in the Audit_Log in production and the performance of just this FirstGroup query is very slow to me even when filtering the change date to just a few months.
Second, and the reason for this post is that I am not sure how to best combine queries to get the information I need. The Group and Status can change many times between Open and Resolved, so I have to backtrack the log to find the last group changed before the status changed.
Thanks and let me know if I can provide any further information.
The databases support the Broadcom Clarity Service Management (formerly CA Service Desk Manager) application.
Tickets are recorded in the Call_Req table and changes to certain fields are recorded in the Audit_Log table. They are joined from the Audit_Log.audobj_trkid field to Call_Req.ref_num field.
Dates are Epoch seconds. The sample DDL and data are at: SQLFiddle link
When a ticket is first created, the only record in the Audit_Log is the audobj_trkid (ref_num) and the date. Even though the ticket Group and Status are recorded at the time is created in the Call_Req table; they are not in the Audit_Log table until their values changed.
Take a look at the sample data and you will see the Group and Status are changed at different times. We know the initial values only if we find the first time it was changed and look at the attr_before_value. If the value was initially empty, then we have to look at the attr_after_value.
My requirement is to report on each ticket and show the Group value when the ticket was in the Open status and then also when it was in the Resolved status. This will allow management to measure transfers between Level 1 and Level 2/3/External groups and which are associated with resolving the ticket.
I have part of the solution. I am able to find the first time a Group changes. Here is my query:
////////////////////////
SELECT
CR.ref_num "Ticket",
Sorted."Change Date",
Sorted.attr_name,
Sorted.attr_before_val "Before Value",
Sorted.attr_after_val "After Value",
CASE WHEN (Sorted.attr_before_val IS NULL OR Sorted.attr_before_val = '') THEN Sorted.attr_after_val ELSE Sorted.attr_before_val END "FirstGroup"
FROM (
SELECT
audobj_trkid,
ROW_NUMBER()
OVER(PARTITION BY audobj_trkid
ORDER BY change_date) AS First,
DATEADD(ss, change_date - 25200, '1/1/1970') "Change Date",
attr_name,
attr_before_val,
attr_after_val
FROM
audit_log
WHERE
DATEADD(ss, change_date - 25200, '1/1/1970') >= '01/01/2019 00:00:00'
AND (attr_name = 'group')
) AS Sorted
LEFT JOIN call_req CR
ON Sorted.audobj_trkid = CR.ref_num
WHERE
Sorted.First = 1
ORDER BY
Sorted.audobj_trkid,
Sorted."Change Date"
////////////////////////
Results: <insert screenshot>
Now I know I can repeat this query and filter for Status, but there are are two issues.
The first is that there are many more tables and related fields which are captured in the Audit_Log in production and the performance of just this FirstGroup query is very slow to me even when filtering the change date to just a few months.
Second, and the reason for this post is that I am not sure how to best combine queries to get the information I need. The Group and Status can change many times between Open and Resolved, so I have to backtrack the log to find the last group changed before the status changed.
Thanks and let me know if I can provide any further information.
[1] Performance issue may be due to the function on change date which would eliminate any good indexing you have on that column. You could try calculating what your filter is in seconds first. e.g. DATEDIFF(SS, '1970-01-01', '2019-01-01')+25200 = 1546326000
So you can try:
Or...
See if that improves performance.
[2] You could try multiple ROW_NUMBER() statements but see if this gets close as it may be simpler to maintain if it works.
So you can try:
[WHERE attr_name = 'group'
AND change_date >= DATEDIFF(SS, '1970-01-01', '2019-01-01')+25200Or...
WHERE attr_name = 'group'
AND change_date >= 1546326000See if that improves performance.
[2] You could try multiple ROW_NUMBER() statements but see if this gets close as it may be simpler to maintain if it works.
SELECT a.id, a.attr_name, a.attr_before_val, a.attr_after_val
, status_change_dt = DATEADD(SS, a.change_date - 25200, '1970-01-01')
, b.group_name, b.group_change_dt
FROM audit_log a
CROSS APPLY (
SELECT TOP 1 group_name = CASE WHEN b.id > a.id THEN b.attr_before_val ELSE b.attr_after_val END
, group_change_dt = DATEADD(SS, b.change_date - 25200, '1970-01-01')
FROM audit_log b
WHERE b.audobj_trkid = a.audobj_trkid
AND b.id <> a.id AND b.attr_name = 'group'
ORDER BY ABS(a.change_date - b.change_date)
) b
WHERE (
a.attr_name = 'status'
) AND a.change_date > DATEDIFF(SS, '1970-01-01', '2019-01-01')+25200
;ASKER
Ryan:
Thank you. You are correct; both status and group could change multiple times. The requirement at this time is only to determine the group at the time of the _initial_ Open status and the _first_ Resolved status. I can get the first group, but it appears I need to find the change date of the first status change to Resolved and use that to then do another sorted query against the group, this time looking for the _last_change of group based on that date?
This does need to be in a tabular format with the change dates for both the group and the status for each ticket.
J.W.
Thank you. You are correct; both status and group could change multiple times. The requirement at this time is only to determine the group at the time of the _initial_ Open status and the _first_ Resolved status. I can get the first group, but it appears I need to find the change date of the first status change to Resolved and use that to then do another sorted query against the group, this time looking for the _last_change of group based on that date?
This does need to be in a tabular format with the change dates for both the group and the status for each ticket.
J.W.
Hi,
Is it the 1st open status since the date filter you put in? Or do you need to find the original date? If yes, is the row with the empty values (i.e. row with id 400003) what is written when the ticket is first opened?
Is it the 1st open status since the date filter you put in? Or do you need to find the original date? If yes, is the row with the empty values (i.e. row with id 400003) what is written when the ticket is first opened?
ASKER
Kevin,
Thank you. I am trying your first query suggestions now and the answer to your last comment is I think I can use the date from the row with empty values but I can also use the Open_Date field from the Call_Req table (I did not include that in the DDL for brevity). They should be the same. It is possible the the status on the 'open date' will not be 'Open', so I am 'assuming' that the initial date is what they really want and were just using 'Open' as shorthand.
'Breeze
Thank you. I am trying your first query suggestions now and the answer to your last comment is I think I can use the date from the row with empty values but I can also use the Open_Date field from the Call_Req table (I did not include that in the DDL for brevity). They should be the same. It is possible the the status on the 'open date' will not be 'Open', so I am 'assuming' that the initial date is what they really want and were just using 'Open' as shorthand.
'Breeze
ASKER
All,
So, I did get clarification. I need to be using the the Open_Date and Resolve_Date from the Call_Req table and less concerned about the actual status' label at the time those dates are written to the log. I still need the groups from the Audit_Log as the Call_Req table only stores the most current Group.
I will update the DDR and sample data as soon as I can.
'Breeze
So, I did get clarification. I need to be using the the Open_Date and Resolve_Date from the Call_Req table and less concerned about the actual status' label at the time those dates are written to the log. I still need the groups from the Audit_Log as the Call_Req table only stores the most current Group.
I will update the DDR and sample data as soon as I can.
'Breeze
ASKER
I have updated the DDL and sample data and the initial change_date in Audit_Log is not the same as the open_date in Call_Req. I will have to look at this some more. The change_date for Resolved and resolve_date in Call_Req are the same. I am guessing there is some small lag between the Call_Req and Audit_Log writes on initial.
'Breeze
'Breeze
I looked at SQL fiddle and do not see a change_date in the Call_Req table.
ASKER
Kevin,
Correct. The change_date is in the Audit_Log. The open_date and resolve_date are in the Call_Req table. There is also a last_modified_date which is written to the Audit_Log as the change_date when the transaction is completed. At the time of the initial save, the last_modified_date and open_date in Call_Req should match the initial change_date in Audit_Log.
Correct. The change_date is in the Audit_Log. The open_date and resolve_date are in the Call_Req table. There is also a last_modified_date which is written to the Audit_Log as the change_date when the transaction is completed. At the time of the initial save, the last_modified_date and open_date in Call_Req should match the initial change_date in Audit_Log.
ASKER
I've updated the sample again. I've added a new Call_Req record and its corresponding Audit_Log data.. This represents when a ticket is transferred and then then Closed with no other changes. If the Call_Req resolve_date was not previously set, then it is set to the same time as the close_date.
SQL Fiddle
Kevin, notice that your script will not pick up the group for ticket '58' was 'Customer Service' at the time the status was 'Open'.
SQL Fiddle
Kevin, notice that your script will not pick up the group for ticket '58' was 'Customer Service' at the time the status was 'Open'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Kevin,
This is spot on with the sample data! I can't get to production until tomorrow but this looks great. In the meantime, I need to find some good references and tutorials on changes to T-SQL since 2008.
This is spot on with the sample data! I can't get to production until tomorrow but this looks great. In the meantime, I need to find some good references and tutorials on changes to T-SQL since 2008.
ASKER
Thanks for all the help! I hope to apply this to a lot of similar sets of data!
'Breeze
'Breeze
how you wish to present these changes in a tabular way? could a ticket changed in status sequence like : Open > Resolved > Open (Re-Open) > Resolved ? if this happened, how you wish to present this?