Need to extract the following information from CUCM

[waqas sardar]

Dear Team,

We need to extract the following information of each Extension from CUCM server on regular basis.

Extension Number

Host name

User name

I tried from import/export tool, but it provide end user and Mac-address detail in different excel files. Can we get in one file?

[Aaron Harrison]

Try this pasted in to the CLI; log the output with putty:

run sql select first 1 d.name DeviceName, l.name location , css.name devicecss, tm.name devicetype, n.dnorpattern linedn, linecss.name linecss, e.userid userid, e.firstname, e.middlename, e.lastname, e.department department from enduserdevicemap em join enduser e on em.fkenduser = e.pkid join device d on em.fkdevice =  d.pkid join callingsearchspace css on css.pkid = d.fkcallingsearchspace join location l on fklocation = l.pkid join typemodel tm on d.tkmodel = tm.enum join devicenumplanmap dnm on dnm.fkdevice = d.pkid join numplan n on dnm.fknumplan = n.pkid join callingsearchspace linecss on n.fkcallingsearchspace_sharedlineappear = linecss.pkid

That will output the users, their assoicated devices, and the extension number with some other info.

Aaron

[waqas sardar]

Dear Aaron,

I log onto cucm via putty and pasted your provide command ,but I got nothing expect this:

devicename location devicecss devicetype linedn linecss userid firstname middlename lastname department

========== ======== ========= ========== ====== ======= ====== ========= ========== ======== ==========

Where is the database?But i test this on VMWare

[Gergely Szabo]

Hi,

VMWare or no VMWare, it's all the same.

Are you sure there is at least one device configured in your CUCM?

Also, what is your CUCM version? I tested Aaron's SQL in my 7.0 CUCM and it works like charm (actually, if you omit the "first 1" clause, it'll give you the full list, not only the first line).

G.

[waqas sardar]

Dear Gergely Szabo ,

I have two user in CUCM

CUCM Version 7.0.1.11000-2

I have past the print screen here .

[Gergely Szabo]

Hi, you say you've got two users, but do you have any device like a phone with a line in the CUCM?

G.

[waqas sardar]

Yes i have both user with line

[waqas sardar]

Can you please upload your output?

[Aditya Gupta]

Hi Waqas

You can try the following query on your CUCM CLI:

run sql select enduser.userid, enduser.telephonenumber, device.name from enduser inner join enduserdevicemap on enduser.pkid=enduserdevicemap.fkenduser inner join device on enduserdevicemap.fkdevice=device.pkid

But the downside to this query is that this will only work if on CUCM End user has telephone number field filled or has the appropriate extension number added on it.

Regards

Aditya Gupta

[waqas sardar]

Dear Aditya,

Yes it worked, please modify and send me back so that there should be Mac-address, name and extension.

[Aditya Gupta]

Hi Waqas

The query which i have sent you list all the end users in your CUCM and giving you the associated devices and extension numbers where as what you are looking for is the list of  extensions and assocaited devices and users .

MAC address of the device has already been included in the above query. "Name" field should be giving you the device name(SEP + MAC address of the device)

By the term "User Name " are you looking for user id  or first name and last name of the end user in CUCM ?

As mentioned previously "Telephone Number" field on the end user page of CUCM must be filled in order for this query to display you the extension numbers. I am doing some research and will check if i can find some other related fields from the numplan table.

Otherway out can be you can always join multiple excel sheets onto one .. Just a workaround ... but i am trying some queries at my end to check if i can find something suitable

Regards

Aditya

[waqas sardar]

Ok ..This is fine .But just tell me , i can use this SQL commands in live environment?

My client have 1500 users

Please try work on the last mention options also.

I am waiting

[Aditya Gupta]

Hi Waqas

You are just fetching the data from CUCM , so its not very impacting , but yes when data becomes huge and you are trying to fetch it using SQL it does impact the Call Manager Node from which data is being fetched till the the time query is executed.

Impact will totally depend upon the load on CUCM node at that particular time . If you have high call volume on all the nodes , i will suggest use the query only during off-hours are low volume hours since pulling such huge amount of data will take some Call Manager Resources.

I have following queries :

- You said you want "User Name" field , is this the same as "User Id".

- Extension number with no users assoication will not be displayed on the query result ... Is it fine ?

run sql select enduser.userid, numplan.dnorpattern, device.name from enduser inner join enduserdevicemap on enduser.pkid=enduserdevicemap.fkenduser inner join device on enduserdevicemap.fkdevice=device.pkid inner join devicenumplanmap on device.pkid=devicenumplanmap.fkdevice inner join numplan on devicenumplanmap.fknumplan=numplan.pkid

This SQL query should give the expected results.

Aditya Gupta

[waqas sardar]

By the term "User Name” are you looking for user id or first name and last name of the end user in CUCM ?

Yes i am looking for mac-address, user id, first name and last name.

I can see Mac-address and user id, but first name and last name also required??

[Aditya Gupta · Accepted solution]

Try this :

run sql select enduser.userid, enduser.firstname, enduser.lastname, numplan.dnorpattern, device.name from enduser inner join enduserdevicemap on enduser.pkid=enduserdevicemap.fkenduser inner join device on enduserdevicemap.fkdevice=device.pkid inner join devicenumplanmap on device.pkid=devicenumplanmap.fkdevice inner join numplan on devicenumplanmap.fknumplan=numplan.pkid

dnorpattern is the extension number.

Regards

Aditya Gupta