SQL - Parse act_log transfer activity

[PedroRamalho]

Hi all,

 

I need to create a SQL View that outputs information regarding incident transfers (from X to Y). My problem is that the only available field with that kind of information is text - act_log.description.

 

I will provide some examples and the data that I need to display:

• Transfer Assignee from '' to 'Tech Name'
  • assignee_prev = NULL
  • assignee_dest = 'Tech Name'
  • group_prev = NULL
  • group_dest = NULL
• Transfer Assignee from 'Tech 1' to 'Tech 2'
  • assignee_prev = 'Tech 1'
  • assignee_dest = 'Tech 2'
  • group_prev = NULL
  • group_dest = NULL
• Transfer Group from 'Group 1' to 'Group 2'
  • assignee_prev = NULL
  • assignee_dest = NULL
  • group_prev = 'Group 1'
  • group_dest = 'Group 2'
• Transfer Assignee from 'Tech 1' to 'Tech 2' Transfer Group from 'Group 1' to 'Group 2'
  • assignee_prev = 'Tech 1'
  • assignee_dest = 'Tech 2'
  • group_prev = 'Group 1'
  • group_dest = 'Group 2'

 

Any SQL ninja that can provide some help?

 

Thank you!

[Pier Olivier Tremblay]

FALSE

 

 

This information is also available in the usp_kpi_ticket_data table :

 

select * from usp_kpi_ticket_data
where field_name='assignee' and field_value is null and next_value is not null

select * from usp_kpi_ticket_data KPI
left join ca_contact cntfrom
     on cntfrom.contact_uuid=KPI.attr_from_uuid
left join ca_contact cntto
     on cntto.contact_uuid=KPI.attr_to_uuid
where 
cntfrom.userid='useridFrom' 
and 
cntto.userid='useridTo'

 

 

May contains typo. Written directly on the forum but the general idea is in there.

 

You can figure out the rest

 

If you absolutely want to parse the act_log i can help you but it will be heavy AF.

 

Hope this helps,

 

Pier

[PedroRamalho]

It seems awesome and simple, @POssq. But I don't have any row in usp_kpi_ticket_data.

Do you know which KPI metric should I enable?

[Pier Olivier Tremblay]

Under the options manager, intall the option : kpi_ticket_data_table

[PedroRamalho]

Thank you pier-olivier.tremblay.

 

I will try this in Quality environment first since I believe it might affect SDM performance.

[Pier Olivier Tremblay]

I did not noticed any performance hit in my environnement. You can take a look at this thread for more informations : Reporting on KPI Data

 

But it is always a best practice to test before going to prod