This is an old revision of the document!


UCCE SQL

Note: Prefix all report SQL with

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT TOP (1000)
 
StartDateTime = Route_Call_Detail.DateTime, 
MRDomainID = Route_Call_Detail.MRDomainID,
MRDomainName = Media_Routing_Domain.EnterpriseName,
DBDateTime = Route_Call_Detail.DbDateTime, 
ANI = ISNULL(Route_Call_Detail.ANI,'NULL'), 
DialedNumberString = Route_Call_Detail.DialedNumberString, 
RoutingClientID = Route_Call_Detail.RoutingClientID, 
RoutingClientName = Routing_Client.EnterpriseName,
CallTypeName = Call_Type.EnterpriseName,
MasterScriptName = Master_Script.EnterpriseName,
FinalObjectID = Route_Call_Detail.FinalObjectID, 
Label = Route_Call_Detail.Label, 
RouterCallKeySequenceNumber = [RouterCallKeySequenceNumber],
RouterQueueTime = [RouterQueueTime],
RecoveryKey = Route_Call_Detail.RecoveryKey, 
RouterCallKeyDay = Route_Call_Detail.RouterCallKeyDay, 
RouterCallKey = Route_Call_Detail.RouterCallKey, 
VruScripts = Route_Call_Detail.VruScripts, 
BeganRoutingDateTime = Route_Call_Detail.BeganRoutingDateTime, 
BeganCallTypeDateTime = Route_Call_Detail.BeganCallTypeDateTime, 
TargetType = Route_Call_Detail.TargetType, 
RequeryResult = Route_Call_Detail.RequeryResult, 
VruProgress = Route_Call_Detail.VruProgress, 
RouterErrorCode = Route_Call_Detail.RouterErrorCode,
RouteID = RouteID,
RequestType = RequestType,
Var1 = Route_Call_Detail.Variable1,
Var2 = Route_Call_Detail.Variable2,
Var3 = Route_Call_Detail.Variable3,
Var4 = Route_Call_Detail.Variable4,
Var5 = Route_Call_Detail.Variable5,
Var6 = Route_Call_Detail.Variable6,
Var7 = Route_Call_Detail.Variable7,
Var8 = Route_Call_Detail.Variable8,
Var9 = Route_Call_Detail.Variable9,
Var10 = Route_Call_Detail.Variable10,
UserToUser = UserToUser,
CDPD = CDPD,
CED = CED
 
FROM 
Route_Call_Detail(nolock), 
Script(nolock), 
Master_Script(nolock), 
Call_Type(nolock), 
Routing_Client(nolock),
Media_Routing_Domain(nolock)
 
WHERE
(Route_Call_Detail.ScriptID = Script.ScriptID)
AND (Route_Call_Detail.MRDomainID = Media_Routing_Domain.MRDomainID)
AND (Script.MasterScriptID = Master_Script.MasterScriptID)
AND (Route_Call_Detail.CallTypeID = Call_Type.CallTypeID)
AND (Route_Call_Detail.RoutingClientID = Routing_Client.RoutingClientID)
AND (Route_Call_Detail.DbDateTime > GetDate()-1) 
--AND (Route_Call_Detail.ANI = '865551234')
--AND (Route_Call_Detail.DialedNumberString = '55555')
--AND (Route_Call_Detail.Label = '55555')
 
ORDER BY 
Route_Call_Detail.DbDateTime DESC

CallType <> DialedNumberString

SELECT     
DialedNumberString = Dialed_Number.DialedNumberString, 
RC_EnterpriseName = Routing_Client.EnterpriseName, 
CT_EnterpriseName = Call_Type.EnterpriseName 
 
FROM         
Routing_Client 
 
INNER JOIN
Dialed_Number ON Routing_Client.RoutingClientID = Dialed_Number.RoutingClientID INNER JOIN
Dialed_Number_Map ON Dialed_Number.DialedNumberID = Dialed_Number_Map.DialedNumberID INNER JOIN
Call_Type ON Dialed_Number_Map.CallTypeID = Call_Type.CallTypeID
 
WHERE
-- Dialed_Number.DialedNumberString = '55555'
Call_Type.EnterpriseName = 'Call_Type_Name'
-- AND Routing_Client.EnterpriseName = 'CVPA1.RC'
 
ORDER BY DialedNumberString
SELECT     
DialedNumberString = Dialed_Number.DialedNumberString, 
RC_EnterpriseName = Routing_Client.EnterpriseName, 
CT_EnterpriseName = Call_Type.EnterpriseName 
 
FROM         
Routing_Client 
 
INNER JOIN
Dialed_Number ON Routing_Client.RoutingClientID = Dialed_Number.RoutingClientID INNER JOIN
Dialed_Number_Map ON Dialed_Number.DialedNumberID = Dialed_Number_Map.DialedNumberID INNER JOIN
Call_Type ON Dialed_Number_Map.CallTypeID = Call_Type.CallTypeID
 
WHERE
Routing_Client.EnterpriseName = 'CVPA1.RC'
 
ORDER BY DialedNumberString
SELECT     
Agent_Real_Time.AgentState AS AgentState,
 
CASE Agent_Real_Time.AgentState 
WHEN '0' THEN 'Logged Out'
WHEN '1' THEN 'Logged On'
WHEN '2' THEN 'Not Ready'
WHEN '3' THEN 'Ready'
WHEN '4' THEN 'Talking'
WHEN '5' THEN 'Work Not Ready'
WHEN '6' THEN 'Work Ready'
WHEN '7' THEN 'Busy Other'
WHEN '8' THEN 'Reserved'
WHEN '9' THEN 'Unknown-9'
WHEN '10' THEN 'Calls On Hold'
WHEN '11' THEN 'Active'
WHEN '12' THEN 'Paused'
WHEN '13' THEN 'Interrupted'
WHEN '14' THEN 'Not Active'
 
ELSE 'unknown'
END AS AgentStateName,
COUNT(*) AS COUNT
 
FROM         
Agent_Real_Time INNER JOIN
Agent ON Agent_Real_Time.SkillTargetID = Agent.SkillTargetID INNER JOIN
Person ON Agent.PersonID = Person.PersonID
 
--WHERE
--(Agent_Real_Time.Extension like '7%')
--AND
--(Agent_Real_Time.AgentState = '4')
 
GROUP BY
AgentState
SELECT     
Agent_Real_Time.DateTime AS DateTime, 
Agent_Real_Time.AgentState AS AgentState, 
 
CASE Agent_Real_Time.AgentState 
WHEN '0' THEN 'Logged Out'
WHEN '1' THEN 'Logged On'
WHEN '2' THEN 'Not Ready'
WHEN '3' THEN 'Ready'
WHEN '4' THEN 'Talking'
WHEN '5' THEN 'Work Not Ready'
WHEN '6' THEN 'Work Ready'
WHEN '7' THEN 'Busy Other'
WHEN '8' THEN 'Reserved'
WHEN '9' THEN 'Unknown-9'
WHEN '10' THEN 'Calls On Hold'
WHEN '11' THEN 'Active'
WHEN '12' THEN 'Paused'
WHEN '13' THEN 'Interrupted'
WHEN '14' THEN 'Not Active'
 
ELSE 'unknown'
END AS AgentStateName,
 
Person.FirstName AS FirstName, 
Person.LastName AS LastName,
Agent.EnterpriseName AS EnterpriseName,
Agent_Real_Time.Extension AS Extension
 
FROM         
Agent_Real_Time INNER JOIN
Agent ON Agent_Real_Time.SkillTargetID = Agent.SkillTargetID INNER JOIN
Person ON Agent.PersonID = Person.PersonID
 
--WHERE
--(Agent_Real_Time.Extension like '7%')
--AND
--(Agent_Real_Time.AgentState = '4')
 
ORDER BY
 
Person.LastName
SELECT     
 
Call_Type.EnterpriseName, 
Call_Type_Real_Time.RouterCallsQNow
 
FROM    
 
Call_Type_Real_Time INNER JOIN
Call_Type ON Call_Type_Real_Time.CallTypeID = Call_Type.CallTypeID
 
ORDER BY 
 
Call_Type_Real_Time.RouterCallsQNow DESC
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT 
      SGRT.DateTime
      ,SG.EnterpriseName
	  ,SGRT.RouterCallsQNow
      ,SGRT.TalkingIn
      ,SGRT.TalkingOther
      ,SGRT.TalkingOut
      ,SGRT.CallsQueuedNow
      ,SGRT.CallsInProgress
      ,SGRT.WorkReady
      ,SGRT.WorkNotReady
      ,SGRT.LongestCallQ
      ,SGRT.LoggedOn
      ,SGRT.NotReady
      ,SGRT.Ready
      ,SGRT.LongestAvailAgent
	  ,SGRT.SkillTargetID
      ,SGRT.AgentOutCallsTo5
      ,SGRT.AnswerWaitTimeTo5
      ,SGRT.Avail
      ,SGRT.AvailTimeTo5
      ,SGRT.AvgHandledCallsTalkTimeTo5
      ,SGRT.AvgHandledCallsTimeTo5
      ,SGRT.BusyOther
      ,SGRT.BusyOtherTimeTo5
      ,SGRT.CallsAnsweredTo5
      ,SGRT.CallsHandledTo5
      ,SGRT.CallsOfferedTo5
      ,SGRT.HandledCallsTalkTimeTo5
      ,SGRT.HandledCallsTimeTo5
      ,SGRT.AgentOutCallsTimeTo5
      ,SGRT.Hold
      ,SGRT.HoldTimeTo5
      ,SGRT.LoggedOnTimeTo5
      ,SGRT.NotReadyTimeTo5
      ,SGRT.PercentUtilizationTo5
      ,SGRT.ReservedAgents
      ,SGRT.ReservedStateTimeTo5
      ,SGRT.TalkInTimeTo5
      ,SGRT.TalkOtherTimeTo5
      ,SGRT.TalkOutTimeTo5
      ,SGRT.TalkTimeTo5
      ,SGRT.TransferInCallsTimeTo5
      ,SGRT.TransferInCallsTo5
      ,SGRT.TransferOutCallsTo5
      ,SGRT.WorkNotReadyTimeTo5
      ,SGRT.WorkReadyTimeTo5
      ,SGRT.AutoOutCallsTo5
      ,SGRT.AutoOutCallsTimeTo5
      ,SGRT.AutoOutCallsTalkTimeTo5
      ,SGRT.PreviewCallsTo5
      ,SGRT.PreviewCallsTimeTo5
      ,SGRT.PreviewCallsTalkTimeTo5
      ,SGRT.ReserveCallsTo5
      ,SGRT.ReserveCallsTimeTo5
      ,SGRT.ReserveCallsTalkTimeTo5
      ,SGRT.TalkingAutoOut
      ,SGRT.TalkingPreview
      ,SGRT.TalkingReserve
      ,SGRT.TalkAutoOutTimeTo5
      ,SGRT.TalkPreviewTimeTo5
      ,SGRT.TalkReserveTimeTo5
      ,SGRT.NumAgentsInterruptedNow
      ,SGRT.InterruptedTimeTo5
      ,SGRT.RouterLongestCallInQ
      ,SGRT.IcmAvailable
      ,SGRT.ApplicationAvailable
      ,SGRT.RedirectNoAnsCallsTo5
      ,SGRT.RouterCallsOfferedTo5
      ,SGRT.RouterCallsAbandToAgentTo5
      ,SGRT.RouterCallsDequeuedTo5
      ,SGRT.ServiceLevelTo5
      ,SGRT.ServiceLevelCallsTo5
      ,SGRT.ServiceLevelCallsAbandTo5
      ,SGRT.ServiceLevelCallsDequeuedTo5
      ,SGRT.ServiceLevelRONATo5
      ,SGRT.ServiceLevelCallsOfferedTo5
      ,SGRT.RouterCallsAbandQTo5
      ,SGRT.FutureUseInt1
      ,SGRT.FutureUseInt2
      ,SGRT.FutureUseInt3
      ,SGRT.FutureUseInt4
      ,SGRT.FutureUseInt5
      ,SGRT.RouterCallsAbandDequeuedTo5
  FROM 
  vhi_awdb.dbo.Skill_Group_Real_Time SGRT,
  vhi_awdb.dbo.Skill_Group SG
 
  WHERE 
 SGRT.SkillTargetID = SG.SkillTargetID
 
ORDER BY SGRT.RouterCallsQNow DESC, SG.EnterpriseName ASC

SQL Query for TCD and joining the Call Detail Table

(Thanks to Jason Williams)

SELECT tcd.DateTime,tcd.DigitsDialed,rcd.Label,tcd.PeripheralID,*
 
FROM Termination_Call_Detail AS tcd, Route_Call_Detail AS rcd 
 
WHERE 
 
CallDisposition = 1 AND 
tcd.DateTime > GetDate()-1 AND 
rcd.DateTime > GetDate()-1 AND 
rcd.RouterCallKey = tcd.RouterCallKey AND 
rcd.RouterCallKeyDay = tcd.RouterCallKeyDay
SELECT tcd.DateTime,tcd.DigitsDialed,rcd.Label,tcd.PeripheralNumber,* 
FROM
Termination_Call_Detail AS tcd, Route_Call_Detail AS rcd 
WHERE 
CallDisposition = 1 AND 
tcd.DateTime > '22 June 2010 00:00' AND 
rcd.DateTime > '22 June 2010 00:00' AND 
rcd.RouterCallKey = tcd.RouterCallKey AND 
rcd.RouterCallKeyDay = tcd.RouterCallKeyDay
SELECT Top 10 DateTime,MaxCallsInProgress,* 
FROM Peripheral_Half_Hour 
WHERE PeripheralID IN('5001', '5002') 
ORDER BY MaxCallsInProgress DESC
SELECT SUM(CallsOfferedToHalf)AS Calls ,DateTime 
FROM Peripheral_Half_Hour
WHERE DateTime > GetDate()-1
GROUP BY DateTime
ORDER BY Calls DESC
 
 
SELECT SUM(CallsHandledHalf) AS TotalHalfCalls,DateTime  
FROM t_Call_Type_Half_Hour
WHERE DateTime > GetDate()-1
GROUP BY DateTime
ORDER BY TotalHalfCalls DESC

Note: there will be some overlap, as this will document the total agent logged in, in a half hour period. So if there is a high Agent “Swap count” due to change in shift during the half hour, concurrent agent count will be lower than the figure determined here
Remove the Where DateTime Line to get max number of agents logged in over a half hour over life of HDS data.

SELECT DateTime,COUNT(*)AS AgentCount 
FROM Agent_Interval
WHERE DateTime > Getdate()-1
AND MRDomainID = '1'
GROUP BY DateTime
ORDER BY AgentCount DESC

SQL Join and Case Example

SELECT     
Agent.EnterpriseName, 
Agent_Event_Detail.DateTime, 
Agent_Event_Detail.Event , 
 
CASE Agent_Event_Detail.Event  
WHEN '1' THEN 'Login'
WHEN '2' THEN 'Logout'
WHEN '3' THEN 'Not Ready'
ELSE 'unknown'
END AS Event_Transition,
 
Agent_Event_Detail.LoginDateTime, 
Agent_Event_Detail.RecoveryKey, 
Agent_Event_Detail.Duration, 
Reason_Code.ReasonText
 
FROM         
Reason_Code 
 
INNER JOIN
Agent_Event_Detail ON Reason_Code.ReasonCode = Agent_Event_Detail.ReasonCode INNER JOIN
Agent ON Agent_Event_Detail.SkillTargetID = Agent.SkillTargetID
SELECT     
Agent_Team.EnterpriseName AS AgentTeam,
Person.FirstName, 
Person.LastName, 
Person.LoginName, 
Agent_Real_Time.AgentState, 
CASE Agent_Real_Time.AgentState 
WHEN '0' THEN 'Logged Out'
WHEN '1' THEN 'Logged On'
WHEN '2' THEN 'Not Ready'
WHEN '3' THEN 'Ready'
WHEN '4' THEN 'Talking'
WHEN '5' THEN 'Work Not Ready'
WHEN '6' THEN 'Work Ready'
WHEN '7' THEN 'Busy Other'
WHEN '8' THEN 'Reserved'
WHEN '9' THEN 'Unknown-9'
WHEN '10' THEN 'Calls On Hold'
WHEN '11' THEN 'Active'
WHEN '12' THEN 'Paused'
WHEN '13' THEN 'Interrupted'
WHEN '14' THEN 'Not Active'
ELSE 'unknown'
END AS AgentStateName,
Agent_Real_Time.Extension
 
FROM         
Agent INNER JOIN
Person ON Agent.PersonID = Person.PersonID INNER JOIN
Agent_Team_Member ON Agent.SkillTargetID = Agent_Team_Member.SkillTargetID INNER JOIN
Agent_Team ON Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID INNER JOIN
Agent_Real_Time ON Agent.SkillTargetID = Agent_Real_Time.SkillTargetID
 
--WHERE
--(Agent_Real_Time.Extension like '7%')
--AND
--(Agent_Real_Time.AgentState = '4')
 
 
ORDER BY 
Agent_Team.EnterpriseName,
FirstName,
LastName
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
 
SELECT DialedNumberString, COUNT(*) AS CallCount
FROM 
Route_Call_Detail (nolock)
WHERE 
(CallTypeID = '5001' OR CallTypeID = '5002') AND
(Route_Call_Detail.DbDateTime >= Getdate()-1)
 
GROUP BY DialedNumberString
 
ORDER BY DialedNumberString
SELECT COUNT(*) AS CallCount
FROM 
Route_Call_Detail (nolock)
WHERE 
(DialedNumberString) = '|12345' AND
(CallTypeID = '5001') AND
(Route_Call_Detail.DbDateTime >= '07/29/2013 00:00:00') AND
(Route_Call_Detail.DbDateTime <= '07/30/2013 00:00:00')

How to get the time for one hour ago.

SELECT DateAdd(hh,-1,GetDate())
 
SELECT * FROM <tablename> WHERE DateTime > DateAdd(hh,-1,GetDate())

Get a Count by DialedNumberString in the last Hour

SELECT DialedNumberString, COUNT(DialedNumberString) AS NumberOfCalls
FROM Route_Call_Detail
WHERE DateTime > (DateAdd(hh,-1,GetDate()))
GROUP BY DialedNumberString 
ORDER BY DialedNumberString
DECLARE @StartExt INT = 10000;
DECLARE @EndExt INT = 10250;
 
 
SELECT (@StartExt + NUMBER) AS Extension 
FROM master..spt_values
WHERE TYPE = 'P' 
AND NUMBER BETWEEN 0 AND (@EndExt - @StartExt)
AND (@StartExt + NUMBER) NOT IN
(SELECT <dbname>..Agent_Real_Time.Extension FROM <dbname>..Agent_Real_Time WHERE Extension BETWEEN @StartExt AND @EndExt)
SELECT Datepart (HOUR, DateTime) AS HOUR, COUNT(*) AS CALLS
  FROM [ipcc1_awdb].[dbo].[Termination_Call_Detail]
  WHERE 
  DateTime > '2014-02-03 00:00'
AND DateTime < '2014-02-03 23:59'
 
 GROUP BY 
Datepart (HOUR, DateTime)

Top Call by Dialed Number String

SELECT 
DialedNumberString, COUNT(*) AS COUNT
 
FROM
 
dbo.Route_Call_Detail
WHERE DateTime > GetDate()-1
 
GROUP BY DialedNumberString
 
ORDER BY COUNT DESC
USE ipcc1_awdb
 
SELECT     
Campaign_Query_Rule.CampaignID, 
Query_Rule.QueryRuleID, 
Campaign.CampaignName, 
Campaign.Enabled AS CampaignEnabled, 
Query_Rule.QueryRuleName, 
Campaign_Query_Rule.StartHours, 
Campaign_Query_Rule.ListOrder, 
Campaign_Query_Rule.StartMinutes, 
Campaign_Query_Rule.EndHours, 
Campaign_Query_Rule.EndMinutes, 
Campaign_Query_Rule.Penetration, 
Campaign_Query_Rule.Duration, 
Campaign_Query_Rule.HitRate
 
FROM         
Campaign_Query_Rule 
INNER JOIN
Campaign ON Campaign_Query_Rule.CampaignID = Campaign.CampaignID INNER JOIN
Query_Rule ON Campaign_Query_Rule.QueryRuleID = Query_Rule.QueryRuleID
 
WHERE Campaign.Enabled = 'Y'
ORDER BY CampaignID, QueryRuleID
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [DialingListID]
      ,[Phone01]
      ,[CallbackNumber]
      ,[CallResult]
      ,[CallResult01]
      ,[CallResult02]
      ,[LastZoneDialed]
      ,[LastNumberDialedZone1]
      ,[LastNumberDialedZone2]
      ,[CallsMadeToZone1]
      ,[CallsMadeToZone2]
      ,[CallbackDateTimeZone1]
      ,[CallbackDateTime01]
      ,[CallbackDateTime02]
      ,[GMTPhone01]
      ,[GMTPhone02]
      ,[DSTPhone01]
      ,[DSTPhone02]
      ,[CallStatusZone1]
      ,[CallStatusZone2]
      ,[AccountNumber]
      ,[LastName]
      ,[FirstName]
      ,[ImportRuleDate]
  FROM [ipcc1_baA].[dbo].[DL_XXXX_YYYY]
  WHERE 
  ((CallStatusZone1 = 'A')
  OR(CallStatusZone1 = 'P'))
  AND ImportRuleDate < '2014-09-14'
  ORDER BY DialingListID DESC

Campaign Close Records

/****** Script for SelectTopNRows command from SSMS  ******/
UPDATE [DL_5023_5026]
SET CallStatusZone1='C',
CallStatusZone2='C'
 
  WHERE 
  ((CallStatusZone1 = 'A')
  OR(CallStatusZone1 = 'P'))
  AND ImportRuleDate < '2014-09-14'

http://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/outbound_option/outboundoption9_0/installation/guide/UCCE_BK_O4A87BBC_00_outbound-option-guide-for-cisco/UCCE_BK_O4A87BBC_00_outbound-option-guide-for-cisco_appendix_01100.html

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT 'CampaignTitleName' AS CampaignTitle
	  ,[DialingListID]
      ,[Phone01]
      ,CASE [CallResult] 
      WHEN '0' THEN ''
      WHEN '2' THEN 'Dial Error'
      WHEN '3' THEN 'Not in Service'
      WHEN '4' THEN 'No Ringback'
      WHEN '5' THEN 'Operator Intercept'
      WHEN '6' THEN 'No Dial Tone'
      WHEN '7' THEN 'Number Invalid'
      WHEN '8' THEN 'No Answer'
      WHEN '9' THEN 'Busy'
      WHEN '10' THEN 'Answered'
      WHEN '11' THEN 'Fax'
      WHEN '12' THEN 'Answering Machine'
      WHEN '13' THEN 'Dialer stopped due to lack of agents or network stopped dialing before it was complete'
      WHEN '14' THEN 'Customer requested callback'
      WHEN '16' THEN 'Call was abandoned by the dialer due to lack of agents'
      WHEN '17' THEN 'Failed to reserve agent for personal callback.'
      WHEN '18' THEN 'Agent has skipped or rejected a preview call or personal callback call.'
      WHEN '19' THEN 'Agent has skipped or rejected a preview call with the close option'
      WHEN '20' THEN 'Customer has been abandoned to an IVR'
      WHEN '21' THEN 'Customer dropped call within configured abandoned time'
      WHEN '22' THEN 'Network Answering Machine'
      WHEN '23' THEN 'Number successfully contacted but wrong number'
      WHEN '24' THEN 'Number successfully contacted but reached the wrong person'
      WHEN '25' THEN 'Dialer has flushed this record due to a change in the skillgroup, the campaign, etc.'
      WHEN '26' THEN 'The number was on the do not call list'
      WHEN '27' THEN 'Call disconnected by the carrier or the network while ringing'
      WHEN '28' THEN 'Data error or no-value call e.g. no voice detected'
 
      ELSE CAST(CallResult AS VARCHAR(200))
      END AS 'CallResultString'
 
      ,[CallsMadeToZone1] AS CallAttempts
      ,CASE [CallStatusZone1]
      WHEN 'A' THEN 'Active'
      WHEN 'B' THEN 'Callback'
      WHEN 'C' THEN 'Closed'
      WHEN 'J' THEN 'Agent Rejected'
      WHEN 'M' THEN 'Maximum Attempts Reached'
      WHEN 'P' THEN 'Pending'
      WHEN 'R' THEN 'Retry'
      WHEN 'S' THEN 'Personal Callback'
      WHEN 'U' THEN 'Unknown'
      WHEN 'X' THEN 'Personal Callback Abandoned'
      ELSE CAST(CallStatusZone1 AS VARCHAR(200))
      END AS 'CallStatus'
      ,[CallbackNumber]
      ,[CallbackDateTime01] AS CallbackDateTime
      ,[AccountNumber]
      ,[LastName]
      ,[FirstName]
      ,[ImportRuleDate]
  FROM [<icm-instance-name>_baA].[dbo].[DL_CCCC_QQQQ]
  WHERE 
  ImportRuleDate > GETDATE()-100
  ORDER BY DialingListID DESC
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
--USE ipcc1_awdb
SELECT     
	Dialer_Detail.DateTime,
--Dialer_Detail.DbDateTime,
	Campaign.CampaignName, 
--Dialer_Detail.CampaignID AS CampaignID,
--Query_Rule.QueryRuleName, 
--Dialer_Detail.QueryRuleID AS QueryRuleID, 
--Import_Rule.ImportRuleName, 
--Query_Rule.ImportRuleID, 
--Import_Rule.FilePath, 
--Dialer_Detail.DialingListID AS Campaign_QueryRule_DialingListID, 
	Dialer_Detail.Phone AS Phone, 
--Dialer_Detail.CallResult AS CallResult,
      CASE [CallResult]
      WHEN '0' THEN ''
      WHEN '2' THEN 'Dial Error'
      WHEN '3' THEN 'Not in Service'
      WHEN '4' THEN 'No Ringback'
      WHEN '5' THEN 'Operator Intercept'
      WHEN '6' THEN 'No Dial Tone'
      WHEN '7' THEN 'Number Invalid'
      WHEN '8' THEN 'No Answer'
      WHEN '9' THEN 'Busy'
      WHEN '10' THEN 'Answered'
      WHEN '11' THEN 'Fax'
      WHEN '12' THEN 'Answering Machine'
      WHEN '13' THEN 'Dialer stopped due to lack of agents or network stopped dialing before it was complete'
      WHEN '14' THEN 'Customer requested callback'
      WHEN '16' THEN 'Call was abandoned by the dialer due to lack of agents'
      WHEN '17' THEN 'Failed to reserve agent for personal callback.'
      WHEN '18' THEN 'Agent has skipped or rejected a preview call or personal callback call.'
      WHEN '19' THEN 'Agent has skipped or rejected a preview call with the close option'
      WHEN '20' THEN 'Customer has been abandoned to an IVR'
      WHEN '21' THEN 'Customer dropped call within configured abandoned time'
      WHEN '22' THEN 'Network Answering Machine'
      WHEN '23' THEN 'Number successfully contacted but wrong number'
      WHEN '24' THEN 'Number successfully contacted but reached the wrong person'
      WHEN '25' THEN 'Dialer has flushed this record due to a change in the skillgroup, the campaign, etc.'
      WHEN '26' THEN 'The number was on the do not call list'
      WHEN '27' THEN 'Call disconnected by the carrier or the network while ringing'
      WHEN '28' THEN 'Data error or no-value call e.g. no voice detected'
 
      ELSE CAST(CallResult AS VARCHAR(200))
      END AS 'CallResultString',
 
--Dialer_Detail.CallStatusZone1 AS CallStatusID,
 
      CASE [CallStatusZone1]
      WHEN 'A' THEN 'Active'
      WHEN 'B' THEN 'Callback'
      WHEN 'C' THEN 'Closed'
      WHEN 'J' THEN 'Agent Rejected'
      WHEN 'M' THEN 'Maximum Attempts Reached'
      WHEN 'P' THEN 'Pending'
      WHEN 'R' THEN 'Retry'
      WHEN 'S' THEN 'Personal Callback'
      WHEN 'U' THEN 'Unknown'
      WHEN 'X' THEN 'Personal Callback Abandoned'
      ELSE CAST(CallStatusZone1 AS VARCHAR(200))
      END AS 'CallStatus',
 
--Dialer_Detail.SkillGroupSkillTargetID,
Skill_Group.EnterpriseName,
--Dialer_Detail.AgentPeripheralNumber,
--Dialer_Detail.PeripheralID,
Agent.EnterpriseName,
Dialer_Detail.AccountNumber,
Dialer_Detail.FirstName,
Dialer_Detail.LastName,
Dialer_Detail.CallbackPhone,
Dialer_Detail.CallbackDateTime,
--Dialer_Detail.DialingMode,
	CASE [DialingMode]
	WHEN '1' THEN 'Predictive Only'
	WHEN '2' THEN 'Predictive Blended'
	WHEN '3' THEN 'Preview Only'
	WHEN '4' THEN 'Preview Blended'
	WHEN '5' THEN 'Progressive Only'
	WHEN '6' THEN 'Progressive Blended'
	WHEN '7' THEN 'Direct Preview Only'
	WHEN '8' THEN 'Direct Preview Blended'
	ELSE CAST(DialingMode AS VARCHAR(200))
	END AS DialedModeString,
Dialer_Detail.RouterCallKey,
Dialer_Detail.RouterCallKeyDay,
Dialer_Detail.ReservationCallDuration/1000 AS ReservationCallDurationSec,
Dialer_Detail.PreviewTime/1000 AS PreviewTimeSec,
Dialer_Detail.CallDuration/1000 AS CallDurationSec
 
 
FROM         
Dialer_Detail,
Campaign,
Query_Rule, 
Import_Rule,
Skill_Group,
Agent
 
WHERE 
Dialer_Detail.DateTime > GETDATE()-10
 
AND Dialer_Detail.CampaignID = Campaign.CampaignID
AND Dialer_Detail.QueryRuleID = Query_Rule.QueryRuleID
AND Query_Rule.ImportRuleID = Import_Rule.ImportRuleID
AND Skill_Group.SkillTargetID = Dialer_Detail.SkillGroupSkillTargetID
AND (Dialer_Detail.PeripheralID = Agent.PeripheralID AND Dialer_Detail.AgentPeripheralNumber = Agent.PeripheralNumber)
 
--AND Dialer_Detail.Phone like '08%'
--AND Len(Dialer_Detail.Phone) != '10'
 
ORDER BY DateTime DESC

TO GET THE CAMPAIGN AND QUERY RULE ID

USE <icm-instance-name>_awdb
 
SELECT     
Campaign_Query_Rule.CampaignID, 
Query_Rule.QueryRuleID, 
Campaign.CampaignName, 
Campaign.Enabled AS CampaignEnabled, 
Campaign.[MaxAttempts],
Query_Rule.QueryRuleName, 
Campaign_Query_Rule.ListOrder, 
Campaign_Query_Rule.StartHours, 
Campaign_Query_Rule.StartMinutes, 
Campaign_Query_Rule.EndHours, 
Campaign_Query_Rule.EndMinutes,
Campaign.[HomeEnabled] AS CampaignZone1Enabled,
Campaign.[HomeStartHours] AS CampaignZone1StartHour,
Campaign.[HomeStartMinutes] AS CampaignZone1StartMinutes,
Campaign.[HomeEndHours] AS CampaignZone1EndHour,
Campaign.[HomeEndMinutes] AS CampaignZone1EndMinutes
 
FROM         
Campaign_Query_Rule 
INNER JOIN
Campaign ON Campaign_Query_Rule.CampaignID = Campaign.CampaignID INNER JOIN
Query_Rule ON Campaign_Query_Rule.QueryRuleID = Query_Rule.QueryRuleID
 
WHERE Campaign.Enabled = 'Y'
ORDER BY CampaignID, QueryRuleID
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
USE ucce_awdb
SELECT     
	Dialer_Detail.DateTime,
Dialer_Detail.DbDateTime,
	Campaign.CampaignName, 
--Dialer_Detail.CampaignID AS CampaignID,
--Query_Rule.QueryRuleName, 
--Dialer_Detail.QueryRuleID AS QueryRuleID, 
--Import_Rule.ImportRuleName, 
--Query_Rule.ImportRuleID, 
--Import_Rule.FilePath, 
Dialer_Detail.DialingListID AS Campaign_QueryRule_DialingListID, 
	Dialer_Detail.Phone AS Phone, 
Dialer_Detail.CallResult AS CallResult,
      CASE [CallResult]
      WHEN '0' THEN ''
      WHEN '2' THEN 'Dial Error'
      WHEN '3' THEN 'Not in Service'
      WHEN '4' THEN 'No Ringback'
      WHEN '5' THEN 'Operator Intercept'
      WHEN '6' THEN 'No Dial Tone'
      WHEN '7' THEN 'Number Invalid'
      WHEN '8' THEN 'No Answer'
      WHEN '9' THEN 'Busy'
      WHEN '10' THEN 'Answered'
      WHEN '11' THEN 'Fax'
      WHEN '12' THEN 'Answering Machine'
      WHEN '13' THEN 'Dialer stopped due to lack of agents or network stopped dialing before it was complete'
      WHEN '14' THEN 'Customer requested callback'
      WHEN '16' THEN 'Call was abandoned by the dialer due to lack of agents'
      WHEN '17' THEN 'Failed to reserve agent for personal callback.'
      WHEN '18' THEN 'Agent has skipped or rejected a preview call or personal callback call.'
      WHEN '19' THEN 'Agent has skipped or rejected a preview call with the close option'
      WHEN '20' THEN 'Customer has been abandoned to an IVR'
      WHEN '21' THEN 'Customer dropped call within configured abandoned time'
      WHEN '22' THEN 'Network Answering Machine'
      WHEN '23' THEN 'Number successfully contacted but wrong number'
      WHEN '24' THEN 'Number successfully contacted but reached the wrong person'
      WHEN '25' THEN 'Dialer has flushed this record due to a change in the skillgroup, the campaign, etc.'
      WHEN '26' THEN 'The number was on the do not call list'
      WHEN '27' THEN 'Call disconnected by the carrier or the network while ringing'
      WHEN '28' THEN 'Data error or no-value call e.g. no voice detected'
 
      ELSE CAST(CallResult AS VARCHAR(200))
      END AS 'CallResultString',
 
--Dialer_Detail.CallStatusZone1 AS CallStatusID,
 
      CASE [CallStatusZone1]
      WHEN 'A' THEN 'Active'
      WHEN 'B' THEN 'Callback'
      WHEN 'C' THEN 'Closed'
      WHEN 'J' THEN 'Agent Rejected'
      WHEN 'M' THEN 'Maximum Attempts Reached'
      WHEN 'P' THEN 'Pending'
      WHEN 'R' THEN 'Retry'
      WHEN 'S' THEN 'Personal Callback'
      WHEN 'U' THEN 'Unknown'
      WHEN 'X' THEN 'Personal Callback Abandoned'
      ELSE CAST(CallStatusZone1 AS VARCHAR(200))
      END AS 'CallStatus',
 
--Dialer_Detail.SkillGroupSkillTargetID,
Skill_Group.EnterpriseName,
Dialer_Detail.AgentPeripheralNumber,
--Dialer_Detail.PeripheralID,
--Agent.EnterpriseName,
Dialer_Detail.AccountNumber,
Dialer_Detail.FirstName,
Dialer_Detail.LastName,
Dialer_Detail.CallbackPhone,
Dialer_Detail.CallbackDateTime,
--Dialer_Detail.DialingMode,
	CASE [DialingMode]
	WHEN '1' THEN 'Predictive Only'
	WHEN '2' THEN 'Predictive Blended'
	WHEN '3' THEN 'Preview Only'
	WHEN '4' THEN 'Preview Blended'
	WHEN '5' THEN 'Progressive Only'
	WHEN '6' THEN 'Progressive Blended'
	WHEN '7' THEN 'Direct Preview Only'
	WHEN '8' THEN 'Direct Preview Blended'
	ELSE CAST(DialingMode AS VARCHAR(200))
	END AS DialedModeString,
Dialer_Detail.RouterCallKey,
Dialer_Detail.RouterCallKeyDay,
Dialer_Detail.ReservationCallDuration/1000 AS ReservationCallDurationSec,
Dialer_Detail.PreviewTime/1000 AS PreviewTimeSec,
Dialer_Detail.CallDuration/1000 AS CallDurationSec
 
 
FROM         
Dialer_Detail,
Campaign,
Query_Rule, 
Import_Rule,
Skill_Group
--Agent
 
WHERE 
Dialer_Detail.DateTime > GETDATE()-1
 
AND Dialer_Detail.CampaignID = Campaign.CampaignID
AND Dialer_Detail.QueryRuleID = Query_Rule.QueryRuleID
AND Query_Rule.ImportRuleID = Import_Rule.ImportRuleID
 
AND Skill_Group.SkillTargetID = Dialer_Detail.SkillGroupSkillTargetID
--AND ((Dialer_Detail.AgentPeripheralNumber = Agent.PeripheralNumber) OR (Dialer_Detail.AgentPeripheralNumber = NULL))
AND (Dialer_Detail.AgentPeripheralNumber IS NULL)
 
ORDER BY DateTime DESC

Agent Wrapup Daily

/****** Script for SelectTopNRows command from SSMS  ******/
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
 
;WITH AgentWrapupDaily (PeripheralID,AgentSkillTargetID,DateTime,WrapupData,WorkTime)
AS
(SELECT 
	  [PeripheralID]
      ,[AgentSkillTargetID]
      ,CONVERT(CHAR(10),[DateTime],101) 
      ,[WrapupData]
      ,[WorkTime]
 
  FROM [Termination_Call_Detail] nolock
  WHERE
  AgentSkillTargetID IS NOT NULL)
 
  SELECT 
	   [PeripheralID]
      ,[AgentSkillTargetID]
	  ,CONVERT(DateTime,[DateTime],101) AS DateTime
      ,isNULL([WrapupData],'') AS WrapupData
      ,isNULL(SUM(WorkTime),0) AS WorkTimeSum
  FROM AgentWrapupDaily
  GROUP BY PeripheralID, AgentSkillTargetID, DateTime, WrapupData
  ORDER BY AgentSkillTargetID, DateTime DESC
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
 
SELECT Agent.PeripheralNumber
FROM 
Agent,
AgentRealTime
 
WHERE
Agent.SkillTargetID = Agent_Real_Time.SkillTargetID AND
Agent_Real_Time.Extension = '<extension>'

SIZING

SELECT TOP (5)    
Call_Type_Interval.DateTime, 
Call_Type_Interval.CallTypeID, 
Call_Type.EnterpriseName, 
Call_Type_Interval.CallsOffered
 
FROM         
Call_Type_Interval INNER JOIN
Call_Type ON Call_Type_Interval.CallTypeID = Call_Type.CallTypeID
 
WHERE DateTime > GetDate()-1
ORDER BY CallsOffered DESC
SELECT COUNT(*) AS CallCount
FROM 
Route_Call_Detail (nolock)
WHERE 
(Route_Call_Detail.DbDateTime >= '03/10/2015 11:00:00') AND
(Route_Call_Detail.DbDateTime <= '03/10/2015 11:30:00')
SELECT  TOP (5)   
Skill_Group_Interval.DateTime, 
Skill_Group_Interval.SkillTargetID, 
Skill_Group.EnterpriseName, 
Skill_Group_Interval.CallsHandled, 
Skill_Group_Interval.AvgHandledCallsTime
 
FROM         
Skill_Group_Interval INNER JOIN
Skill_Group ON Skill_Group_Interval.SkillTargetID = Skill_Group.SkillTargetID
WHERE DateTime > '2016-01-04 00:00:00'
AND DateTime < '2016-01-05 00:00:00'
 
ORDER BY CallsHandled DESC
DECLARE @start_date Datetime
DECLARE @end_date Datetime
--SET @start_date = GETDate()-1
--SET @end_date = GetDate()
SET @start_date = '2016-01-04 11:00:00'
SET @end_date = '2016-01-04 12:00:00'
 
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
 
SELECT --TOP (100)
Duration = AVG(Termination_Call_Detail.Duration)
,NetworkTime = AVG(Termination_Call_Detail.NetworkTime)
,NetQTime = AVG(Termination_Call_Detail.NetQTime)
,LocalQTime = AVG(Termination_Call_Detail.LocalQTime)
,RingTime = AVG(Termination_Call_Detail.RingTime)
,DelayTime = AVG(Termination_Call_Detail.DelayTime)
,TimeToAband = AVG(Termination_Call_Detail.TimeToAband)
,TalkTime = AVG(Termination_Call_Detail.TalkTime)
,HoldTime = AVG(Termination_Call_Detail.HoldTime)
,CallDuration = AVG(Termination_Call_Detail.TalkTime+Termination_Call_Detail.HoldTime)
,ConferenceTime = AVG(Termination_Call_Detail.ConferenceTime)
,WorkTime = AVG(Termination_Call_Detail.WorkTime)
 
 
  FROM 
  Termination_Call_Detail (nolock)
  LEFT JOIN Media_Routing_Domain (nolock) ON (Termination_Call_Detail.MRDomainID = Media_Routing_Domain.MRDomainID)
  LEFT JOIN Agent (nolock) ON (Termination_Call_Detail.AgentSkillTargetID = Agent.SkillTargetID)
  LEFT JOIN Agent SAgent (nolock) ON (Termination_Call_Detail.SourceAgentSkillTargetID = SAgent.SkillTargetID)
  LEFT JOIN Person (nolock) ON (Agent.PersonID = Person.PersonID)
  LEFT JOIN Person SPerson (nolock) ON (SAgent.PersonID = SPerson.PersonID)
  LEFT JOIN Agent_Team_Member (nolock) ON (Agent.SkillTargetID = Agent_Team_Member.SkillTargetID)
  LEFT JOIN Agent_Team (nolock) ON (Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID)
  LEFT JOIN Skill_Group (nolock) ON (Termination_Call_Detail.SkillGroupSkillTargetID = Skill_Group.SkillTargetID)
--  LEFT JOIN Precision_Queue (nolock) on (Termination_Call_Detail.PrecisionQueueID = Precision_Queue.PrecisionQueueID)
  LEFT JOIN Call_Type (nolock) ON (Termination_Call_Detail.CallTypeID = Call_Type.CallTypeID)
 
 WHERE 
 
  Termination_Call_Detail.DateTime >=  @start_date
  AND Termination_Call_Detail.DateTime < @end_date
  --Termination_Call_Detail.PeripheralID = '5000'
  AND RouterCallKey <> '0'
  AND Termination_Call_Detail.PeripheralCallType = '2'

On Logger Only by default!

SELECT MessageId, CentralControllerFileTime, Side,SourceSystemName,Category,ProcName,Severity,MessageString FROM t_Event
WHERE Severity IN ('Warning','Error','Informational')
ORDER BY CentralControllerFileTime DESC
  • vendors/cisco/uc/icm/sql.1559817685.txt.gz
  • Last modified: 2019/06/06 11:41
  • by gerardorourke