Add Connection Profiles to Cisco AnyConnect Secure Mobility Client

I enjoy the new VPN client, it’s small and fast, however I hated that you can’t save profiles in the drop down list like you could in the traditional VPN client.

VPNNoProfile

This has been bothering for a long time and kept finding conflicting information on if this was possible or not. Finally got it to work.

VPNProfiles

This is for version 3.1x and Windows 7 let me know if this works for your version and OS.

  • Create a preferences.xml file in C:\ProgramData\Cisco\Cisco AnyConnect Secure Mobility Client\Profile\
  • Use this format

<?xml version="1.0" encoding="UTF-8"?>

<AnyConnectProfile xmlns="http://schemas.xmlsoap.org/encoding/">
<ServerList>
     <HostEntry>
          <User>dclouduser</User>
          <SecondUser></SecondUser>
          <ClientCertificateThumbprint></ClientCertificateThumbprint>
          <ServerCertificateThumbprint></ServerCertificateThumbprint>
          <HostName>dCloud</HostName>
          <HostAddress>dcloud-rtp-anyconnect.cisco.com</HostAddress>
          <Domain></Domain>
          <Group>ssl_url</Group>
          <ProxyHost></ProxyHost>
          <ProxyPort></ProxyPort>
          <SDITokenType>none</SDITokenType>
          <ControllablePreferences>
          <LocalLanAccess>true</LocalLanAccess></ControllablePreferences>
     </HostEntry>

     <HostEntry>
          <User>dmacias</User>
          <SecondUser></SecondUser>
          <ClientCertificateThumbprint></ClientCertificateThumbprint>
          <ServerCertificateThumbprint></ServerCertificateThumbprint>
          <HostName>Speech-Soft</HostName>
          <HostAddress>vpn.dmacias.com</HostAddress>
          <Domain></Domain>
          <Group>ssl_url</Group>
          <ProxyHost></ProxyHost>
          <ProxyPort></ProxyPort>
          <SDITokenType>none</SDITokenType>
          <ControllablePreferences>
          <LocalLanAccess>true</LocalLanAccess></ControllablePreferences>
     </HostEntry>
</ServerList>

</AnyConnectProfile>

  • Save the file.
  • Restart the connectivity client.
  • Enjoy

~david

EDIT 01/18/2017: This also works with Cisco AnyConnect 4.x!

Find the total number of attributes and skillgroups in an agent team.

With the new 9.x release and the popularity of Finesse I’m seeing a lot more cases of latency/timeouts from the Finesse desktop.  A lot of these have been attributed to network issues, but I still feel that Finesse has been made way too sensitive to the network.  Makes me yearn for the good old days of CTIOS.  One thing which came up recently is to find out how many skillgroups and attributes/PQs an agent team has.  As the SRND alludes that having too many SG/PQs (>50) in a team will cause performance issues.  The following two queries will give you the total count of unique attributes and skillgroups in an agent team, all you need to do is know your agent team ID.

Attributes

SELECT count(distinct(awdb.dbo.t_Agent_Attribute.AttributeID)), COUNT(awdb.dbo.t_Agent_Attribute.AttributeID) FROM awdb.dbo.t_Agent_Attribute INNER JOIN t_Agent_Team_Member ON (t_Agent_Attribute.SkillTargetID = t_Agent_Team_Member.SkillTargetID)
WHERE t_Agent_Team_Member.AgentTeamID = ‘XYZ’

Skillgroups

SELECT count(distinct(awdb.dbo.t_Agent_Attribute.AttributeID)), COUNT(awdb.dbo.t_Agent_Attribute.AttributeID) FROM awdb.dbo.t_Agent_Attribute INNER JOIN t_Agent_Team_Member ON (t_Agent_Attribute.SkillTargetID = t_Agent_Team_Member.SkillTargetID)
WHERE t_Agent_Team_Member.AgentTeamID = ‘XYZ’

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

Download Huge Cisco Finesse Logs

You might have seen my previous post on how to use wget to download Finesse logs.  This works like a champ, however wget has an issue with large files (> 1GB), so I had to come up with a new solution as some of our logs were in the 2-3 GB range.  This is where mulk comes in.  This little tool runs great on Windows and has had no problems with any file size.  Download it and using the command prompt do the following:

mulk-0.6.0>mulk -v –user=<user> –password=<password> -d=0 http://10.89.66.102/finesse/logs/

Updated 01/13/2014:

mulk.exe http://10.0.0.1/finesse/logs/ –v –-user <user> –-password <password> –d 0

The only major drawback from mulk is that it doesn’t have a setting to just download the latest files, but if you think about it, you could probably use wget and mulk in conjunction to create the perfect too to save you some time.

~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

Dialed Number, Call Type, and Script Association

Found this gem on the Cisco Support Forums today and I just had to blog about it because it’s awesome!  The OP is looking for a way an easy way to find which ICM dialed numbers are associated with which call type and which call type is associated with which script.  The end result is this work of art:

SELECT dn.DialedNumberString, dn.EnterpriseName AS Dialed_Number_Name, ct.EnterpriseName AS Call_Type_Name, ms.EnterpriseName AS Script_Name
FROM Dialed_Number dn
LEFT OUTER JOIN Dialed_Number_Map dnm ON dn.DialedNumberID = dnm.DialedNumberID
LEFT OUTER JOIN Call_Type ct ON dnm.CallTypeID = ct.CallTypeID
LEFT OUTER JOIN Call_Type_Map ctm ON ct.CallTypeID = ctm.CallTypeID
LEFT OUTER JOIN Master_Script ms ON ctm.MasterScriptID = ms.MasterScriptID

Put this in a CUIC report and you can give the users the power to confirm where things are supposed to route without having to ask you. :-)

~david

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

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

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