Example SQL queries (UCCE)

[cmannina]

Hi all,

Just received access to a replicated UCCE awdb. I've been tasked to do some reporting off the replicate db. I've browsed the schema handbook, but was hoping to get some example SQL queries to get me started. I'm currently looking for the following:

- Total incoming calls (both inside and outside the organization)

- Total outgoing calls (both inside and outside the organization)

- Length of each call

- Date/time of each call 

- Abandoned calls

- Queued 

- Talk time

- Time to answer

Thank you! Appreciate any help or insight. 

[bill.king1]

There are several posts like this on the group, like this one https://community.cisco.com/t5/contact-center/ucce-sql-queries/td-p/2828656
However, not sure what you mean by a replicated awdb, as most of what you're likely going to look for with historical reporting is in the hds table and not awdb.
All depends on what you're looking to do/how far back you want to go, but the awdb will be helpful as far as the configuration items (agents, call types, etc.).

[SubnetZero1]

https://www.ciscounitytools.com/CodeSamples/Unity/SQLSampleQueries/SQLQueryExamples.htm

https://www.cisco.com/c/en/us/support/docs/unified-communications/unity-connection/118299-technote-cuc-00.html

These two links should get your started. 

[SubnetZero1]

select 
d.DateTime 
,c.CampaignName
,qr.QueryRuleName
,d.CallbackDateTime
,d.CallResult
,d.CallDuration
,CASE
	WHEN d.CallResult = 0 THEN 'Not Dialed'
	WHEN d.CallResult = 2 THEN 'Error condition while dialing'
	WHEN d.CallResult = 3 THEN 'Number not in service'
	WHEN d.CallResult = 4 THEN 'No ringback'
	WHEN d.CallResult = 5 THEN 'Operator intercept'
	WHEN d.CallResult = 6 THEN 'No dial tone'
	WHEN d.CallResult = 7 THEN 'Invalid number'
	WHEN d.CallResult = 8 THEN 'No answer'
	WHEN d.CallResult = 9 THEN 'Busy'
	WHEN d.CallResult = 10 THEN 'Connected'
	WHEN d.CallResult = 11 THEN 'Fax'
	WHEN d.CallResult = 12 THEN 'Answering machine'
	WHEN d.CallResult = 13 THEN 'Network interruption'
	WHEN d.CallResult = 14 THEN 'Callback requested'
	WHEN d.CallResult = 16 THEN 'Dialer abandon'
	WHEN d.CallResult = 18 THEN 'Agent Skipped or Rejected Preview Call'
	WHEN d.CallResult = 19 THEN 'Agent Skipped or Rejected Preview Call'
	WHEN d.CallResult = 20 THEN 'Customer has been abandoned to an IVR'
	WHEN d.CallResult = 21 THEN 'Customer abandon'
	WHEN d.CallResult = 25 THEN 'Record flushed'
	WHEN d.CallResult = 26 THEN 'Do not call'
	WHEN d.CallResult = 27 THEN 'Ringing disconnect'
	WHEN d.CallResult = 28 THEN 'Dead air'
	WHEN d.CallResult = 29 THEN 'SIP Message not supported by VG'
	ELSE CAST(d.CallResult AS VARCHAR(2))
 END AS DialerResult
,d.Phone
,d.AccountNumber
,d.FutureUseVarchar1 AS 'WhichCUBE'
,d.WrapupData
,d.DialingListID
,d.*


from west_hds.dbo.t_Dialer_Detail d
left join west_awdb.dbo.Campaign c on d.CampaignID = c.CampaignID
left join west_awdb.dbo.Query_Rule qr on d.QueryRuleID = qr.QueryRuleID
--left join [west_hds].dbo.t_Termination_Call_Detail cd on d.RouterCallKey = cd.RouterCallKey and d.RouterCallKeyDay = cd.RouterCallKeyDay AND AgentSkillTargetID IS NOT NULL
--left join Agent a ON cd.AgentSkillTargetID = a.SkillTargetID
--left join Person p ON a.PersonID = p.PersonID

where d.DateTime >= '2022-9-06 11:36:00' AND d.DateTime < '2022-9-07 23:59:00'
--and QueryRuleName = 'REG'
--and p.LoginName is not NULL
--and CampaignName NOT IN 'NOBEL'
--and CampaignName LIKE '%REG%' 
--and QueryRuleName LIKE 'xxxxx'
and CallResult IN ('2')
--and AccountNumber IN ('xxxxxxx')
--and d.Phone LIKE '%99%'
--and cd.PrecisionQueueID is NULL
--and CallDisposition = 
--AND CallGUID Like '%xxxx%'
--AND d.FutureUseVarchar1 IN  ('xx.xx.xx.xx')
AND d.FutureUseVarchar1 IN  ('xx.xx.xx.xx' , 'xx.xx.xx.xx')


order by d.DateTime desc






--select top 100 * from west_hds.dbo.t_Dialer_Detail a ORDER BY  a.DateTime DESC

--select top 1000 * from west_awdb.dbo.Campaign a ORDER BY DateTimeStamp DESC

---select top 1000 * from west_awdb.dbo.Query_Rule a ORDER BY DateTimeStamp DESC