Hi,
most of you have probably already spent a lot of time looking for a cradle to grave information about a call, moving between VQs.
Here is a little script to provide a summary.
It presupposes that you have defined user-data to be collected in ICON, cf xx,yy and zz
/* Time interval definition */
DEFINE start_date = '27-06-2019 12:00:00';
DEFINE end_date = '27-06-2019 12:20:00';
select
IRF_RS.RESOURCE_NAME AS "IRF RESOURCE", MF_RS.RESOURCE_ALIAS AS "MF RESOURCE",
IF_.SOURCE_ADDRESS AS "ANI", IF_.TARGET_ADDRESS AS "DNIS",
IRF_DESC.SERVICE_TYPE, IRF_DESC.SERVICE_SUBTYPE,IRF_DESC.BUSINESS_RESULT,
IRF_DATA1.xx, IRF_DATA1.yy,
UDATA1.xx, UDATA1.yy, UDTA1.zz,
UDATA2.xx, UDATA2.yy, UDATA2.zz,
TO_CHAR(CAST( FROM_TZ(CAST(unixts_to_date(IRF.START_TS) as TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Berlin' as DATE), 'DD-MM-YYYY hh24:mi:ss') AS IRF_START_TIME,
TO_CHAR(CAST( FROM_TZ(CAST(unixts_to_date(IRF.END_TS) as TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Berlin' as DATE), 'DD-MM-YYYY hh24:mi:ss') AS IRF_END_TIME,
IRF.TECHNICAL_DESCRIPTOR_KEY AS "IRF_TECH_DESC_KEY", MF.TECHNICAL_DESCRIPTOR_KEY AS "MF_TECH_DESC_KEY",
IRF.INTERACTION_ID, IRF.RESOURCE_KEY AS "IRF RESOURCE KEY", MF.MEDIATION_SEGMENT_ID, MF.RESOURCE_KEY AS "MF RESOURCE KEY",
IRF_KEY.INTERACTION_RESOURCE_ID, IRF_KEY.INTERACTION_DESCRIPTOR_KEY, IRF_KEY.CUSTOM_KEY_1, IRF_KEY.CUSTOM_KEY_2,
IRF.MEDIATION_DURATION AS "IRF MEDIATION DURATION", IRF.TALK_DURATION, IRF.CONS_INIT_TALK_DURATION, IRF.CONS_RCV_TALK_DURATION, IRF.AFTER_CALL_WORK_DURATION, MF.MEDIATION_DURATION AS "MF MEDIATION DURATION",
MF.START_TS, MF.END_TS, MF.RESOURCE_GROUP_COMBINATION_KEY, MF.START_DATE_TIME_KEY, MF.END_DATE_TIME_KEY,
'&start_date' AS "START_SEARCH", '&end_date' AS "END_SEARCH"
FROM resource_ IRF_RS, INTERACTION_FACT IF_, mediation_segment_fact MF, resource_ MF_RS,
interaction_resource_fact IRF, irf_user_data_keys IRF_KEY, interaction_descriptor IRF_DESC, user_data_cust_dim_2 UDATA2, user_data_cust_dim_1 UDATA1,
IRF_USER_DATA_CUST_1 IRF_DATA1
WHERE
/***** Search possibility 1 Interaction ID from IRF table *****/
--IRF.INTERACTION_ID IN (18137817,18137093) AND
--IRF.INTERACTION_ID=18693475 AND
/***** Search possibility 2 ANI without prefix *****/
--IF_.SOURCE_ADDRESS LIKE '%15751458%' AND
/***** Search possibility 3 DNIS without prefix *****/
--IF_.TARGET_ADDRESS LIKE '%789456123%' AND
/***** Search possibility 4 agent names for direct calls *****/
(
IRF_RS.RESOURCE_NAME LIKE '%username%' OR
MF_RS.RESOURCE_NAME LIKE '%123456%' OR
) AND
/***** Search possibility 5 time interval, see above *****/
IRF.START_TS >= ROUND((CAST(FROM_TZ(CAST(TO_DATE('&start_date','DD-MM-YYYY hh24:mi:ss') as TIMESTAMP), 'Europe/Berlin') AT TIME ZONE 'UTC' as DATE) - TO_DATE('01-01-1970','DD-MM-YYYY')) * 86400) AND
IRF.START_TS <= ROUND((CAST(FROM_TZ(CAST(TO_DATE ('&end_date','DD-MM-YYYY hh24:mi:ss') as TIMESTAMP), 'Europe/Berlin') AT TIME ZONE 'UTC' as DATE) - TO_DATE('01-01-1970','DD-MM-YYYY')) * 86400) AND
IF_.INTERACTION_ID = IRF.INTERACTION_ID
AND
IRF.RESOURCE_KEY = IRF_RS.RESOURCE_KEY -- Resource Key from IRF table, usually an agent
AND
IRF.INTERACTION_ID = MF.INTERACTION_ID(+) -- Mediation Interaction Fact is only filled, when the call transits via VQ => Field can be emplty, therefore LEFT JOIN with IRF
AND
MF.RESOURCE_KEY = MF_RS.RESOURCE_KEY(+) -- Resource Key from the MF table, mostly a VQ => Field can also be empty
AND
IRF.INTERACTION_RESOURCE_ID = IRF_KEY.INTERACTION_RESOURCE_ID
AND
IRF_DATA1.INTERACTION_RESOURCE_ID = IRF_KEY.INTERACTION_RESOURCE_ID
AND
IRF_DESC.INTERACTION_DESCRIPTOR_KEY = IRF_KEY.INTERACTION_DESCRIPTOR_KEY
AND
UDATA2.ID = IRF_KEY.CUSTOM_KEY_2
AND
UDATA1.ID = IRF_KEY.CUSTOM_KEY_1
ORDER BY -- IRF_DATA1.R_TRANSFER,
CASE -- Order by Mediation if possible, else by IRF
WHEN MF.INTERACTION_ID IS NOT NULL THEN MF.START_TS
ELSE IRF.START_TS END,
IRF.START_TS, IRF_KEY.INTERACTION_RESOURCE_ID
#Reporting/Analytics------------------------------
David Gerber
Newcape Consulting e.U.
www.newcape-consulting.com------------------------------