Find UCCE agents and their assigned attributes and level

Maybe I should have checked CUIC to see if there was something, but I don’t remember something like this available. All I wanted was to see all my agents their attributes and levels.

SELECT [lab_awdb].[dbo].[Agent].EnterpriseName
      ,[lab_awdb].[dbo].[Attribute].EnterpriseName
      ,[AttributeValue]
  FROM [lab_awdb].[dbo].[Agent_Attribute]
JOIN [lab_awdb].[dbo].[Agent] ON [lab_awdb].[dbo].[Agent_Attribute].[SkillTargetID] = [lab_awdb].[dbo].[Agent].[SkillTargetID]
JOIN [lab_awdb].[dbo].[Attribute] ON [lab_awdb].[dbo].[Attribute].AttributeID = lab_awdb.dbo.Agent_Attribute.AttributeID

~david

Determine Busy Hour From UCCE TCD

The busy call hour is a valuable piece of information in order to forecast how many phone lines you’re going to need as well as how many agents, however getting this information can be difficult and then ensuring that it’s accurate is a whole other matter.  Recently I ran into an issue and I wanted to get the busy hour for myself.  I don’t have any access to the provider records, so all I have is the TCD table… when all you have is a hammer the whole world looks like a nail.

First, I turned to Google, which pointed me to how to get the calls per second.  This is good information as you would imagine that the highest CPS would fall during the busiest hour.  The query mentioned is:

   1:  SELECT Time=CONVERT(char,DateTime,108), CPS=CONVERT(decimal(5,2), RouteCallDetailTo5/300.0)
   2:  FROM t_Logger_Meters
   3:  WHERE DateTime BETWEEN '01/01/2013 00:05' AND '01/02/2013 23:59'
   4:  ORDER BY Time

Second, I opened up SQL Studio and began running some queries and came up with the following:

   1:  SELECT DATEPART(HOUR, DateTime) AS [Hour of Day], COUNT(distinct RouterCallKey) 
   2:  FROM t_Termination_Call_Detail
   3:  WHERE DateTime > '01/01/2013 06:00' and DateTime < '01/01/2013 20:00'
   4:  GROUP BY DATEPART(HOUR, DateTime)
   5:  ORDER BY DATEPART(HOUR, DateTime)
 
This query assumes that every new call will receive its own RCK, so we can use this as our counter.  Next, the query breaks up the select statement by hour.  Finally, put the output in Excel and make a pretty graph.  Some caveats, this is very generic and over counts.  It doesn’t care about transfers, outbound calls, etc.  Next, the query doesn’t look at the routing client, so if you have a lot of UCM generated calls this might not help you very much.
~david

Faster Way to Download Cisco Finesse Logs Using Wget

You should check out my other post on dealing with large Finesse logs.

If you’ve had the ‘pleasure’ of troubleshooting Cisco’s newest desktop offering Finesse you know that getting logs from it is a bit of a pain.  While sitting on a TAC call I came up with this approach which has made the process a bit easier.  This solution is not perfect, but it’s better than having to click, right click, download as…

  1. First ensure you have access to the Finesse logs directory /finesse/logs/">/finesse/logs/">/finesse/logs/">http://<finessehost>/finesse/logs/, you’ll need your administrator username and password.
  2. Once you can confirm you can login, download GNU Wget for you operating system.
  3. Open a command prompt and run the following command:

wget –-http-user=<user> –-http-password=<password> –r –np http://<finessehost>/finesse/logs/ \

This command will download every log file, it might take a while depending on your setup, but ultimately this first download is the most painful and afterwards you can use a slightly different command to just download the newest logs.

wget –-http-user=<user> –-http-password=<password> –r –np –N http://<finessehost>/finesse/logs/ \

This command will only download the newest logs which you can then quickly grab.  If you want to be a badass you could then come up with a way to automatically only zip the new ones.

Hope this helps.

~david

Cisco CVP 7.x No Session Error

Ran into this small issue while trying to release a new CVP application for one of my customers.  Everything was working on the test CVP server, but when it was time to put it on the production CVP servers I received the following error:

24769204: 10.10.90.115: Jul 19 2012 04:51:33.406 -0500: %CVP_7_0_IVR-3-CALL_ERROR: CALLGUID=9EA62E12100001383796F8800A0A5A73 DNIS=8111111111122013 CVP VXML Server encountered a No Session error – URL: http://CVP03:7000/CVP/en-us/../Server?DNIS=18775555555&InsuranceOpen=0&CallType=InsuranceCall&ANI=+1555555555

&PrimaryLocation=Birm&callid=9EA62E12100001383796F8800A0A5A73&

application=Insurance&RoutingClient=CVP03 (Client: 10.10.90.123) [id:3023]
24769205: 10.10.90.115: Jul 19 2012 04:51:33.406 -0500: %CVP_7_0_IVR-3-CALL_ERROR: RunScript Error from 10.10.90.123 [CVP_NO_SESSION_ERROR(44)] CALLGUID: 9EA62E12100001383796F8800A0A5A73 DNIS=8111111111122013 {VRUScriptName: ‘GS,Server,V’ ConfigParam: ”} [id:3023]

Now, this stumped me for a bit as it made not sense as to why this wouldn’t work as all the servers are exactly the same.  After a bit more digging I noticed that the application wasn’t deployed in CVP03.  Then I noticed that the application wasn’t deployed on any of the CVP servers.  Back in business.

When you don’t write CVP applications enough you forget the little things.

~david

Cisco UCCE Outbound Option Find Query Rule for Dialed Record

Here’s a simple query using an inner join to find out which query rule added a certain record to the dialer.  This is pretty useful when you’re trying to figure out why a record was dialed which went to the wrong skill group.  The only challenge here is joining two different tables in two different databases, in this case the HDS and AW databases.

 

SELECT [DateTime]
      ,[CustomerTimeZone] AS CustTimeZone
      ,[CampaignID]
      ,[CallResult]
      ,[CallStatusZone1]
      ,[CallStatusZone2]
      ,[asp_awdb].[dbo].[t_Query_Rule].QueryRuleName
      ,[DialingListID]
      ,[Phone]
      ,[SkillGroupSkillTargetID] AS SGID
      ,[AgentPeripheralNumber] AS AgentExt
      ,[PeripheralCallKey]
      ,[CallDuration]
      ,[AccountNumber]
      ,[FirstName]
      ,[LastName]
      ,[CallbackPhone]
      ,[CallbackDateTime]
      ,[DialingMode]
      ,[DialerID]
      ,[ImportRuleDateTime]
  FROM [dam_hds].[dbo].[t_Dialer_Detail]
INNER JOIN [dam_awdb].[dbo].[t_Query_Rule] on [dam_hds].[dbo].[t_Dialer_Detail].QueryRuleID = [dam_awdb].[dbo].[t_Query_Rule].QueryRuleID
WHERE DateTime > ’10/01/2010′
ORDER BY DateTime DESC

Presto!

~david