Queue stats on phone

Answered Question
Mar 15th, 2007
User Badges:

I have a customer who would like the agents to be able to view queue stats on their phone as well as from agent desktop. Could anyone point me in the right direction?


Correct Answer by Gergely Szabo about 10 years 2 months ago

Hi there,


so here it comes.


The sample setup is as follows. First, some basic terms I am using in the script.


- cc-aw: he name of the server actually running the script. This is the ICM Admin Workstation (AW) server (the script pulls information from the local database (127.0.0.1) using SQL auth (this is very important: you must have mixed mode auth enabled in your SQL server, if you have installed AW according to the Cisco docs, you should already have it enabled)


- vialia: the instance name of the ICM installation. This means, on the AW server we can see two ICM databases: vialia_awdb (AW information), vialia_hds (Historical Database). We only need the first one here.


- statreader: the account name we are logging into the SQL server. This should, of course, have the necessary rights enabled for the vialia_awdb database (public and db_owner should be OK, but you can play with restrictions)


- [email protected]: the password for the above account


- url=http://cc-aw/stats/sg_boston.asp: this is the URL of the script. This means, the script name itself is sg_boston.asp (showing information about the Boston skill group), and is saved in the C:\Inetpub\wwwroot\stats\ folder. You might have to give it execute script permission.

- 5099: is the SkillTargetID of the skill group we want to see information about. This is, for instance, the SkillTargetID of the Boston skill group. One might find the SkillTargetID's of all skill groups in the vialia_awdb.dbo.Skill_Group table (try SELECT * FROM vialia_awdb.dbo.Skill_Group in Query Analyzer to get the complete list)


Very important lines in the script:


Response.AddHeader "Refresh", "10; url=http://cc-aw/stats/sg_boston.asp"

This adds a header, instructing the phone to reload the (same) script every ten seconds.


oConn.Open "driver={SQL Server};server=127.0.0.1;uid=statreader;pwd=[email protected];database=vialia_awdb"

For uid, pwd and database values: see the above explanation.


So please change all these values!


It is always good to try the script out in your favorite web browser. If you get an XML object with some realistic numbers, then everything is OK, you can set the URL as idle URL on the phones of the agents.

If you get an error message, then:

- check whether the script itself is accessible by IIS,

- execute permission enabled?

- SQL login OK?


Have fun. And please let me know.


P.S.



  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (7 ratings)
Loading.
Gergely Szabo Thu, 03/15/2007 - 11:42
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

Hello,


- regarding the information needed: try the [instancename]_awdb.dbo.*_Real_Time tables/views on the AW/HDS server.

- regarding data presenentation on the phone: try the Cisco IP Phone Services SDK.


I have a customer with such an application, it is actually a tiny ASP script running on the AW/HDS server itself, pulling information from the local database.


Want me to post a sample script?



ranpierce Thu, 03/15/2007 - 11:44
User Badges:
  • Silver, 250 points or more

Sure I would like to see it if that is alright?

Gergely Szabo Thu, 03/15/2007 - 11:58
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

No problem, tomorrow you'll have it (must visit the customer anyway)

gbwatkins Fri, 03/16/2007 - 07:13
User Badges:

I would like to see that script as well. Thanks in advance for posting.

Correct Answer
Gergely Szabo Fri, 03/16/2007 - 07:56
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

Hi there,


so here it comes.


The sample setup is as follows. First, some basic terms I am using in the script.


- cc-aw: he name of the server actually running the script. This is the ICM Admin Workstation (AW) server (the script pulls information from the local database (127.0.0.1) using SQL auth (this is very important: you must have mixed mode auth enabled in your SQL server, if you have installed AW according to the Cisco docs, you should already have it enabled)


- vialia: the instance name of the ICM installation. This means, on the AW server we can see two ICM databases: vialia_awdb (AW information), vialia_hds (Historical Database). We only need the first one here.


- statreader: the account name we are logging into the SQL server. This should, of course, have the necessary rights enabled for the vialia_awdb database (public and db_owner should be OK, but you can play with restrictions)


- [email protected]: the password for the above account


- url=http://cc-aw/stats/sg_boston.asp: this is the URL of the script. This means, the script name itself is sg_boston.asp (showing information about the Boston skill group), and is saved in the C:\Inetpub\wwwroot\stats\ folder. You might have to give it execute script permission.

- 5099: is the SkillTargetID of the skill group we want to see information about. This is, for instance, the SkillTargetID of the Boston skill group. One might find the SkillTargetID's of all skill groups in the vialia_awdb.dbo.Skill_Group table (try SELECT * FROM vialia_awdb.dbo.Skill_Group in Query Analyzer to get the complete list)


Very important lines in the script:


Response.AddHeader "Refresh", "10; url=http://cc-aw/stats/sg_boston.asp"

This adds a header, instructing the phone to reload the (same) script every ten seconds.


oConn.Open "driver={SQL Server};server=127.0.0.1;uid=statreader;pwd=[email protected];database=vialia_awdb"

For uid, pwd and database values: see the above explanation.


So please change all these values!


It is always good to try the script out in your favorite web browser. If you get an XML object with some realistic numbers, then everything is OK, you can set the URL as idle URL on the phones of the agents.

If you get an error message, then:

- check whether the script itself is accessible by IIS,

- execute permission enabled?

- SQL login OK?


Have fun. And please let me know.


P.S.



Attachment: 
Zin.Karzazi Fri, 03/16/2007 - 08:07
User Badges:
  • Silver, 250 points or more

Really great, im gonna test it next week.

You have My 5 points.


Thanks.

ranpierce Fri, 03/16/2007 - 08:12
User Badges:
  • Silver, 250 points or more

Thanks, you got mine too.

gbwatkins Wed, 04/25/2007 - 07:08
User Badges:

Thanks for the script. I have one question however and it pertains the ICM AW. Having never worked with this software package, I would like a little more clarification. Does it work with IPCC Express and if so, how do you get it - purchase or download.


Thanks a ton!

Gergely Szabo Wed, 04/25/2007 - 07:16
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

Hi,


no, it does not work with IPCC Express, unfortunately.

(Actually, due to historical reasons, IPCC Enterprise and IPCC Express are two completely different products, different philosophy, different settings, etc)


However, it is not a big deal to get those information when it comes to IPCC Express. For instance, you can write a neat, HTTP-triggered CRS (aka Cisco Script) application that does basically the same, ie getting the information, transform it to XML, and finally, use the URL of this application as the idle screen on the phone.


One important thing, though: it might slow down the system a bit, so the refreshes should be far from real time, personally, I recommend 30 seconds.


I did this quite some time ago, the source files should be somewhere (to be honest, at this moment, I have no idea where to I saved them, if you give me a couple of days, I will come back to you and send it over).



gbwatkins Thu, 04/26/2007 - 08:58
User Badges:

Thanks,


I would appreciate it if you could find that script so I could use it as a template. In the meantime, I will work to get one working.


Geoff

ranpierce Thu, 04/26/2007 - 09:05
User Badges:
  • Silver, 250 points or more

I am in the same boat, I have IPCC express. I would greatly appreciate it.


rlp

gbwatkins Fri, 05/04/2007 - 05:44
User Badges:

Any luck finding the xml script for IPCC express? I am not really adept at writting html or xml scripts and could use a point in the right direction.


Thanks!

mike.lo Fri, 05/04/2007 - 20:55
User Badges:

hi, ipccx with enhanced edition can't support http trigger. so is it possible to get the queue state from ipccx and write into xml file, and then write asp file to call this xml and show on ip phone?

mymite060708 Sat, 05/05/2007 - 23:39
User Badges:

Just one thing, cisco told me that they do not support mixed mode SQL auth on ipcc express version 4.04

Actions

This Discussion