cancel
Showing results for 
Search instead for 
Did you mean: 
cancel

Cisco UCCE SQL Queries

9792
Views
50
Helpful
3
Comments

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

  INNER JOIN (

  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

Select

 r.DialedNumberString AS [DNIS]

,r.ANI

,r.DateTime

,r.RouterCallKeyDay

,r.RouterCallKey

,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

select
User_Variable.VariableName,
Persistent_Variable.ValueInt,
Persistent_Variable.ValueFloat,
Persistent_Variable.ValueChar
From
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

Comments
Beginner

used many of these queries in my cut-overs

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

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

Nice Work!!!

CreatePlease to create content
Content for Community-Ad
Future of Work Virtual Summit Day 5

Cisco COVID-19 Survey