Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements
You may experience some slow load times, errors, and slight inconsistencies. We ask for your patience as we finalize the launch. Thank you.

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

New Member

SQL select statements

Hey everyone,

First of all, yes, I have been looking through the 8.5 database schema guide.  As I have been reviewing the schema, I have been developing some ideas as to how to collect the desired data.  However, if anyone has already developed or found the SQL statements (which I'm sure someone already has) it would help me by minimizing buggs in my data collection program.

All of these statistics need to be grouped by CSQ and selected for a certain time range (<start time> and <stop time>).  I.e. 1 hour increments.  I have no problem getting a list of results and then performing calulations to get the desired end result.  Also, if I need to perform multiple select statements to essentialy join two tables, please include both statements.  Finally, I saw the RtCSQsSummary table, but I have to collect data for the past, not at that given time.

1. Total calls presented per CSQ

2. Total calls answered per CSQ

3. Total calls abandoned per CSQ

4. Percentage of calls abandoned per CSQ  (if this is not stored in the database, I'm thinking: <calls abandonded>/<calls presented>)

5. Average abandon time in seconds (if this is not stored in the db, I'm thinking: sum(<abandon time>)/<calls abandonded>)

6. Service Level - % calls answered in 90 seonds by a skill-set (I saw metServiceLevel in table ContactQueueDetail; however, I would have to find how to configure this threshold for the application)

7. Average speed of answer per CSQ

8. Average call talk time per CSQ

9. Aggregate logged in time of CSQ resources/agents

10. Aggregate ready time of CSQ resources/agents

I realize that some of these should be easy to find (as I am still digging through the db schema guide), but I was reading how a new record is created for every call leg so I could easily see how I could get inaccurate information without properly developed select statements.

Any help will be greatly appreciated.

Brendan

  • Contact Center
Everyone's tags (4)
1 ACCEPTED SOLUTION

Accepted Solutions

SQL select statements

Brendan,

I read your post very well.

You have the database schema with its tables and description. each table has its associated tables (connected with primary and foriegn keys). I think you need to start the tables to check what you need.

cisco use stored procedure to create the reports. the stored procedure is "sp_csq_interval" to create report "

Contact Service Queue Activity Report"

HTH

Anas

please rate all helpful posts


6 REPLIES

SQL select statements

Hi,

you can achieve most of the required info from "Contact Service Queue Activity Report".

HTH

Anas

please rate all helpful posts

New Member

SQL select statements

Anas,

I know I can pull most to all of this information from the historical reporting tool.  However, because of the tool I am developing, and to all but eliminate human interaction, I need to pull data that can be used in a Java application.

I appologize for not making that clear.

v/r,

Brendan

SQL select statements

Hi,

kindly use the below link

http://www.cisco.com/en/US/docs/voice_ip_comm/cust_contact/contact_center/crs/express_8_5/user/guide/uccx85dbschema.pdf

it is the data base schema for UCCX 8.5.

if you want to connect to DB. go to page 123. it shows you how to connect to DB. it is for UCCX 9 not 8.5 but it worths to try

http://www.cisco.com/en/US/docs/voice_ip_comm/cust_contact/contact_center/crs/express_9_02/programming/guide/UCCX_BK_CFD16E30_00_cisco-unified-contact-center-express.pdf

HTH

Anas

please rate all helpful posts

New Member

SQL select statements

Anas,

Please read my original post.  I have the schema guide (which I have been going through) and connecting to the db is not my problem.  I am looking for someone who has already developed the SQL statements to gather the information required.

v/r,

Brendan

SQL select statements

Brendan,

I read your post very well.

You have the database schema with its tables and description. each table has its associated tables (connected with primary and foriegn keys). I think you need to start the tables to check what you need.

cisco use stored procedure to create the reports. the stored procedure is "sp_csq_interval" to create report "

Contact Service Queue Activity Report"

HTH

Anas

please rate all helpful posts


New Member

SQL select statements

Anas,

I had to do a lot more digging after this reply, but it did eventually lead me to what I was looking for.

Thank you,

Brendan

637
Views
21
Helpful
6
Replies