CUCM SQL query

[karthikvish2000]

Hello all,

I am wondering if there is an SQL query that would return "User ID" and corresponding "Primary Extension" for a user.

I have configured "Primary extension" for all users in the cluster and I wish to extract this information using AXL for import into a 3rd party application for a custom solution (details not required).

Can somebody please help with this?

Thanks

Karthik

[Brett Fritzsch · Accepted solution]

Karthik,

This query will match the EndUser ID to the primary extension that is configured on the end user page and list out the UserID and Extension.

run sql select e.userid, n.dnorpattern from enduser as e, numplan as n, endusernumplanmap as x where (e.pkid = x.fkenduser and x.fknumplan = n.pkid)

Hope this helps!

-Brett Fritzsch

[ashok_boin]

Hi Bret,

Will this query works with Cisco CUCM 8.5 also?

And also, I could not able to understand this query correctly. Do we need to directly run "

run sql select e.userid, n.dnorpattern"?

Thanks for sharing this valuable info.

Regards...

-Ashok.

[Brett Fritzsch]

Ashok,

Yes this will work on 8.5.  In regards to the query just copy and paste what I have below:

run sql select e.userid, n.dnorpattern from enduser as e, numplan as n,  endusernumplanmap as x where (e.pkid = x.fkenduser and x.fknumplan =  n.pkid)

You should get an output like the following:

userid   dnorpattern

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

5659     5659

5658     5658

test     69211

5657     5657

TestUser 8006

Hope this helps!

-Brett Fritzsch

[ashok_boin]

Thanks a lot, Brett.

It's really great help.

Regards...

-Ashok.

[karthikvish2000]

Excellent.

Thanks Brett. This is exactly what I am after.

Thanks for your help. Really appriciate it.

[Sandy Hale]

Hi Brett.  If you are still around can you tell me how to output this query to a file?

Thanks

[Jaime Valencia]

You can simply use a program like putty to SSH, and configure it to capture all the output of the session to a text file.

[Sandy Hale]

Hi again, my query is not returning everything because I do not see my own userid  sdh7820.

Any idea?

[Sandy Hale]

I figured it out. You must have the Directory Number Association  / Primary Extension in the End User Configuration. So this may or maynot be completed for all Users. Any other suggestions.  What I really want to do is clean up my CUCM.  As I close a site I want to be able to clean everything up and delete the device and everthing associated with (CSS, DP, Location...) I want to get the End User and the Default Profile associated with a DN?

[gdewinter]

Sandy,

If you add e.telephoneNumber to the select =>

select e.userid, n.dnorpattern, e.telephoneNumber from enduser as e ...

You should see both of them.

Br,

Geert