cancel
Showing results for 
Search instead for 
Did you mean: 
cancel

UCCX SQL Query to get list of user uploaded prompts from CCX12 CLI

Joshua Head
Beginner
Beginner

Hi,

 

We are trying to get a list of all the prompts that have been uploaded to the cluster from CLI...

 

I am aware of 'file uccx list prompt_file system/G711_ULAW/en_US detail' for system prompts but can't find anything similar for user prompts.

Any help would be appreciated as going through the folder structure would be extremely time consuming.

1 ACCEPTED SOLUTION

Accepted Solutions

derek.fraser
Beginner
Beginner

Hi Joshua,

I faced the same issue and landed on your thread.  After some research and digging through the UCCX SQL tables I found a solution that worked for me and wanted to post in hopes of benefiting someone in the future.  As you mentioned the "file uccx list prompt_file system" only retrieves the system prompts, which is helpful but I wanted to get an export of the user prompts (specifically for date modified) over paging through the Prompt Management in appadmin with a static list of 20 per page.  In my case the UCCX system had 32 pages so copy/paste was out, there is a trick with CUCM with you can change the page "Rows Per Page" in the URL to get around the max 250 records but doesn't look like UCCX follows that same coding structure.  Another method is clicking on the top level folder icon in Prompt Management (ex. en_US) which downloads all the .wavs into a zip that you can browse with a file explorer or list in a shell, but the date/time stamps are modified to when when you download by the OS, not retaining what's in UCCX.

 

Off to the UCCX database...initially I thought prompts would be in db_cra, this is where skills, resources, teams, etc are stored but after striking out there I eventually found the prompts, documents and grammar files are stored in db_cra_repository.  The two tables to focus on are promptsfiletbl (prompts data) and promptsfoldertbl (prompts parent folder).  Below is a SQL query that will retrieve the same data relayed in appadmin page:

 

run uccx sql db_cra_repository SELECT p.filename, pf.foldername, p.lastmodifystamp, p.lastmodifyuser, p.length \
FROM promptsfiletbl AS p \
INNER JOIN promptsfoldertbl AS pf ON p.parentfolderid = pf.folderid

 

One thing to note is that in the UCCX database the "lastmodifystamp" is type "datetime year to fraction(3)" and stored in the db as UTC so you can convert it in the SQL query or in post with an Excel formula.  At the time of this writing there are bugs (CSCuo06421, CSCsm44356) in Cisco cli's where dashes are rejected so the below command won't work with direct access in the UCCX cli but will work if you use a SQL client like SQuirreL.  Below I'm converting to EDT with "- 4 UNITS HOUR" or you could export your cli results into Excel and use a formula like "=C2-4/24"

 

run uccx sql db_cra_repository SELECT p.filename, pf.foldername, p.lastmodifystamp - 4 UNITS HOUR AS datemodified, p.lastmodifyuser, p.length \
FROM promptsfiletbl AS p \
INNER JOIN promptsfoldertbl AS pf ON p.parentfolderid = pf.folderid

 

Another method I found was accessing the data via the UCCX API GET Prompt documented here:

https://developer.cisco.com/docs/contact-center-express/#!get-prompt-files-and-folder/get-prompt-files-and-folder

Utilizing the UCCX API you can collect the prompt data and parse in your language of choice (thumbs up for Python and requests library).  My testing was on UCCX 11.6.2 ES06.

 

Hopefully it helps,

Derek

View solution in original post

2 REPLIES 2

derek.fraser
Beginner
Beginner

Hi Joshua,

I faced the same issue and landed on your thread.  After some research and digging through the UCCX SQL tables I found a solution that worked for me and wanted to post in hopes of benefiting someone in the future.  As you mentioned the "file uccx list prompt_file system" only retrieves the system prompts, which is helpful but I wanted to get an export of the user prompts (specifically for date modified) over paging through the Prompt Management in appadmin with a static list of 20 per page.  In my case the UCCX system had 32 pages so copy/paste was out, there is a trick with CUCM with you can change the page "Rows Per Page" in the URL to get around the max 250 records but doesn't look like UCCX follows that same coding structure.  Another method is clicking on the top level folder icon in Prompt Management (ex. en_US) which downloads all the .wavs into a zip that you can browse with a file explorer or list in a shell, but the date/time stamps are modified to when when you download by the OS, not retaining what's in UCCX.

 

Off to the UCCX database...initially I thought prompts would be in db_cra, this is where skills, resources, teams, etc are stored but after striking out there I eventually found the prompts, documents and grammar files are stored in db_cra_repository.  The two tables to focus on are promptsfiletbl (prompts data) and promptsfoldertbl (prompts parent folder).  Below is a SQL query that will retrieve the same data relayed in appadmin page:

 

run uccx sql db_cra_repository SELECT p.filename, pf.foldername, p.lastmodifystamp, p.lastmodifyuser, p.length \
FROM promptsfiletbl AS p \
INNER JOIN promptsfoldertbl AS pf ON p.parentfolderid = pf.folderid

 

One thing to note is that in the UCCX database the "lastmodifystamp" is type "datetime year to fraction(3)" and stored in the db as UTC so you can convert it in the SQL query or in post with an Excel formula.  At the time of this writing there are bugs (CSCuo06421, CSCsm44356) in Cisco cli's where dashes are rejected so the below command won't work with direct access in the UCCX cli but will work if you use a SQL client like SQuirreL.  Below I'm converting to EDT with "- 4 UNITS HOUR" or you could export your cli results into Excel and use a formula like "=C2-4/24"

 

run uccx sql db_cra_repository SELECT p.filename, pf.foldername, p.lastmodifystamp - 4 UNITS HOUR AS datemodified, p.lastmodifyuser, p.length \
FROM promptsfiletbl AS p \
INNER JOIN promptsfoldertbl AS pf ON p.parentfolderid = pf.folderid

 

Another method I found was accessing the data via the UCCX API GET Prompt documented here:

https://developer.cisco.com/docs/contact-center-express/#!get-prompt-files-and-folder/get-prompt-files-and-folder

Utilizing the UCCX API you can collect the prompt data and parse in your language of choice (thumbs up for Python and requests library).  My testing was on UCCX 11.6.2 ES06.

 

Hopefully it helps,

Derek

Thanks Derek, excellently written response, a bit late for me but will be useful in the future I'm sure!

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: