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

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

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

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

mRemote and Cisco’s AnyConnect Client Issues

I love mRemote.  Makes keeping track of all the different connections (RDP, VNC, SSH, etc.) a breeze.  However, I’ve recently noticed that after I installed Cisco’s AnyConnect client I could no longer open my existing connections or create new connections.  Upon startup I would receive the following window:

mRemoteSecurityPassword

What I ended up having to do is remove AnyConnect and then disable FIPS encryption following this link http://support.microsoft.com/kb/811770.

WindowsPolicy

Works like a charm now.  More than likely you don’t have to remove AnyConnect, however I don’t use it that much and rather just remove it all together.

~david

PS: The mRemote project is dead at version 1.5, there’s a fork out there called mRemoteNG which I’m trying out check it out here http://www.mremoteng.org/.

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

CTIOS Error 10125

PeripheralErrorCode:10125 AgentID:1000 UniqueObjectID:agent.5000.1000
     MessageID:eControlFailureConf MessageType:eSetAgentStateRequest
     ErrorMessage:IPCC Error [10125]You have attempted to log into an invalid
     instrument.  If you think it is correct there could be a configuration error.
      Otherwise, check the number and try again. FilterTarget:agent.5000.1000

I recently ran into this error and for the life of me could not figure out what was wrong.  So, the first thing I did was make sure it was happening on more than one PC, check.  Next, checked if it was happening with more than one phone, check.  Next, check if it happens with both PGs… this is where I noticed that I could login to one PG, but not to the other.  Which then lead me to check the configuration of both PGs and found that the PG configuration for the PG where the login was failing had an agent extension of 7 digits instead of 10 digits.  Thus why CTIOS was rejecting my login to a phone with an extension with 10 digits.

Presto!

~david

Cisco ICM Admin Script Will Not Run

I ran into an issue yesterday which I’m still not 100% sure as to why it happened, but I at least was able to clear it  up.  I created an administrator script which did run even though it was scheduled to run every 30 seconds.  The script even when down to a start and end node only and still no response.  So, when making changes in ICM the first place to look at is the router.  Did a quick “cdlog ra /last /o” and the ra.log showed the following:

   1:  21:02:40 ra-rtr Trace: Undefined symbol: Global.userMy_UserVar. 
   2:  21:02:40 ra-rtr Add of ScriptData warning. 
Unable complete script load 7300: Undefined symbol: Global.userMy_UserVar. 
   3:  21:02:40 ra-rtr All configuration operations complete. 

So, if nothing else, this lets me know that there is something in my config which is not right and it’s possible that new changes might be queued up and that’s why my admin script is not running.  I went through all references of my user variable and removed them.  I then deleted the user variable and created a new one with the same name and put it back in every script.  Looked at the router log again and it looked clean.  After that, the admin script ran fine.

 

~david

David get’s a silver badge

I try to stay involved with Cisco’s forum as to try to give back to the community a bit as well as trying to get some knowledge from the masses.  While I’m not a know it all it’s nice to have a little bit of recognition.  Last week i went from Bronze to Silver, which based on points others award you for the answers you provide. :-)

BadgesI have a long way to go before getting those 25k points… wonder if I could turn them in for cool Cisco gear?

~david

More Fun with ICM Script Editor

I really debated calling this, “How ICM Script Editor Allows You To See The Forest Through The Trees”, but I try to make titles which are relevant to my post.  Regardless, here is my latest fun encounter with Script Editor.

I was working on a script, which all of a sudden didn’t work as it originally did.  I checked the ToD routing logic, it was right.  I checked the admin scripts, they were right.  I checked every single node leading up to that step and they were all right.  I had two co-workers check it for me and they could not find the error either.

This is a small example of what I was looking at:

Script Editor Wrong

Now, in the above example it should be really apparent what is wrong, if you haven’t caught it, I’ll let you see what a correct script would look like:

Script Editor Right

Yep, no connection to the If node.  Now, you’re probably thinking, what is this guys thinking, that’s so easy to spot.  You’re right it is easy to spot when you have 4 nodes, but when you’re script spans multiple pages and has hundreds of nodes, it’s a little harder and honestly after starting at a script like this for weeks

Lost

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

you become crossed-eyed.

~dmacias