Solved: Queue stats on phone

gbwatkins · ‎03-15-2007

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?

Gergely Szabo · ‎03-16-2007

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)

- 1need@ccX: 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=1need@ccX;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.

View solution in original post

Gergely Szabo · ‎03-15-2007

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 · ‎03-15-2007

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

Gergely Szabo · ‎03-15-2007

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

gbwatkins · ‎03-16-2007

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

Gergely Szabo · ‎03-16-2007

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)

- 1need@ccX: 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=1need@ccX;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.

Zin.Karzazi · ‎03-16-2007

Really great, im gonna test it next week.

You have My 5 points.

Thanks.

ranpierce · ‎03-16-2007

Thanks, you got mine too.

gbwatkins · ‎04-10-2007

awesome, thanks a ton!!

gbwatkins · ‎04-25-2007

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 · ‎04-25-2007

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 · ‎04-26-2007

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 · ‎04-26-2007

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

rlp

gbwatkins · ‎05-04-2007

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 · ‎05-04-2007

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?