CA Service Management

Good day everyone,

Hope some can enlightenment me up here, Ihave CA SDM at work and have access to the SQL DB, I been tryong for days now to check a query to get what group closed the tickets (my filter will be by dates let´s say report for each month).

What I want to accomplish is to get some kind of resolution rate by solving group, let me be more detail about it, my Service Desk engineers create a ticket and assigned to second level IT, or second level infrastructure or you call it, I want to have a report that states out of 100 tickets, 20 where resolved by second level IT and 80 by infra group, is that possible or i´m lost here?

Hi,

Here's an example query.  Replace <group name> with a group name from your environment.

select
        cr.ref_num                              as "Incident #", 
        dateadd(S,cr.open_date,'1970-01-01')    as "Open Date",
        dateadd(S,cr.close_date,'1970-01-01')   as "Close Date",
        PCAT.sym                                as "Category",
        IMP.sym                                 as "Impact",
        PRI.sym                                 as "Priority",
        URG.sym                                 as "Urgency",
        AFF.resource_name                       as "Affected Service",
        cr.summary                              as "Summary",
        CAST(CR.description AS VARCHAR(5000))   as "Description"
               
from call_req CR

        join ca_contact GRP
        on CR.group_id = GRP.contact_uuid

        full outer join prob_ctg PCAT
        on CR.category = PCAT.persid

        full outer join pri PRI
        on CR.priority = PRI.enum
       
        full outer join urgncy URG
        on CR.urgency = URG.enum
       
        full outer join impact IMP
        on CR.impact = IMP.enum
       
        full outer join ca_owned_resource AFF
        on CR.affected_service = AFF.own_resource_uuid

where GRP.last_name = '<group name>'
and CR.close_date between
          --Unix timestamp for 30 days ago
          ((DATEDIFF(SECOND, DATEADD(SECOND, DATEDIFF(SECOND, GETUTCDATE(), GETDATE()), '1970-01-01'), GETDATE())) - (30 * 24 * 60 * 60))
          and
          --Unix timestamp for now
          (DATEDIFF(SECOND, DATEADD(SECOND, DATEDIFF(SECOND, GETUTCDATE(), GETDATE()), '1970-01-01'), GETDATE()))

Man that was a quick answer, let me give it a try and will let you kow the out come thanos so much!

gbruneau apprecited the query it works, just one question, the query states Close group so I´m wondering if ticket is reslved by certian group and sent back to service desk ot be close would it consider it reso.ved by Service desk?

Thanks in advance 

You're welcome, this query shows you the current group of the ticket. It doesn't capture which group resolved the ticket.  To get that information you may want to look into enabling KPI ticket data (option manager) which can capture this kind of information. Alternatively, you can try modifying the query I shared to include the activity log (act_log ) and filter on type = 'TR'.  You can then add system_description to the select statement, which would display transfer data.

gbruneau

Man thanks os much all worked and I edited my query, I only need to get the analyst name instead of the code and customer nameinstade of code, my query looks like this thanks to you 

select
CR.ref_num,
CR.id,
CR.summary,
CR.description,
CR.status,
LG.type,
dateadd(S,cr.open_date,'1970-01-01') as "Open Date",
dateadd(S,cr.resolve_date,'1970-01-01') as "REsolved Date",
GRP.contact_uuid,
GRP.last_name as Grupo,
LG.analyst,
Cr.zsolucion,
CR.customer
from act_log LG

join call_req CR
on replace(LG.call_req_id, 'cr:', '') = CR.id

join ca_contact GRP
on CR.group_id = GRP.contact_uuid
where LG.type = 'RE'

Looks good, I made some tweaks.  Let me know if that works for you.

select
CR.ref_num,
CR.id,
CR.summary as 'Summary',
CR.description as 'Ticket Description',
LG.action_desc as 'Log Description',
CR.status,
LG.type,
dateadd(S,cr.open_date,'1970-01-01') as "Open Date",
dateadd(S,cr.resolve_date,'1970-01-01') as "Resolved Date",
(CUS.first_name + ' ' + CUS.last_name) as 'Affected End User',
GRP.last_name as 'Group',
(ANALYST.first_name + ' ' + ANALYST.last_name) as analyst,
CR.zsolucion,
CR.customer
from act_log LG

join call_req CR
on LG.call_req_id = CR.persid

join ca_contact GRP
on CR.group_id = GRP.contact_uuid

inner join ca_contact ANALYST
on LG.analyst = ANALYST.contact_uuid

inner join ca_contact CUS
on cr.customer = CUS.contact_uuid

where LG.type = 'RE'

Works like a charm, let me share the last code I end up with, and thanks to your input my reports will be easier to run, below the code in case it helps someone else.

I've added MTTR Long for reports, and MTTR minutes in case it needs to be used for calculation, also added the customers company name.

much appreciated gbruneau

select

CR.id,
CR.ref_num as 'Ticket',
CR.summary as 'Summary',
CR.description as 'Ticket Description',
LG.action_desc as 'Log Description',
CR.status as 'Ticket Status',
LG.type as 'Log Filter',
dateadd(S,cr.open_date,'19700101') as "Open_Date",
dateadd(S,cr.resolve_date,'19700101') as "Resolved_Date",
CONCAT (' Días: ', datediff(DAY,dateadd(S,cr.open_date,'19700101'),dateadd(S,cr.resolve_date,'19700101')),
' Hrs: ', datediff(HOUR,dateadd(S,cr.open_date,'19700101'),dateadd(S,cr.resolve_date,'19700101')),
' Mins: ', datediff(MINUTE,dateadd(S,cr.open_date,'19700101'),dateadd(S,cr.resolve_date,'19700101')),
' Sec: ', datediff(SECOND,dateadd(S,cr.open_date,'19700101'),dateadd(S,cr.resolve_date,'19700101'))) as 'MTTR Long',
datediff(MINUTE,dateadd(S,cr.open_date,'19700101'),dateadd(S,cr.resolve_date,'19700101')) as 'MTTR Mins',
(CUS.first_name + ' ' + CUS.last_name) as 'Affected End User',
Vr.organization as 'Organizacion',
(ANALYST.first_name + ' ' + ANALYST.last_name) as analyst,
GRP.last_name as 'Group',
CR.zsolucion
--CR.customer
from act_log LG

join call_req CR
on LG.call_req_id = CR.persid

join ca_contact GRP
on CR.group_id = GRP.contact_uuid

inner join ca_contact ANALYST
on LG.analyst = ANALYST.contact_uuid

inner join ca_contact CUS
on cr.customer = CUS.contact_uuid

inner join View_Contact_Full Vr
on Cr.customer = Vr.contact_uuid

where LG.type = 'RE'

If you ever install the KPI option Grant was talking about, take a look at this one.

It gets all the "group" or "assignee" transfer that were made when the ticket group was "Escalade CSI" (you may need to change this  ) and get the time between each activities.

So basically i wrote this one to pinpoint exactly what was going on in a group where everything was taking forever.

WITH rows AS(
SELECT kpi.[id]
      ,dateadd(ss,end_time,'1970-01-01') as end_time
      ,kpi.[obj_id]
      ,[field_name]
      ,[field_value]
      ,[next_value]
      ,[user_context]
      ,CR.ref_num
       ,ROW_NUMBER() OVER (ORDER BY kpi.obj_id,kpi.id) as row_num
  FROM [dbo].[usp_kpi_ticket_data] KPI
  inner join (select distinct obj_id from usp_kpi_ticket_data where field_value='Escalade CSI') sKPI --Change this condition based on your needs
     on KPI.obj_id=sKPI.obj_id
  inner join call_req CR
     on cr.id=kpi.obj_id
  where (field_name = 'group' or field_name = 'assignee') and cr.type='I'  and dateadd(ss,end_time,'1970-01-01')>'2017-01-01'
--Uncomment next line to get data for a particular ticket
--and cr.ref_num='I166803'
  )

  select
  mrows.id,
  mrows.ref_num as [Ticket],
  mrows.field_name [Attribute],
  mrows.field_value as [Value before],
  mrows.next_value as [Value after],
  cnt.last_name + ', ' + cnt.first_name as [Activity by],
  case when mrows.obj_id = crows.obj_id
  then
   convert(nvarchar(5),datediff(ss,crows.end_time,mrows.end_time)  / 3600)
  + ':' +
  case when convert(nvarchar(5),  (datediff(ss,crows.end_time,mrows.end_time) % 3600) / 60) <10
  then '0' + convert(nvarchar(5),  (datediff(ss,crows.end_time,mrows.end_time)  % 3600) / 60)
  else convert(nvarchar(5),  (datediff(ss,crows.end_time,mrows.end_time) % 3600) / 60)
  end
  +':'+
  case when convert(nvarchar(5),datediff(ss,crows.end_time,mrows.end_time) % 60) <10
  then '0' + convert(nvarchar(5),datediff(ss,crows.end_time,mrows.end_time) % 60)
  else convert(nvarchar(5),datediff(ss,crows.end_time,mrows.end_time) % 60)
  end
  else ''
  end as [Elapsed time since last activity],
  datediff(ss,crows.end_time,mrows.end_time) as [Elapsed time since last activity - seconds]
  from rows mrows
  inner join rows crows
     on crows.row_num=mrows.row_num-1 and crows.obj_id =mrows.obj_id
  left join ca_contact cnt
     on cnt.contact_uuid=mrows.user_context
order by mrows.obj_id,mrows.id