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
14. Cisco Outbound Dialer Campaign Results (Use instance_awdb)
To find the campaign ID use (select * from Campaign)
select Phone, PhoneID, FirstName, LastName, AccountNumber, CallResult, DateTime from Dialer_Detail
WHERE DateTime between '01/01/2023 00:00' AND '01/02/2023 23:59'
AND CampaignID = 'replacewithcampaignID'
