Author / dmacias

by dmacias

El Amor en los Tiempos del Colera

Thought I had posted this before, but could not find it.  Here’s my favorite quote from this great book.

Con ella aprendio Florentino Ariza lo que ya habia padecido muchas veces sin saberlo: que se puede estar enamorado de varias personas a la vez, y de todas con el mismo dolor, sin traicionar a ninguna. Solitario etre la muchedumbre del muelle, se habia dicho con un golpe de rabia: ‘El corazon tiene mas cuartos que un hotel de putas’. Estaba ba~nado en lagrimas por el dolor de los adioses. Sin embargo, no bien habia desaparecido el barco en la linea del horizonte, cuando ya el recuredo de Fermina Daza habia vuelto a ocupar su espacio total.

by dmacias

Cisco CUIC 8.x More Value List and Collections

I wanted to make a clarification to my earlier post about Value Lists and Collections.  This post will explain how to create a new Value List which will retrieve agents who have a specific value in their Agent.Description field.  Then Collections can be used to extract other more specific agents.  This could be used to retrieve agents from a single peripheral or agents named David, etc..  First, here’s the Value List:

Value List

And finally here’s a Collection:

Collection

Hope this makes sense and will help you create your own Value List and Collections.

~david

by dmacias

Cisco CUIC Creating Value Lists and Collections

This one should be pretty simple, but figure I would add it here for posterity’s sake.  I wanted to setup a real time Value List of all the call types, then break down my Value List into smaller Collections for each specific type of call type.

First step is to log in to CUIC and got to Value Lists and click on Create.

Untitled

The Value List Query:

SELECT Call_Type.CallTypeID AS ID, Call_Type.EnterpriseName AS VALUE FROM Call_Type (nolock) 
Order by Call_Type.EnterpriseName

The Collection Query:

SELECT Call_Type.CallTypeID AS ID, Call_Type.EnterpriseName AS VALUE FROM Call_Type (nolock) 
WHERE Call_Type.CustomerDefinitionID = (SELECT User_Group.CustomerDefinitionID FROM User_Group (nolock) 
WHERE UPPER(User_Group.UserGroupName) = UPPER(<COLLECTIONIDENTIFIER>)) OR (SELECT COUNT(User_Group.CustomerDefinitionID) 
FROM User_Group (nolock) WHERE UPPER(User_Group.UserGroupName) = UPPER(<COLLECTIONIDENTIFIER>)) = 0 ORDER BY Call_Type.EnterpriseName ASC

Validate your query and save.

Second, click on your Value List and click on Collections.

Untitled1

Notice the Collection Type of Wildcard which allows your create a wildcard which references all your call types.  This could be French* or CustomerService*.  Hit Save, Populate Values, and confirm the values to ensure the collection has the call types you’re looking for.

~dmacias

by dmacias

Cisco CUIC Real Time Agent Report with Not Ready Reason Code

I had my first exposure to CUIC last week and was glad to finally see this product and get to play with it.  For the most part it looks just like CUIS, except for the whole appliance model.  The UI is a little more streamlined, but what I remember from CUIS, it’s all there.  Now, one big change is that with CUIC, reports are cut down to a standard set (20 if I remember correctly) as opposed to the dozens of reports available through WebView.  This created some initial confusion as you try to familiarize yourself with what kind of data the new reports have.  Perhaps the best feature is the ability to remove columns from reports and just display the data you want to see.

One report which comes up all the time is an agent real time report showing the agent’s current not ready reason code.  I have an SQL query I use to get this data myself, but now that CUIC is supposed to make it easier to create your own reports, I wanted to give it a shot and create my very first custom report.

First, let’s start with the SQL query which joins the real time agent states as well as reason code tables.

   1:  SELECT [awdb].[dbo].[t_Agent_Real_Time].[SkillTargetID]
   2:          ,[awdb].[dbo].[t_Agent].[EnterpriseName]
   3:        ,[DateTime]
   4:        ,[SkillGroupSkillTargetID]
   5:        ,[ServiceSkillTargetID]
   6:        ,[AgentState]
   7:        ,'Agent State' =
   8:              CASE
   9:                    WHEN [AgentState] = 2 THEN 'Not Ready'
  10:                    WHEN [AgentState] = 3 THEN 'Ready'
  11:                    WHEN [AgentState] = 4 THEN 'Talking'
  12:                    WHEN [AgentState] = 5 THEN 'Wrap Up'
  13:              END
  14:        ,[awdb].[dbo].[t_Agent_Real_Time].[ReasonCode]
  15:        ,[awdb].[dbo].[t_Reason_Code].[ReasonText]
  16:        ,[Extension]
  17:        ,[DateTimeLastStateChange]
  18:    FROM [awdb].[dbo].[t_Agent_Real_Time] 
  19:    JOIN [awdb].[dbo].[t_Reason_Code] ON ([awdb].[dbo].[t_Agent_Real_Time].[ReasonCode]=[awdb].[dbo].[t_Reason_Code].[ReasonCode])
  20:    JOIN [awdb].[dbo].[t_Agent] ON [awdb].[dbo].[t_Agent_Real_Time].[SkillTargetID] = [awdb].[dbo].[t_Agent].[SkillTargetID]
  21:  GO

Now, don’t judge me on a) the poor SQL writing skills or b) my affinity to spacing.  It’s important to understand what the above query is doing to ensure you know where your reason codes are coming from.

Second, you will need to create a new report definition based on the stock Agent Real Time report definition.  You will need to extract the SQL of the report query shown below.

SELECT Agent.EnterpriseName, full_name=(Person.LastName + ‘, ‘ + Person.FirstName) , Skill_Group.EnterpriseName as SkillName, Agent.SkillTargetID, Agent_Real_Time.DateTime, Datepart(yy, Agent_Real_Time.DateTime) as Year, Datepart(mm, Agent_Real_Time.DateTime) as Month, Datepart(ww, Agent_Real_Time.DateTime) as Week, Datepart(dy, Agent_Real_Time.DateTime) as DOY, Datepart(dw, Agent_Real_Time.DateTime) as DOW, CONVERT(char(10),Agent_Real_Time.DateTime,101) as DATE, Agent_Real_Time.SkillGroupSkillTargetID, ServiceName = Service.EnterpriseName, Media = Media_Routing_Domain.EnterpriseName, 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’ WHEN 10 THEN ‘Hold’ WHEN 11 THEN ‘Active’ WHEN 12 THEN ‘Paused’ WHEN 13 THEN ‘Interrupted’ WHEN 14 THEN ‘Not Active’ ELSE CONVERT(VARCHAR, Agent_Real_Time.AgentState) END AgentState, CASE Agent_Real_Time.PhoneType When 0 THEN ‘Not Mobile’ WHEN 1 THEN ‘Call By Call’ WHEN 2 THEN ‘Nailed Connection’ Else ‘Not Applicable’ END phonetypetext, remotephonenumber = Agent_Real_Time.RemotePhoneNumber, ReasonCode = CASE WHEN Agent_Real_Time.ReasonCode = 0 THEN ‘NONE’ ELSE CONVERT(VARCHAR, Agent_Real_Time.ReasonCode) END , Agent_Real_Time.Extension, Agent_Real_Time.DateTimeLastStateChange, Agent_Real_Time.DateTimeLogin, CASE WHEN RequestedSupervisorAssist = 1 THEN ‘Yes’ ELSE ‘No’ END RequestedSupervisorAssist, CASE Agent_Real_Time.Destination WHEN 1 THEN ‘ACD’ WHEN 2 THEN ‘Direct’ WHEN 3 THEN ‘Auto Out’ WHEN 4 THEN ‘Reserve’ WHEN 5 THEN ‘Preview’ ELSE ‘Not Applicable’ END Destination, CASE Agent_Real_Time.Direction WHEN 1 THEN ‘In’ WHEN 2 THEN ‘Out’ WHEN 3 THEN ‘Other’ ELSE ‘Not Applicable’ END Direction, CASE WHEN Agent_Real_Time.OnHold = 1 THEN ‘Yes’ ELSE ‘No’ END OnHold, Agent_Real_Time.NetworkTargetID, Agent_Real_Time.AgentStatus, Agent_Real_Time.CustomerPhoneNumber, Agent_Real_Time.CustomerAccountNumber, Agent_Real_Time.CampaignID, Agent_Real_Time.QueryRuleID, Duration = DATEDIFF(ss, Agent_Real_Time.DateTimeLastStateChange, CASE WHEN (DATEDIFF(ss, Agent_Real_Time.DateTimeLastStateChange, (SELECT NowTime from Controller_Time (nolock))) <= 0 ) THEN Agent_Real_Time.DateTimeLastStateChange ELSE(SELECT NowTime FROM Controller_Time (nolock)) END), CASE WHEN Agent_Real_Time.Routable = 1 THEN ‘Yes’ ELSE ‘No’ END RoutableText,Agent_Real_Time.DateTimeLastModeChange, Agent_Real_Time.CallInProgress, Agent_Real_Time.MaxTasks, CASE WHEN Agent_Real_Time.AvailableInMRD = 0 THEN ‘No’ WHEN Agent_Real_Time.AvailableInMRD = 1 THEN ‘Yes_ICM’ WHEN Agent_Real_Time.AvailableInMRD = 2 THEN ‘Yes_APP’ ELSE ‘No’ END AvailInMRDText, Agent_Real_Time.DateTimeTaskLevelChange, Agent_Real_Time.RouterCallsQueueNow, Agent_Real_Time.RouterLongestCallQ FROM Agent (nolock), Person (nolock), Media_Routing_Domain (nolock), Agent_Real_Time (nolock) LEFT JOIN Service (nolock) ON Agent_Real_Time.ServiceSkillTargetID = Service.SkillTargetID, Skill_Group WHERE ( Agent_Real_Time.SkillGroupSkillTargetID = Skill_Group.SkillTargetID) and Agent.PersonID=Person.PersonID and Media_Routing_Domain.MRDomainID = Agent_Real_Time.MRDomainID and ( Agent.SkillTargetID = Agent_Real_Time.SkillTargetID ) UNION SELECT Agent.EnterpriseName, full_name=(Person.LastName + ‘, ‘ + Person.FirstName) , convert(varchar,’Not Applicable’), Agent_Real_Time.SkillTargetID, Agent_Real_Time.DateTime, Datepart(yy, Agent_Real_Time.DateTime) as Year, Datepart(mm, Agent_Real_Time.DateTime) as Month, Datepart(ww, Agent_Real_Time.DateTime) as Week, Datepart(dy, Agent_Real_Time.DateTime) as DOY, Datepart(dw, Agent_Real_Time.DateTime) as DOW, CONVERT(char(10),Agent_Real_Time.DateTime,101) as DATE, Agent_Real_Time.SkillGroupSkillTargetID, ServiceName = Service.EnterpriseName, Media=Media_Routing_Domain.EnterpriseName, 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’ WHEN 10 THEN ‘Hold’ WHEN 11 THEN ‘Active’ WHEN 12 THEN ‘Paused’ WHEN 13 THEN ‘Interrupted’ WHEN 14 THEN ‘Not Active’ ELSE CONVERT(VARCHAR, Agent_Real_Time.AgentState) END AgentState, CASE Agent_Real_Time.PhoneType When 0 THEN ‘Not Mobile’WHEN 1 THEN ‘Call By Call’ WHEN 2 THEN ‘Nailed Connection’ Else ‘Not Applicable’ END phonetypetext, remotephonenumber = Agent_Real_Time.RemotePhoneNumber, ReasonCode = CASE WHEN Agent_Real_Time.ReasonCode = 0 THEN ‘NONE’ ELSE CONVERT(VARCHAR, Agent_Real_Time.ReasonCode) END , Agent_Real_Time.Extension, Agent_Real_Time.DateTimeLastStateChange, Agent_Real_Time.DateTimeLogin, CASE WHEN RequestedSupervisorAssist = 1 THEN ‘Yes’ ELSE ‘No’ END RequestedSupervisorAssist, CASE Agent_Real_Time.Destination WHEN 1 THEN ‘ACD’ WHEN 2 THEN ‘Direct’ WHEN 3 THEN ‘Auto Out’ WHEN 4 THEN ‘Reserve’ WHEN 5 THEN ‘Preview’ ELSE ‘Not Applicable’ END Destination, CASE Agent_Real_Time.Direction WHEN 1 THEN ‘In’ WHEN 2 THEN ‘Out’ WHEN 3 THEN ‘Other’ ELSE ‘Not Applicable’ END Direction, CASE WHEN Agent_Real_Time.OnHold = 1 THEN ‘Yes’ ELSE ‘No’ END OnHold, Agent_Real_Time.NetworkTargetID, Agent_Real_Time.AgentStatus, Agent_Real_Time.CustomerPhoneNumber, Agent_Real_Time.CustomerAccountNumber, Agent_Real_Time.CampaignID, Agent_Real_Time.QueryRuleID, Duration=DATEDIFF(ss, Agent_Real_Time.DateTimeLastStateChange, CASE WHEN (DATEDIFF(ss, Agent_Real_Time.DateTimeLastStateChange, (SELECT NowTime from Controller_Time (nolock))) <= 0 ) THEN Agent_Real_Time.DateTimeLastStateChange ELSE (SELECT NowTime FROM Controller_Time (nolock)) END), CASE WHEN Agent_Real_Time.Routable = 1 THEN ‘Yes’ ELSE ‘No’ END RoutableText , Agent_Real_Time.DateTimeLastModeChange, Agent_Real_Time.CallInProgress, Agent_Real_Time.MaxTasks, CASE WHEN Agent_Real_Time.AvailableInMRD = 0 THEN ‘No’ WHEN Agent_Real_Time.AvailableInMRD = 1 THEN ‘Yes_ICM’ WHEN Agent_Real_Time.AvailableInMRD = 2 THEN ‘Yes_APP’ ELSE ‘No’ END AvailInMRDText, Agent_Real_Time.DateTimeTaskLevelChange, Agent_Real_Time.RouterCallsQueueNow, Agent_Real_Time.RouterLongestCallQ FROM Agent (nolock), Person (nolock), Media_Routing_Domain (nolock), Agent_Real_Time (nolock) LEFT JOIN Service (nolock) ON Agent_Real_Time.ServiceSkillTargetID = Service.SkillTargetID WHERE ((Agent_Real_Time.SkillGroupSkillTargetID = 0) OR (Agent_Real_Time.SkillGroupSkillTargetID IS NULL)) and Agent.PersonID=Person.PersonID and Media_Routing_Domain.MRDomainID = Agent_Real_Time.MRDomainID and ( Agent.SkillTargetID = Agent_Real_Time.SkillTargetID )

Third, modify the SQL query.  Also, it helps a lot to actually arrange the above query and break it down section by section.

 

   1:   
   2:  SELECT 
   3:                  Agent.EnterpriseName, 
   4:                  full_name=(Person.LastName + ', ' + Person.FirstName), 
   5:                  Skill_Group.EnterpriseName as SkillName, 
   6:                  Agent.SkillTargetID, 
   7:                  Agent_Real_Time.DateTime, 
   8:                  Datepart(yy, Agent_Real_Time.DateTime) as Year, 
   9:                  Datepart(mm, Agent_Real_Time.DateTime) as Month, 
  10:                  Datepart(ww, Agent_Real_Time.DateTime) as Week, 
  11:                  Datepart(dy, Agent_Real_Time.DateTime) as DOY, 
  12:                  Datepart(dw, Agent_Real_Time.DateTime) as DOW, 
  13:                  CONVERT(char(10),Agent_Real_Time.DateTime,101) as DATE, Agent_Real_Time.SkillGroupSkillTargetID, 
  14:                  ServiceName = Service.EnterpriseName, 
  15:                  Media = Media_Routing_Domain.EnterpriseName, 
  16:                  CASE Agent_Real_Time.AgentState  
  17:                                  WHEN 0 THEN 'Logged Out' 
  18:                                  WHEN 1 THEN 'Logged On' 
  19:                                  WHEN 2 THEN 'Not Ready' 
  20:                                  WHEN 3 THEN 'Ready' 
  21:                                  WHEN 4 THEN 'Talking' 
  22:                                  WHEN 5 THEN 'Work Not Ready' 
  23:                                  WHEN 6 THEN 'Work Ready' 
  24:                                  WHEN 7 THEN 'Busy Other' 
  25:                                  WHEN 8 THEN 'Reserved'  
  26:                                  WHEN 9 THEN 'Unknown' 
  27:                                  WHEN 10 THEN 'Hold' 
  28:                                  WHEN 11 THEN 'Active'  
  29:                                  WHEN 12 THEN 'Paused' 
  30:                                  WHEN 13 THEN 'Interrupted' 
  31:                                  WHEN 14 THEN 'Not Active' 
  32:                                  ELSE CONVERT(VARCHAR, Agent_Real_Time.AgentState) 
  33:                  END AgentState, 
  34:                  CASE Agent_Real_Time.PhoneType 
  35:                                  When 0 THEN 'Not Mobile' 
  36:                                  WHEN 1 THEN 'Call By Call' 
  37:                                  WHEN 2 THEN 'Nailed Connection' 
  38:                                  Else 'Not Applicable' 
  39:                  END phonetypetext, 
  40:                  remotephonenumber = Agent_Real_Time.RemotePhoneNumber, 
  41:                  ReasonCode = CASE 
  42:                                  WHEN Agent_Real_Time.ReasonCode = 0 
  43:                                  THEN 'NONE' 
  44:                                  ELSE CONVERT(VARCHAR, Agent_Real_Time.ReasonCode) 
  45:                                  END , 
  46:                  Reason_Code.ReasonText,
  47:                  Agent_Real_Time.Extension, 
  48:                  Agent_Real_Time.DateTimeLastStateChange, 
  49:                  Agent_Real_Time.DateTimeLogin, 
  50:                  CASE 
  51:                                  WHEN RequestedSupervisorAssist = 1 THEN 'Yes' ELSE 'No' 
  52:                  END RequestedSupervisorAssist, 
  53:                  CASE Agent_Real_Time.Destination 
  54:                                  WHEN 1 THEN 'ACD' 
  55:                                  WHEN 2 THEN 'Direct' 
  56:                                  WHEN 3 THEN 'Auto Out' 
  57:                                  WHEN 4 THEN 'Reserve' 
  58:                                  WHEN 5 THEN 'Preview' 
  59:                                  ELSE 'Not Applicable' 
  60:                  END Destination, 
  61:                  CASE  Agent_Real_Time.Direction 
  62:                                  WHEN 1 THEN 'In' 
  63:                                  WHEN 2 THEN 'Out' 
  64:                                  WHEN 3 THEN 'Other' 
  65:                                  ELSE 'Not Applicable' 
  66:                  END Direction, 
  67:                  CASE 
  68:                                  WHEN Agent_Real_Time.OnHold = 1 THEN 'Yes' 
  69:                                  ELSE 'No' 
  70:                  END OnHold, 
  71:                  Agent_Real_Time.NetworkTargetID, 
  72:                  Agent_Real_Time.AgentStatus, 
  73:                  Agent_Real_Time.CustomerPhoneNumber, 
  74:                  Agent_Real_Time.CustomerAccountNumber, 
  75:                  Agent_Real_Time.CampaignID, 
  76:                  Agent_Real_Time.QueryRuleID, 
  77:                  Duration = DATEDIFF(ss, Agent_Real_Time.DateTimeLastStateChange, 
  78:                  CASE 
  79:                                  WHEN (DATEDIFF(ss, Agent_Real_Time.DateTimeLastStateChange, (SELECT NowTime from Controller_Time (nolock))) <= 0 ) 
  80:                                  THEN Agent_Real_Time.DateTimeLastStateChange 
  81:                                  ELSE(SELECT NowTime FROM Controller_Time (nolock)) 
  82:                  END), 
  83:                  CASE 
  84:                                  WHEN Agent_Real_Time.Routable = 1 
  85:                                  THEN 'Yes' ELSE 'No' 
  86:                  END RoutableText, 
  87:                  Agent_Real_Time.DateTimeLastModeChange, 
  88:                  Agent_Real_Time.CallInProgress, 
  89:                  Agent_Real_Time.MaxTasks, 
  90:                  CASE 
  91:                                  WHEN Agent_Real_Time.AvailableInMRD = 0 THEN 'No' 
  92:                                  WHEN Agent_Real_Time.AvailableInMRD = 1 THEN 'Yes_ICM' 
  93:                                  WHEN Agent_Real_Time.AvailableInMRD = 2 THEN 'Yes_APP' 
  94:                                  ELSE 'No' 
  95:                  END AvailInMRDText, 
  96:                  Agent_Real_Time.DateTimeTaskLevelChange, 
  97:                  Agent_Real_Time.RouterCallsQueueNow, 
  98:                  Agent_Real_Time.RouterLongestCallQ 
  99:  FROM 
 100:                  Agent (nolock), 
 101:                  Person (nolock), 
 102:                  Media_Routing_Domain (nolock), 
 103:                  Agent_Real_Time (nolock) LEFT JOIN Service (nolock) ON Agent_Real_Time.ServiceSkillTargetID = Service.SkillTargetID 
 104:                  JOIN Reason_Code ON Agent_Real_Time.ReasonCode = Reason_Code.ReasonCode,
 105:                  Skill_Group
 106:  WHERE ( Agent_Real_Time.SkillGroupSkillTargetID = Skill_Group.SkillTargetID) 
 107:                  and Agent.PersonID=Person.PersonID 
 108:                  and Media_Routing_Domain.MRDomainID = Agent_Real_Time.MRDomainID 
 109:                  and ( Agent.SkillTargetID = Agent_Real_Time.SkillTargetID ) 
 110:  UNION SELECT 
 111:                  Agent.EnterpriseName, 
 112:                  full_name=(Person.LastName + ', ' + Person.FirstName) , 
 113:                  convert(varchar,'Not Applicable'), 
 114:                  Agent_Real_Time.SkillTargetID, 
 115:                  Agent_Real_Time.DateTime, 
 116:                  Datepart(yy, Agent_Real_Time.DateTime) as Year, 
 117:                  Datepart(mm, Agent_Real_Time.DateTime) as Month, 
 118:                  Datepart(ww, Agent_Real_Time.DateTime) as Week, 
 119:                  Datepart(dy, Agent_Real_Time.DateTime) as DOY, 
 120:                  Datepart(dw, Agent_Real_Time.DateTime) as DOW, 
 121:                  CONVERT(char(10),Agent_Real_Time.DateTime,101) as DATE, 
 122:                  Agent_Real_Time.SkillGroupSkillTargetID, 
 123:                  ServiceName = Service.EnterpriseName, 
 124:                  Media=Media_Routing_Domain.EnterpriseName, 
 125:                  CASE 
 126:                                  Agent_Real_Time.AgentState 
 127:                                  WHEN 0 THEN 'Logged Out' 
 128:                                  WHEN 1 THEN 'Logged On' 
 129:                                  WHEN 2 THEN 'Not Ready' 
 130:                                  WHEN 3 THEN 'Ready' 
 131:                                  WHEN 4 THEN 'Talking' 
 132:                                  WHEN 5 THEN 'Work Not Ready' 
 133:                                  WHEN 6 THEN 'Work Ready' 
 134:                                  WHEN 7 THEN 'Busy Other' 
 135:                                  WHEN 8 THEN 'Reserved'  
 136:                                  WHEN 9 THEN 'Unknown' 
 137:                                  WHEN 10 THEN 'Hold' 
 138:                                  WHEN 11 THEN 'Active'  
 139:                                  WHEN 12 THEN 'Paused' 
 140:                                  WHEN 13 THEN 'Interrupted' 
 141:                                  WHEN 14 THEN 'Not Active' 
 142:                                  ELSE CONVERT(VARCHAR, Agent_Real_Time.AgentState) 
 143:                  END AgentState, 
 144:                  CASE 
 145:                                  Agent_Real_Time.PhoneType 
 146:                                  When 0 THEN 'Not Mobile'
 147:                                  WHEN 1 THEN 'Call By Call' 
 148:                                  WHEN 2 THEN 'Nailed Connection' 
 149:                                  Else 'Not Applicable' 
 150:                  END phonetypetext, 
 151:                  remotephonenumber = Agent_Real_Time.RemotePhoneNumber, 
 152:                  ReasonCode = 
 153:                  CASE 
 154:                                  WHEN Agent_Real_Time.ReasonCode = 0 THEN 'NONE' 
 155:                                  ELSE CONVERT(VARCHAR, Agent_Real_Time.ReasonCode) 
 156:                  END , 
 157:                  Reason_Code.ReasonText,
 158:                  Agent_Real_Time.Extension, 
 159:                  Agent_Real_Time.DateTimeLastStateChange, 
 160:                  Agent_Real_Time.DateTimeLogin, 
 161:                  CASE 
 162:                                  WHEN RequestedSupervisorAssist = 1 THEN 'Yes' 
 163:                                  ELSE 'No' 
 164:                  END RequestedSupervisorAssist, 
 165:                  CASE 
 166:                                  Agent_Real_Time.Destination 
 167:                                  WHEN 1 THEN 'ACD' 
 168:                                  WHEN 2 THEN 'Direct' 
 169:                                  WHEN 3 THEN 'Auto Out' 
 170:                                  WHEN 4 THEN 'Reserve' 
 171:                                  WHEN 5 THEN 'Preview' 
 172:                                  ELSE 'Not Applicable' 
 173:                  END Destination, 
 174:                  CASE  
 175:                                  Agent_Real_Time.Direction 
 176:                                  WHEN 1 THEN 'In' 
 177:                                  WHEN 2 THEN 'Out' 
 178:                                  WHEN 3 THEN 'Other' 
 179:                                  ELSE 'Not Applicable' 
 180:                  END Direction, 
 181:                  CASE 
 182:                                  WHEN Agent_Real_Time.OnHold = 1 THEN 'Yes' 
 183:                                  ELSE 'No' 
 184:                  END OnHold, 
 185:                  Agent_Real_Time.NetworkTargetID, 
 186:                  Agent_Real_Time.AgentStatus, 
 187:                  Agent_Real_Time.CustomerPhoneNumber, 
 188:                  Agent_Real_Time.CustomerAccountNumber, 
 189:                  Agent_Real_Time.CampaignID, 
 190:                  Agent_Real_Time.QueryRuleID, 
 191:                  Duration=DATEDIFF(ss, Agent_Real_Time.DateTimeLastStateChange, 
 192:                  CASE 
 193:                                  WHEN (DATEDIFF(ss, Agent_Real_Time.DateTimeLastStateChange, 
 194:                                  (SELECT NowTime from Controller_Time (nolock)))  <= 0 ) 
 195:                                  THEN Agent_Real_Time.DateTimeLastStateChange    
 196:                                  ELSE (SELECT NowTime FROM Controller_Time (nolock)) 
 197:                  END), 
 198:                  CASE 
 199:                                  WHEN Agent_Real_Time.Routable = 1 THEN 'Yes' 
 200:                                  ELSE 'No' 
 201:                  END RoutableText , 
 202:                  Agent_Real_Time.DateTimeLastModeChange, 
 203:                  Agent_Real_Time.CallInProgress, 
 204:                  Agent_Real_Time.MaxTasks, 
 205:                  CASE 
 206:                                  WHEN Agent_Real_Time.AvailableInMRD = 0 THEN 'No' 
 207:                                  WHEN Agent_Real_Time.AvailableInMRD = 1 THEN 'Yes_ICM' 
 208:                                  WHEN Agent_Real_Time.AvailableInMRD = 2 THEN 'Yes_APP' 
 209:                                  ELSE 'No' 
 210:                  END AvailInMRDText, 
 211:                  Agent_Real_Time.DateTimeTaskLevelChange, 
 212:                  Agent_Real_Time.RouterCallsQueueNow, 
 213:                  Agent_Real_Time.RouterLongestCallQ 
 214:                  FROM   
 215:                                  Agent (nolock), 
 216:                                  Person (nolock), 
 217:                                  Media_Routing_Domain (nolock), 
 218:                                  Agent_Real_Time (nolock) LEFT JOIN Service (nolock) ON Agent_Real_Time.ServiceSkillTargetID = Service.SkillTargetID
 219:                                  JOIN Reason_Code ON Agent_Real_Time.ReasonCode = Reason_Code.ReasonCode
 220:                  WHERE 
 221:                                  ((Agent_Real_Time.SkillGroupSkillTargetID = 0) OR (Agent_Real_Time.SkillGroupSkillTargetID IS NULL)) 
 222:                                  and Agent.PersonID=Person.PersonID
 223:                                  and Media_Routing_Domain.MRDomainID = Agent_Real_Time.MRDomainID 
 224:                                  and ( Agent.SkillTargetID = Agent_Real_Time.SkillTargetID )

Note line 46 and 104 where the join is made to retrieve the reason text.  Also note that you need to duplicate these lines in the Union Select statement.

Finally, create a new report definition, create a new report based off the definition, and test.  This posts assumes that you know at least the very basics around CUIC.  It doesn’t hurt to know some SQL, personally I know enough to be dangerous, but touch it so seldom that I’m no expert.

Hope this helps others out there as this is a pretty common report and a lot of customer ask for it.

~david

by dmacias

How many Cisco CTI OS agents are current logged in?

I’ve seen this question come up a few times in the Cisco Support Community.  Figured I would post something about it here.  The question is to find out how many agents are currently logged to CTI OS.  The following will give you the current number of logged in agents and if you leave it running for a long period of time it will tell you how many agents logged in by average, minimum, and maximum number of logged in agents.

– All Programs > Administrator Tools > Performance
– Click on New Counter Set
– Right click on the counter screen and click on Add Counters…
– Select the following counter below

clip_image002[4]

– You will see the counter increase/decrease as agents log in and out.

clip_image002

~dmacias

by dmacias

New Years Resolutions and Data Visualization

At the end of last year (December 2010) I set out to change a few things.  The biggest one being to try and burn 10,000 calories a month through exercise.  The first few months I kept track of my calorie expenditure base on a heart monitor, when my heart monitor met an untimely death I switched to an Android app call CardioTrainer. I highly recommend this app for anyone else who wants to keep track of their workouts.

While I was only able to accomplish this 6 times, I have burned over 100,000 calories so far and lost about 10 pounds.  I will continue working towards 10,000 a month in 2012.  So, now I have a year of data and I’ve been looking to play around with Google’s Chart Tools and figure this would be the combination.  Here’s my first stab at visualizing the data I’ve collected so far.

 

~david

by dmacias

David gets a gold star (Cisco Support Community)!

I like to contribute a lot to Cisco’s NetPro Forums, now called Cisco Support Community.  I’ve been a member since 2005 and love the community since everyone seems to help everyone out a lot.  For this particular reason I feel inclined to give back as much as possible.  That being said, when you post a reply posters can give you points for how good your response was or how much it helped them.  So, that’s one way to get some recognition if you’re actually being helpful.

That being said, there are different levels based on how many points you’ve been awarded.  Just recently I reached the gold start level by surpassing 750 points.  While I still have a long way to go to catch some of the top contributors who are in the 10s of thousands of points, it does feel nice to hit a small milestone.

image

~dmacias

by dmacias

Cisco CRM Adapter with Firefox

I will assume you have the server connected and working properly, this post focuses solely on getting the CRM adapter working with Firefox 3.x, 6.x.  The version I’m working with is Cisco_Unified_CRM_Connector_7.52, which allows you to install the server or client.  This is Cisco’s latest version and upon install it includes Salesforce.com Adapter 3.00.  Install the adapter, including the Firefox extension and perform the following steps:

  • Download the Demo Adapter 3.01.  This includes the Salesforce.com Adapter 3.01.
  • Run through setup and install it in a different directory.  Ensure you install the Firefox extension.  Cisco’s in C:\Program Files\Cisco\CRM Connector\Salesforce.com Adapter\ and Salesforce in C:\Program Files\Salesforce.com\Demo Adapter 3.01.
  • Go to the Demo Adapter 3.01 folder and register the SFDCFirefoxConnector.dll and SFDCFirefoxConnectorPS.dll.
    • You can register them by double clicking on the file and opening them with C:\Windows\System32\regsvr32.exe
  • Restart Firefox.

Firefox should now be working with your Salesforce.com instance.  Please note that you now have two adapters and you must be running Cisco’s version 3.00 in order for this to work.  I’m assuming someone at Cisco didn’t package the right DLL and or the right Firefox extension (which is included in the Demo Adapter 3.01 folder sfdcffextension.xpi).

~dmacias

by dmacias

First ever triathlon (or how to check your ego)

Trying to change it up a bit and take a break from pounding pavement for 26.2 miles, it was decided to give it a go at a triathlon.  Now, while I can run until the cows come home, I can’t swim very well.  Actually, I’m like a fish… out of water… in the water.  That’s right, I flounder about never really sinking, never really moving forward, and never really doing anything in particular.  Oh, there’s a stroke and a kick here and there, but not sure if they are ever synchronized in any way.  Needless to say, this was going to be awesome!

The event picked was the Findley Lake Triathlon, consisting of an easy 400 meter swim, followed by a brisk 14.3 mile bike ride, and topped off with a swift 5k trail run.  I own a pair of Speedos, a bike, and some shoes, I was set.

Race Day

It is customary to be nervous on the day of a big event and I’m one to just need some quiet before an event to really harness my inner D.  However, for this event there was no inner D, the night before I had slept about 2 hours.  As I tossed and turned pondering what would happen if I drowned, I made a decision; the only way I was going to get out of that lake was either hauled out by a life guard or via the finish line.  Prayers were requested.

There were a total of 74 participants (this number is crucial) for this event, with probably 50 men and 30 women.  The swim was to start at 8 AM with the men and around 5-10 minutes later with the women.  It was an in-water start, which I enjoyed, with all of us lined up ready to start about 20 yards from the lake shore.  As the gun goes off, I hold on to the starting line and wait for the stronger swimmers to go out.  My strategy being that I would try to make a not too shallow parabola around the half way point, this would cause me to swim a bit more, but would also allow me the most flexibility if I were to stray too far or too close from the course.

I began my swim feeling pretty good and confident this would be over sooner than expected.  I began with a good steady free style stroke just to try and get as far along the course as possible while I was still fresh.  In my mind I’m sure I’m a quarter of the way there and it’s time to take a bit of a break.  I look up and notice I’m barely 10 meters in… yes, in what felt like ages and a great stroke, I advanced a mere 10 meters.  I switch it up a bit and go with a breast stroke to get some good breaths.  I look back and about 5 meters behind me there’s another male doing, what seemed to be, the doggie paddle.  I looked over a few more times and he seemed to be dedicated to this stroke and seemed intend on going the whole way using this stroke, I was not going to be passed by the doggie paddler.

I get to the half way point and the doggie paddler has made a wide turn around the bend, but is pretty much neck and neck with me.  At this time the first two women swimmers catch up and even stopped for a second to figure out which way to go around me.  They were the strong swimmers and I was the flounderer, they were going to need to go around me and did.  At this point I look to my left and see the doggie paddler getting ahead of me and realized this was going worst than expected.  However, hope was not lost.  I had made it to the half way point and knew that if there was a current it should take me to the shore.  I also knew that if there was a current in a lake, it would take days for me to wash up, so it was up to me to get to the finish line before the day’s end.  I begin a combination of doggie paddle and back strokes to ensure I keep my head above water as much as possible.  I’m sucking wind like it’s going out of style and my chest feels like it weights a thousand pounds.  I try to ignore the multitude of women passing me by and try to focus on what’s ahead, solid ground.

I take a break to try and get my bearings and realize that I’m about 15 meters away, this is it.  I will not be hauled away by a life guard.  I began a mad back stroke dash.  I bumped the finish line with my head and try to reach for the bottom of the lake.  I’m about chest deep in water and I begin to walk my way out of the water.  The smart thing to have done is to swim until you’re only tight deep in water and run the rest of the way, but I was so exhausted and so eager to use my legs that I didn’t care about one more stroke.  At this point I don’t notice anyone else in the water ahead of me and don’t dare look behind me to see if I’m last.  My objective was ahead and that’s all that mattered.

I get out of the water and get hosed down to clean up some of the grime from the lake.  Put on some shorts over my speedos and slide on some shoes and walk out of the transition 1 area with the bike next to me.  At this point I’m thinking I will make up some of the lost ground on the bike and on the run.  As I get on the open road I spot the first other person ahead of me.  I can tell it’s a female biker and she doesn’t seem to be going too fast.  In my mind I think, “first victim, here we go.”  I never caught up to her.  Actually, I was passed by 3 other people and lost sight of my first “victim”.  About half way through the bike ride I catch up to a 13 year old boy on a non-road bike; given it his all.  I told him good job as I passed him and thought to myself that kid will beat me in a year or two.

I finish the bike portion and switch shoes and get to running.  Here’s my bread and butter.  I had hoped for a decently flat course and wasn’t sure what to expect on this trail run.  I should have known better.  The trail run consisted of muddy areas, some very steep valleys and climbs and a lot of roots.  It was slow moving, painful descents due to bad knees, and a couple of twisted ankles.  I saw two other runners and was pretty sure I would cross the finish line and everyone would be gone.

Results

 

——- 1/4M Swim ——-

T1

——- 16M Bike ——-

T2

——- 5K Run ——-

Time

Total

Place

 Name

Bib No

Age

Gender

Rnk

Time

Pace

Time

Rnk

Time

Rate

Time

Rnk

Time

Pace

Back

Time

59

 David Macias

55

31

M

73

17:47.3

71:08

2:05.4

61

1:04:31.9

14.9

1:20.2

33

32:10.7

10:23

43:47.0

1:57:55.7

Yes, that’s second to the last place in the swim, with a staggering pace of 71 minutes per mile.  I improved to 61st place in the bike stage and 33rd place in the run with an overall place of 59th.

Aftermath

I will do another triathlon.  I will do another one and not be second to the last in the swim.  I will also get a swim coach to improve my atrocious so-called-stroke.  It was an absolutely great experience.  It’s amazing the level of athleticism a lot of these participants display and while I might never get there competing along side makes me feel good.  It’s incredible how fit these people are, specially when you compare them to marathon runners.  It’s a whole different breed of people.

~dmacias