Script Monitoring

[ali.parvez]

Is it possible to display the real time script monitoring stats on a plasma screen as a wallboard for the call centre. If so, how is that possible?

[joe.conger]

Matt

I was wondering if you had created an asp page that also included the agent name, status, and duration. i.e For resource group "Sales" pull all the Sales agents.

Sales Team

John Doe Talking 00:05:22

John Smith Ready 00:03:21

I currently have this working for jsp pages with HTTP triggers/Scripts. Your use of Real-time snap/asp without the use of http triggers/scripts is a much more efficient approach then what I'm currently doing. My agents run the jsp pages from their desktop browser which does impact server performance when the browser does a refresh.

Thanks

[mmelbourne]

I'd like to achieve this as a next step. Are you using Get Reporting Statistics steps to get per-agent information, or are you performing a direct database dip? Maybe others have done this with ASP code, but it would involve tieing up the database tables.

[l.mourits]

Hi,

I have been looking into both options but have not found a solution yet.

Using the get reporting statistics step requires the full name as input. Waht could be done is to use a db get step from the Resource table to identify active usernames and then use the get reporting statistic step within the db read step. The problem I ran into was that the nvarchar that is returned was not a supported java class which is why I could not map it to a local variable.

As a result I went into the asp and database dipping solution, but the complexity of the SQL query (to get the last distictive rows for all entries based on the field agent-ID in order to get the current status from AgentStateDetail) while the table is large runs into performance issues.

I think the best way to move forward is building a custom java class and use the db and get reporting statistics, but since I have no java experience at all I have trouble doing this myself.

For what it is worth, I hope this helps,

Leo

[joe.conger]

I'm using Get Reporting Stats to get the Queue Stats and the Agent Stats. Currently I pull the Queue(Agents Ready, Calls Waiting, Avg Wait, Calls Handled, and Calls Abandoned) and the Agent(Status, Status Duration, and Total Handled). Also in the script I total up all the calls handled & abandoned for a specific call center. It works but it is not very efficient and also I must update the scripts whenever we add or remove agents.

[l.mourits]

Correct, that works, but indeed is not efficient. So, I took the csq.asp written by mmelbourne, and changed into an asp script to dip into the database to get the realtime agent status for all agents.

It took me some time to figure out the most efficient query, as I don't want to run into performance issues. The key was to first select the active agents only, then use this select as input to get the last event from each active agent (by using max(eventDateTime). Using this eventDateTime together with the relationship on agentID and resourceID from the AgentStateDetail and Resource table gave me the results I wanted in less then a second (and this is a database that has never been purged, containing data for over 2 years with appr. 15 agents so quite some rows there ;-))

The attached agent.asp will give full agent name, status and last time the status changed. It will display not ready and not logged in agents in red, talking, reserved and working in orange and available in green (something that fitted my needs).

I have two improvements still on my wish list.

First off, I like to display the time in the current status, rather then the last time the status changed. This shouldn't be to hard, knowing it is possible to get the current date from the system, then use this to calculate the number of seconds in the current status from the returned eventDateTime value. However, I did not yet find the time to figure it out yet and did not want to wait to long to post this to the community.

My second improvement wish is to be able to set a constant where one can define the maximum number of rows. Why? Well, if you have a lot of agents it will display a lot of rows and it might show the scroll bar which is not what we want on a wallboard ;-)

In order to achieve this it is needed to enhance the script in a way that it calls procedures to build page and table headers. Again, shouldn't be too hard but I did not find the time yet.

Anyway, hope it helps, and let me know what you guys think of it ;-))

Leo

[mmelbourne]

Excellent improvement on my efforts (5 points from me). Just wish I fully understood the SQL query ;-)

[l.mourits]

Thanks for the rating ;-)

Now, in case there are more people having trouble understanding the SQL, here is the explanation:

Let's look at the whole query first:

SELECT resourceName, eventType, eventDateTime FROM Resource, AgentStateDetail

WHERE Resource.resourceID = agentStateDetail.agentID

AND eventDatetime IN

(SELECT MAX(eventDateTime)

FROM AgentStateDetail

WHERE agentID IN

(SELECT resourceID

FROM Resource

WHERE active = 1)

GROUP BY agentID)

ORDER BY resourceName

The key is in the IN statement that I use, this allows a SELECT to use a value as input from another SELECT statement. Let's explain in the way this query is executed to better understand.

Since each IN statement requires the SELECT that is used as input to be exceuted first the above query will start with the following:

(SELECT resourceID

FROM Resource

WHERE active = 1)

Hopefully I don't have to explain that this will provide a list of all resourceIDs that are active. Then this list of resourceIDs is used as input for the following part of the query.

(SELECT MAX(eventDateTime)

FROM AgentStateDetail

WHERE agentID IN

In other words, execute a SELECT MAX(eventDateTime) FROM AgentStateDetail for all agents in the list of the previous SELECT. As a result, this query will give the maximum (read: latest) eventDateTime for each active agent.

Then, it wille execute the last part of the query which is:

SELECT resourceName, eventType, eventDateTime FROM Resource, AgentStateDetail

WHERE Resource.resourceID = agentStateDetail.agentID

AND eventDatetime IN

As one can see, it uses the eventDateTime found in the previous SELECT statement as input, so this query will be executed for each active agent only. It gathers the full name from table Resource and all other info from AgentStateDetail with a match on resourceID and agentID as these are the matching keys. I could have use a SELECT with just agentID and leave the resourceID = agentID part out of the query, but I thought full name would be a better option to display.

Hope it explains how the query works, and even more I hope it explains why it performs so well ;-))

Regards,

Leo

[mmelbourne]

Thanks! That clarifies things.

Did you have to manually grant access to the Resource and AgentStateDetail tables to the wallboardUser?

[l.mourits]

Yep, I had to grant read access to the AgentStateDetail and Resource table for the wallboard user but this is quite simple using enterprise manager (just check the user's permissions and add the read access to the tables mentioned).

Regards,

Leo

[joe.conger]

Thanks for the information. I'm not a SQL guru so the explanation was very helpful.

I do have 3 seperate call centers so I added resource.assignedTeamID to the query to pull a group of users instead of all the active ones. Also the status duration is a little different then what I need. I need the Status Duration to calculate the amount of time the agent has been in their current status.

John Doe Talking 00:03:23 "3 min 23 sec"

Did you happen to monitor perfmon during the page load or refresh. On my server the processor utilization spiked to 30+% on every refresh.

Thanks

[l.mourits]

Joe,

If you experience CPU issues they are more likely due to the change you made to the SQL query. The query as I wrote it in the script is very CPU friendly, I am quite sure about that.

I did use perfmon/task manager to monitor system performance and I did not see any CPU utilization increase on either my test or production system. However, I have noticed that there are processes peaking to 75% once in a while but this is not due to the sqlmangr proces or due to IIS. I did see the peaks while the asp did not run as well.

As stated in my reply, to have the actual time in status displayed, is on my wish lists as well, and I am actually working on it to get this achieved. Just consider this as a pilot/draft/beta version (whatever you like to call it) and feel free to improve and post back to the community ;-)

Hope this helps,

Leo

[shane.orr]

Question Regarding these scripts. I was able to get them working but wanted to know if anyone could tell me in the RtCSQsSummary table fields like TotalCalls and AbandonedCalls do these values purge or when do they start. Do they start over after a certain time period or maybe when the server reboots? I guess I was wondering from what time period is these abandon calls start.

Also on my agent status page I have some users that dont show up because they have not logged in yet or in quite a while, as soon as the do login they show up but I was wondering if there was a time period where a user would remain logged off for a time period then stop showing up on the agent status page?

[l.mourits]

Hi,

The fields TotalCalls and AbandonedCalls in RtCSQsSummary start counting again each day, so the counters are set to zero at midnight.

The script comes with a parameter.cfg which contains all kinds of parameters to customize the display. The following two affect the showing of logged out agents.

Const intAgentStatsMaxDays = 1

Const intAgentStatsMaxHours = 8

If MaxDays is set to any value higher then 1 MaxHours will no longer affect the display. In the example above (which is the default) agents logged out for over 8 hours are not displayed. If you want to display agents logged out for over 8 hours change these parameters to match your criteria (ps. if you change it to more then one day the display will start showing the number of days next to hours,minutes and seconds)

HTH,

Leo

[l.mourits]

Allright, here is an updated version of the agent.asp script. It now displays the time since the last status change, and it is more structured (using all kinds of parameters to allow easier customization).

One of the ideal parameters is the intMaxDays and intMaxHours that you can set now. Depening on waht you configure there the agents display depending on when their last status change was (I added this since I only want to display agents that had a status change in the last 8 hours, basically the agents that are in that day).

I got alerted by Joe's response about performance and after his second mail I investigated this further. It turs out that the SQL is indeed CPU intensive, and allthough it runs for less then 1 second it indeed uses about 25% CPU utilization.

Bearing this in mind I recommended to set the refresh time not lower then 10 seconds and have only 1 wallboard screen running.

Let me know what you guys think of the script so far and if this is heading the right way ;-

In case anyone has a clue on how to improve the CPU util peaking, please post your reply or drop me an email.

Thanks,

Leo

[Chuck Bohatka]

I'd like to take advantage of this page (brilliant BTW!) but my AgentStateDetail table is empty. What do I need to check for this to populate regularly?

Thanks.

c b j