UCCX 7 Script to Connect to Local SQL DB

ryan_bell · ‎03-11-2011

Hi All,

Currently I am working on an ACD script for a customer's help desk. The version is UCCX 7.0.1 SR4 Premium. The requirements are once the call is complete and the agent selects the Wrap-Up Data, to send an email to their ticketing system with the call details and the Wrap-Up Data. Current I have everything working for the queue for the ACD, and to send an Email to the ticketing system with the Create and Send Mail steps. My issue to obtaining to Wrap-Up data to also include in the email.

My idea is after the call is connected to the agent, to query the "AgentConnectionDetail" table in the UCCX SQL DB which stores the call and the wrap-up data once the call in complete. I can currently load the SessionID as a variable in the script and I can setup the script to repeat the query (for a specified amount of time) until the SesionID loads in the AgentConnectionDetail table with the info.

My problem though is how do I connect to the UCCX SQL table from the script since the server's authentication mode is Windows only and I've seen multiple things how setting SQL to Mixed Mode Auth can cause problems and is not supported. I want to query the live DB so that the email can send out ASAP and so the script is not running for too long.

Does anyone have any suggestions or other ways to gather that info in the script to include in the email being sent out?

Thanks,

Ryan

ryan_bell · ‎03-11-2011

OK so far I've just setup a DTS Package/Job to export the data from this table to a Data Warehouse server every 1 minute and I'm then pulling the data from that which works fine, just delays things up to a minute which isn't too bad.

NOW though I've got a new issue which I do not understand nor know how I'm going to overcome. To query the record, I'm using the sessionID which I already obtained in the script. This mostly works fine, except when an agent is set to ready in their CAD, and does not answer the call, the caller is then placed back in the queue and answered by the next available agent. The problem though is once this happens, there are now two records in the AgentConnectionDetail table with the SAME sessionID, which I thought was not possible. I also queryed SQL directly on the UCCX server for this sessionID and I still return two rows, though the first one has no duration or wrapupdata since the first call was not actually answered.

Below is the query from SQL....could this be a Bug?

sessionID            sessionSeqNum nodeID profileID   resourceID startDateTime                                          endDateTime                                            qIndex gmtOffset ringTime talkTime holdTime workTime callWrapupData                           callResult dialingListID rowguid                              Expr1
-------------------- ------------- ------ ----------- ----------- ------------------------------------------------------ ------------------------------------------------------ ------ --------- -------- -------- -------- -------- ---------------------------------------- ---------- ------------- ------------------------------------ --------------------
70000279338          0             1      1           1043        2011-03-11 16:53:26.843                                2011-03-11 16:53:36.843                                1      -480      10       0        0        0                                                 0          -1            D8F374B5-5AF1-42B7-8CAF-F3DB6FEF02F4 70000279338
70000279338          0             1      1           1043        2011-03-11 16:53:44.560                                2011-03-11 16:54:06.717                                1      -480      2        20       0        30       IT - Reporting                           0          -1            9FAD2E82-A74C-4A8F-9033-67DA79093CAC 70000279338

(2 row(s) affected)

Thanks,

Ryan

Phuong Hoai Huynh · ‎03-14-2011

Hi Ryan

I wouldn't consider it a bug.

- All calls are tracked against one session ID, till it terminates.

- Now there are senario where that session ID will still be open and remain in place even though there are new child session created when calls go to subflow/new scripts.

In this case the session was directed to an agent, the UCCX will not terminate this orignal session till the call has finished which is the inbound leg of the call.

Regards

Hoai Huynh

ryan_bell · ‎03-14-2011

That does makes sense, though I thought the record wasn't to be created in that table until a successful call was complete.

"The Cisco Unified CCX system creates a new record in the AgentConnectionDetail table when an agent disconnects a call or a leg by hanging up or by transferring the call."

I guess this includes diconnection by not answering the call as well. So as a workaround, I modified my query to check for the seesionID and only when talkTime does not equal 0

"WHERE sessionID = $sessionID AND talkTime <> 0"

So far this seams to work.

Thanks,

Ryan