CUCM SQL Query Assistance

[James Hawkins]

Hi,

I am trying to write a CUCM SQL query to pull information about users, numbers and departments out of the enduser table.

The basic format of the query is run sql SELECT firstname,lastname,telephonenumber,department FROM enduser WHERE telephonenumber IS NOT NULL ORDER BY lastname which returns data in columns.

I want the data to be in CSV type format - james,hawkins,7295,Technical Support - and am trying to create a SQL query that does this so I will minimise the data processing in the app I am writing.

I have used multiple nested CONCAT operators to get most of what I want but three blank lines are being inserted after each line of returned data.

run sql SELECT CONCAT (CONCAT ((CONCAT(firstname,',')), (CONCAT(lastname,','))), CONCAT ((CONCAT(telephonenumber,',')), (CONCAT(department,'')))) FROM enduser WHERE telephonenumber IS NOT NULL ORDER BY lastname

Does anyone know how to remove these extra lines within the SQL statement?

Also if there is a better way than using nested CONCAT statements to get this output I would be interested to know of it.

[Anthony Holloway]

It's likely just a function of the word wrapping on the console.  To prove it, maximize your window and set your terminal font to the smallest setting you can, and then re-run the sql query.

[James Hawkins]

Anthony,

Thanks for the suggestion but unfortunately it did not solve the issue.

Thinking about the problem in more depth I have decided to use AXL for run the SQL query and the parse the XML file to create the CSV data.

This had it's own challenges as the XML file was not properly created where fields such as firstname were blank in CUCM.

I was able to solve this using the SQL query below which replaces blank fields with the text 'Not Specified'

SELECT REPLACE(firstname,'','Not Specified') AS firstname,lastname,telephonenumber,REPLACE(department,'','Not Specified') AS notes FROM enduser WHERE telephonenumber LIKE '1________' ORDER BY lastname

The actual use case is to create a Finesse Phone book by pulling CUCM end users using AXL and then using REST to upload the phonebook to UCCX.

The next issue I think I will bump into is the 1500 entries per phone book in UCCX 11.6 but I think I should be able to split into multiple phones books.