UCCE SQL query to find the Call Types which on not used in ICM script or DN

Vinod Patil · ‎10-13-2017

Hi Experts,

I want to delete the ICM call types which are not used any where, as this task can be done manually by checking through Config manager >> Script reference >> Call Type and also Call Type association (Script editor ) but there are 1000 of CT and it is time consuming so I am looking for some SQL query which can return unused call types in ICM.

Please help me.

Best regards,

Vinod

Slavik Bialik · ‎10-13-2017

WOW, I wouldn't go there. It won't be so simple and very easy to miss references.

For example you can do a SQL query like that:

SELECT ct.[calltypeid], 
       [customerdefinitionid], 
       [enterprisename], 
       ct.[description], 
       [deleted] 
FROM   [scc2_awdb].[dbo].[call_type] ct 
       LEFT JOIN [scc2_awdb].[dbo].[ref_call_type_map] AS RefCTM 
              ON ct.calltypeid = RefCTM.calltypeid 
WHERE  RefCTM.scriptschedule IS NULL

It'll give you all the call types that aren't referenced to any script schedule.

Also:

SELECT DN.[dialednumberid], 
       [customerdefinitionid], 
       [labelid], 
       [enterprisename], 
       [routingclientid], 
       [dialednumberstring], 
       DN.[description], 
       [deleted] 
FROM   [scc2_awdb].[dbo].[dialed_number] DN 
       LEFT JOIN [scc2_awdb].[dbo].[dialed_number_map] DNM 
              ON DN.dialednumberid = DNM.dialednumberid 
WHERE  DNM.calltypeid IS NULL

This will give you all the call types that aren't associated to any Dialed Numbers.

But I bet I'm missing much more things. Like the call type reference in the script editor itself, I think I found where it stored, there's a view named "Script_Cross_Reference", but couldn't get anything logic from that table as there are lots of object referenced there, and by the ucce database scheme book, a call type should be TargetType = 7 (a field in the DB), but in my script and DB couldn't find any references with 7 which doesn't make any sense because I have a few "Set Calltype" nodes.

Vinod Patil · ‎10-14-2017

Thanks Slavik, In my case if I do a join only with Script, Master_Script and Script_Cross_Reference with condition as TargetType = 7, can see the data but if I join this query to Call_Type then no data in TargetType field. I think have to perform manual by going one by one each CT.

Thanks a lot for your help

Slavik Bialik · ‎10-14-2017

I would advise that.
I probably saw nothing because in most cases when I'm using the "Set Call Type" node I use it dynamically, and I'm concatenating a variable the contains part of the call type name, and adding it the relevant suffix. If I had used this node and have chosen a call type from the list I probably could see it in the database while filtering with TargetType = 7.

jimbrandolini · ‎03-27-2018

Hey Guys,

I was looking for a similar report that showed all Dialed Numbers and what script they were pointed too.

There's no easy way to do this in the ICM Admin Tools.

I wrote this SQL Query.... My Joins are very ugly, I know. But it will give you the dialed number and the script name its pointed too.

USE
      [grt_awdb]

SELECT

    a.DialedNumberString as "Dialed Number String",
    a.EnterpriseName as "Dialed Number String Name",
    c.EnterpriseName as "Call Type",
    e.EnterpriseName as "ICM Script"
    
    
FROM

    grt_awdb.dbo.t_Dialed_Number a join grt_awdb.dbo.t_Dialed_Number_Map b on
    (a.DialedNumberID = b.DialedNumberID) join grt_awdb.dbo.t_Call_Type c on
    (b.CallTypeID = c.CallTypeID) join grt_awdb.dbo.t_Call_Type_Map d on
    (c.CallTypeID = d.CallTypeID) join grt_awdb.dbo.t_Master_Script e on
    (d.MasterScriptID = e.MasterScriptID)

geoff · ‎03-27-2018

The previous poster is on track with the most common ICM query of all: the Dialed Number=> Call Type => Scheduled Script map.

I have lost track of the number of times I have run this query, but it must be in the thousands. Run the query, upload into Excel, present to the customer.

Typically we see something like this (using the lazy JOIN syntax)

SELECT DialedNumberString
	,d.Description AS 'DN_Description'
	,c.EnterpriseName AS CallType
	,c.Description AS CT_Description
	,ms.EnterpriseName AS ScriptName
FROM Dialed_Number_Map AS m
	,Call_Type AS c
	,Dialed_Number AS d
	,Call_Type_Map AS ctm
	,Master_Script AS ms
WHERE ctm.Item = 0
	AND m.CallTypeID = c.CallTypeID
	AND c.CallTypeID = ctm.CallTypeID
	AND m.DialedNumberID = d.DialedNumberID
	AND ms.MasterScriptID = ctm.MasterScriptID
--	AND d.RoutingClientID = 5001 -- a CVP routing client
--	AND ms.EnterpriseName LIKE 'xxx%'
ORDER BY d.DialedNumberString

You may want to set the Routing Client and you may want to look at specific scripts.

If you don't care about the Dialed Number then all you want is

SELECT c.EnterpriseName AS CallType
	,c.Description
	,ms.EnterpriseName AS 'Scheduled Script'
FROM Call_Type AS c
	,Call_Type_Map AS ctm
	,Master_Script AS ms
WHERE ctm.Item = 0
	AND c.CallTypeID = ctm.CallTypeID
	AND ms.MasterScriptID = ctm.MasterScriptID

If you want to know Script Cross Refs

SELECT m.EnterpriseName AS Script
	,ct.EnterpriseName AS 'Call Type'
	,ct.Description
FROM Master_Script m
	,Call_Type ct
	,Script s
	,Script_Cross_Reference x
WHERE m.MasterScriptID = s.MasterScriptID
	AND x.ScriptID = s.ScriptID
	AND x.ForeignKey = ct.CallTypeID
	AND x.TargetType = 7
	AND s.Version = m.CurrentVersion
	AND m.EnterpriseName LIKE 'xxxxx%
	--AND ct.EnterpriseName LIKE 'yyyy%'
	ORDER BY ct.EnterpriseName

And yes - Dynamic Call Types are a problem

My God, there are a million queries we could run.

Regards,
Geoff

shobhit20 · ‎09-25-2019

Hi Geoff,
I wanted to delete the ICM user variables which are not referenced/used any where. I know that this can be done manually by checking through Config manager >> Script reference >> User Variables but there are lot of user variables and it will consume lot of time. So I am looking for some SQL query which can return unused user variables in ICM.

Omar Deen · ‎09-25-2019

@shobhit20

Might be easier from a query perspective to show what IS being used rather than the opposite

  SELECT MasterScriptName = MS.EnterpriseName, VariableName = UV.VariableName
  FROM [Ref_Script_Cross_Reference] SCR
  LEFT JOIN Ref_Script S ON S.ScriptID = SCR.ScriptID
  LEFT JOIN Ref_Master_Script MS ON MS.MasterScriptID = S.MasterScriptID
  LEFT JOIN User_Variable UV ON UV.UserVariableID = SCR.ForeignKey
  WHERE SCR.TargetType = '31' 
  GROUP BY MS.EnterpriseName, UV.VariableName
  ORDER BY UV.VariableName

shobhit20 · ‎09-26-2019

Thanks Omar. I was able to get the user variables which are being used. Is there a way how can we figure out the unused one's like comparing this query results with something from ICM?

Omar Deen · ‎09-26-2019

SELECT UV.UserVariableID, UV.VariableName AS VariablesNotBeingUsed,
CASE UV.ObjectType
WHEN '1' THEN 'Service'
WHEN '2' THEN 'Skill Group'
WHEN '3' THEN 'Agent'
WHEN '4' THEN 'Translation Route'
WHEN '5' THEN 'Agent Administration Group'
WHEN '6' THEN 'Announcement'
WHEN '7' THEN 'Call Type'
WHEN '8' THEN 'Enterprise Service'
WHEN '9' THEN 'Enterprise Skill Group'
WHEN '10' THEN 'Region'
WHEN '11' THEN 'Dialed Number'
WHEN '12' THEN 'Logical Interface Controller'
WHEN '13' THEN 'Physical Interface Controller'
WHEN '14' THEN 'Peripheral'
WHEN '15' THEN 'Routing Client'
WHEN '16' THEN 'Trunk Group'
WHEN '17' THEN 'Route'
WHEN '18' THEN 'Peripheral Target'
WHEN '19' THEN 'Label'
WHEN '20' THEN 'Master Script'
WHEN '21' THEN 'Script Table'
WHEN '22' THEN 'Script Table Column'
WHEN '23' THEN 'Script'
WHEN '24' THEN 'Schedule'
WHEN '25' THEN 'ICR View'
WHEN '26' THEN 'View Column'
WHEN '27' THEN 'Network Trunk Group'
WHEN '28' THEN 'Service Array'
WHEN '29' THEN 'Application Gateway'
WHEN '30' THEN 'Device Target'
WHEN '31' THEN 'User Variable'
WHEN '32' THEN 'User Formula'
WHEN '33' THEN 'Network VRU Script'
WHEN '34' THEN 'Scheduled Target'
WHEN '35' THEN 'Network VRU'
WHEN '36' THEN 'Skill Group Member'
WHEN '37' THEN 'Expanded Call Variable'
WHEN '38' THEN 'Agent Team'
WHEN '39' THEN 'Campaign'
WHEN '40' THEN 'Dialer'
WHEN '41' THEN 'Import Rule'
WHEN '42' THEN 'Query Rule'
WHEN '43' THEN 'Campaign Query Rule'
WHEN '44' THEN 'Dialer Port Map'
WHEN '45' THEN 'Message Category'
WHEN '46' THEN 'Message Destination'
WHEN '47' THEN 'Response Template'
ELSE 'UNKNOWN'
END AS ObjectType, UV.Description
FROM User_Variable UV
LEFT JOIN (SELECT DISTINCT UVID = UV.UserVariableID, UVName = UV.VariableName
FROM Ref_Script_Cross_Reference SCR
LEFT JOIN Ref_Script S ON S.ScriptID = SCR.ScriptID
LEFT JOIN Ref_Master_Script MS ON MS.MasterScriptID = S.MasterScriptID
LEFT JOIN User_Variable UV ON UV.UserVariableID = SCR.ForeignKey
WHERE SCR.TargetType = '31') 
TempTable ON TempTable.UVID = UV.UserVariableID
WHERE TempTable.UVID IS NULL
ORDER BY UV.VariableName

Please rate helpful posts