CA Service Management

Hi Xtraction Team

We have added Previous Group field datamodel.bat file for Incident dashboard reporting. But after adding this column in dashboard profile. There is a duplication of tickets. See below. The field is showing previous group But same ticket is showing multiple times. if we remove this field than there is no duplication.

For your information. I have added  previous group field in datamodel as below.

The expression is below:

(
SELECT SUBSTRING(SUBSTRING(act.action_desc,CHARINDEX('Transfer Group from',act.action_desc)+20,
(CHARINDEX('to ',act.action_desc)-CHARINDEX('Transfer Group from',act.action_desc)-20)),2,LEN(SUBSTRING(act.action_desc,CHARINDEX('Transfer Group from',act.action_desc)+20,
(CHARINDEX('to ',act.action_desc)-CHARINDEX('Transfer Group from',act.action_desc)-20)))-2)
as previous_group FROM dbo.act_log act

INNER JOIN
(
Select call_req_id as crid, MAX(last_mod_dt) as dt2 from dbo.act_log WHERE type = 'TR' and action_desc LIKE 'Transfer Group from%' group by call_req_id
) as act2

on act.call_req_id = act2.crid
and act.last_mod_dt = act2.dt2

WHERE act.type = 'TR' and act.action_desc LIKE 'Transfer Group from%' and act.call_req_id = ACTIVITY_LOG.CALL_REQ_ID )

Please Advise how to resolve duplication issue?

Asim.......

What version of Xtraction is being used?

You might need to open a CA Support case so that the Xtraction Support team from Ivanti can be consulted.

I wouldn't bother contacting Ivanti as they won't help with CA SDM SQL statements.

You can try moving this query to the REQUEST object with the following expression (I only modified the last where clause).

(
SELECT SUBSTRING(SUBSTRING(act.action_desc,CHARINDEX('Transfer Group from',act.action_desc)+20,
(CHARINDEX('to ',act.action_desc)-CHARINDEX('Transfer Group from',act.action_desc)-20)),2,LEN(SUBSTRING(act.action_desc,CHARINDEX('Transfer Group from',act.action_desc)+20,
(CHARINDEX('to ',act.action_desc)-CHARINDEX('Transfer Group from',act.action_desc)-20)))-2) 
as previous_group FROM dbo.act_log act

INNER JOIN 
(
Select call_req_id as crid, MAX(last_mod_dt) as dt2 from dbo.act_log WHERE type = 'TR' and action_desc LIKE 'Transfer Group from%' group by call_req_id
) as act2

on act.call_req_id = act2.crid
and act.last_mod_dt = act2.dt2

WHERE act.type = 'TR' and act.action_desc LIKE 'Transfer Group from%' and act.call_req_id = REQUEST.PERSID )

Additional note.. Whenever you add an act_log column to the request list you are joining the outputs for both tables. Which is the reason you see duplicates, you have a row for each activity log for each ticket. To get around this you can use sub-queries, but those can get complicated..

HI Grant

i have updated the expression to this as you said ....act.call_req_id = REQUEST.PERSID but still after reload datamodel. i can see duplication.

Hi,

Please perform the following if you haven't already.

• Remove Previous Group from your report and File Save
• Uncheck the Active flag on the PREVIOUS_GROUP attribute in ACTIVITY_LOG
• Make sure PREVIOUS_GROUP is added to the REQUEST object and marked as active
• Save the data model
• Clear your browser cache
• Reload the data model in Xtraction web UI
• Add Previous Group back to the report

HI Grant

Please Note that  previous group field is is under incident object (as we required for incident only)& not request object.

still  where clause will be this as you modified??? act.call_req_id = REQUEST.PERSID

please confirm

Hi,

My Incident data source has the REQUEST table. If your data model is configured the same then yes, the where clause is correct.

Please look my screenshots above. Im edit in datasoucre--> servicedes--->views-->indents-->tables-->activity_log

expression is correct  then?>

No, you shouldn't place this in activity_log. Instead place it in:

datasoucre--> servicedes--->views-->indents-->tables-->request

THankyou Grant. Issue resolved. One thing also wanted to ask...which i will ask in another thread.