Showing results for 
Search instead for 
Did you mean: 
Walkthrough Wednesdays

Cisco UCCE SQL Queries


Note: if facing problem, avoid to copy paste these queries. :)

1. Check active scripts

select * from Master_Script

2. Check total number of active scripts

select count(*)from Master_Script

3. Check Call records in ICM database (RCD table) using ANI

Select * FROM Route_Call_Detail WHERE  ANI='replacewithNumber'

Select * From Route_Call_Detail
WHERE ANI LIKE '%replacewithNumber'
--WHERE ANI LIKE '%replacewithNumber'
AND DateTime > '5/2/2016 8:00:00'

SELECT DateTime, Variable9, Variable10, Variable5, Variable6
FROM Route_Call_Detail
WHERE ANI Like '%replacewithNumber'
--WHERE ANI LIKE '%replacewithNumber'
AND DateTime > '5/2/2016 8:00:00'

4. Check Call records in ICM database (TCD table) using ANI

Select * FROM Termination_Call_Detail WHERE ANI='replacewithNumber'

5. Check Dialed Number, Call Type and Scripts mappings

Select Dialed_Number.DialedNumberString,Dialed_Number.DialedNumberID,Call_Type.EnterpriseName As CalltypeName,Call_Type.CallTypeID,
Master_Script.EnterpriseName As ScriptName,Master_Script.MasterScriptID
from Master_Script, Call_Type_Map, Call_Type
, Dialed_Number_Map, Dialed_Number
where Master_Script.MasterScriptID = Call_Type_Map.MasterScriptID
and Call_Type_Map.CallTypeID = Call_Type.CallTypeID
and Call_Type.CallTypeID = Dialed_Number_Map.CallTypeID
and Dialed_Number_Map.DialedNumberID = Dialed_Number.DialedNumberID
order by Dialed_Number.DialedNumberString

6. Check Scripts and Skill Groups mappings

Select Master_Script.EnterpriseName as ScriptName, Master_Script.CurrentVersion, Script.Version, Skill_Group.EnterpriseName as SkillGroupName From Master_Script Join Script ON Master_Script.MasterScriptID = Script.MasterScriptID Join Script_Cross_Reference SCR ON SCR.TargetType = 2 and SCR.ScriptID = Script.ScriptID, Skill_Group Where Master_Script.CurrentVersion = Script.Version and Skill_Group.SkillTargetID = SCR.ForeignKey and Master_Script.CurrentVersion = Script.Version

7. Check script related configurations using query, Replace text with script name *(Replace with Script Name)

SELECT TargetType,

       XR.ScriptID , MS.EnterpriseName as Script,

       SG.SkillTargetID, SG.EnterpriseName as Skill_2,  

       CT.CallTypeID, CT.EnterpriseName as CallType_7,  

       ESG.EnterpriseSkillGroupID, ESG.EnterpriseName as EntSkill_9,

       REG.RegionID, REG.EnterpriseName as Region_10,

       R.RouteID, R.EnterpriseName as Route_17, 

      MSC.MasterScriptID, MSC.EnterpriseName as MSEntName_20,

       UV.UserVariableID, UV.VariableName as Variable_31, 

       UF.UserFormulaID, UF.EnterpriseName as Formula_32,

       VRU.NetworkVruScriptID, VRU.EnterpriseName as VRU_33,

       ECC.ExpandedCallVariableID, ECC.EnterpriseName as ECC_37 

FROM Script_Cross_Reference XR


  SELECT iSC.ScriptID AS ScriptID, iMS.EnterpriseName, iMS.CurrentVersion

    FROM Master_Script iMS

      INNER JOIN Script iSC ON iSC.MasterScriptID = iMS.MasterScriptID AND iSC.Version = iMS.CurrentVersion

-- Plug in your Script name in the next line...

    WHERE (iMS.EnterpriseName LIKE '%Replace with Script Name%')

    GROUP BY iSC.ScriptID, iMS.EnterpriseName, iMS.CurrentVersion)

    MS ON MS.ScriptID = XR.ScriptID

  left OUTER JOIN Skill_Group SG on SG.SkillTargetID = XR.ForeignKey and XR.TargetType = 2

  left OUTER JOIN Call_Type CT on CT.CallTypeID = XR.ForeignKey and XR.TargetType = 7

  left OUTER JOIN Enterprise_Skill_Group ESG on ESG.EnterpriseSkillGroupID = XR.ForeignKey and XR.TargetType = 9

  left outer join Region REG on RegionID = XR.ForeignKey and TargetType=10

  left outer join Route R on RouteID = XR.ForeignKey and TargetType=17

  left outer join Master_Script MSC on MasterScriptID = XR.ForeignKey and TargetType=20

  left outer join User_Variable UV on UserVariableID = XR.ForeignKey and TargetType=31

  left outer join User_Formula UF on UserFormulaID = XR.ForeignKey and TargetType=32

  left outer join Network_Vru_Script VRU on NetworkVruScriptID = XR.ForeignKey and TargetType=33

  left outer join Expanded_Call_Variable ECC on ExpandedCallVariableID= XR.ForeignKey and TargetType=37

ORDER BY MS.EnterpriseName, XR.TargetType;


8. Primary Handled Call Count

select Count(CallDisposition) As PrimaryHandledCallCount from Termination_Call_Detail where CallDisposition in (13, 52) and DateTime>='02/09/2016 00:00' and DateTime<='02/09/2016 13:03'



9. Abandoned in Network Flag Count

Select Count(CallDisposition) As AbandonedinNetworkFlagCount from Termination_Call_Detail where DateTime>='02/09/2015 00:00' and DateTime<='02/09/2015 13:17' and CallDispositionFlag = 1

10. Abandoned Delay Count

Select Count(CallDisposition) As AbandonedDelayCount from Termination_Call_Detail where DateTime>='02/09/2015 00:00' and DateTime<='02/09/2015 13:29' and CallDispositionFlag = 4

11. Call_Type Count & Enterprise Name / Skill Group Count & Enterprise Name

select count(*) from Call_Type

select EnterpriseName from Call_Type

select count(*) from Skill_Group

select EnterpriseName from Skill_Group

12. Retrieve and use Column Tile for values


 r.DialedNumberString AS [DNIS]





,r.Variable3 AS [LineOfBusiness1]

,r.Variable4 AS [LineofBusiness2]

,m.EnterpriseName AS [Script Name]

FROM pic4_hds.dbo.t_Route_Call_Detail AS r

JOIN pic4_awdb.dbo.t_Script AS s ON r.ScriptID=s.ScriptID

JOIN pic4_awdb.dbo.t_Master_Script AS m ON s.MasterScriptID=m.MasterScriptID

Where r.ANI =''replacewithNumber''AND r.DateTime BETWEEN '2015-12-07 12:56:00'AND '2015-12-07 13:00:0'

13. For Presistent Variable value in Logger (run on Logger Db)

select * from Persistent_Variable
select * from User_Variable

User_Variable INNER JOIN Persistent_Variable ON User_Variable.UserVariableID = Persistent_Variable.UserVariableID
SELECT uv.ObjectType, pv.ValueInt, uv.VariableName, pv.ValueChar
FROM Persistent_Variable pv, User_Variable uv
WHERE pv.UserVariableID = uv.UserVariableID
ORDER BY uv.VariableName DESC


used many of these queries in my cut-overs

Thanks Amir... Keep up the good work :)

Hi Amir, Is it possible to get list of all user variables and their associated script names using Database query?

Nice Work!!!