Solved: Extracting info about JTAPI user

Anthony O'Reilly · ‎02-15-2010

Hi,

We are preparing for a call manager upgrade and we have about 800 phones associated with JTAPI when we should have only 320.

Is there any tool out there that you can extract data about the devices that are associated with JTAPI. I am looking for the serial number and extension.

Once I have this info, I can easily select what folder will ned to be updated during the upgrade.

Thanks

Anthony.

William Bell · ‎02-17-2010

Anthony,

The way the query is written assumes that you are running this against the CCM database (e.g. CCM0300, CCM0301, etc.). I apologize for the confusion. You can switch database contexts from the drop down menu located in the tool bar of the SQL query analyzer (see attached screen shot). You will pick the latest CCM database. As an example CCM0301 is later than CCM0300.

Also, I thought of something when I was validating the query syntax. You are probably only interested in IP phones and not the other items stored in the device table (such as Hunt Lists, Route Lists, etc., CTI RP, CTI Ports, etc.). So, the following query(ies) may be better suited to your needs.

IF you want all phone types (including IP phones, analog stations, etc.) THEN use the following

select d.name as 'devicename',dmap.numplanindex, n.dnorpattern

from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid

inner join numplan as n on dmap.fknumplan=n.pkid

where d.tkclass=1

order by d.name, dmap.numplanindex

OR

IF you want only IP phones (i.e. SEPxxxxxxxx) THEN use the following:

select d.name as 'devicename',dmap.numplanindex, n.dnorpattern

from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid

inner join numplan as n on dmap.fknumplan=n.pkid

where d.tkclass=1 and d.name like 'SEP%'

order by d.name, dmap.numplanindex

HTH.

Regards,
Bill

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

View solution in original post

William Bell · ‎02-15-2010

Anthony,

What version of CUCM are you trying to extract this data from?

Are you looking for devices associated to a specific user ID? Is this what you mean by JTAPI association?

In regards to the data you can find. You won't be able to determine the serial numbers from the CUCM. You have to get that from the device/phone. Either by physically looking at the phone set or scraping the information from the HTML presented by the phone when you connect to http://

To find out device associations in CUCM pre-Appliance (i.e. 4x) you will need to pull the data from LDAP. Either a backend LDAP or the DC Directory service on the CUCM. If you have DC Directory, then you can use the avvid_migrate_save tool to "dump" the LDAP data. Or you can use Softerra LDAP browser to grab data for the user object you care about (you will need to follow the user object's CCNProfile reference.

Check out the following links if the above doesn't mean much to you:

http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/admin/4_1_3/ccmsys/a04direc.html

http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/admin/3_3_2/ccmsys/a04ldap.html#wp1047642 - I only include this because it gives an example of the CCNProfile and associated devices list that you may be inquiring about

If you are running an appliance model of CUCM and you want to see device associations then you can run the following query in the command shell or using the SQL Query Tool (install plugin on CUCM). The command shell version is provided as an example:

admin: run sql select eu.username, d.name as device from enduser as eu inner join enduserdevicemap as eud on eud.fkenduser=eu.pkid inner join device as d on eud.fkdevice=d.pkid where eu.username='myuserid'

In the example above, replace myuserid with the user ID you care about. Make sure you keep the single quotes.

If you need more data on SQL Query Tool, check here.

HTH.

Regards,
Bill

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

Anthony O'Reilly · ‎02-16-2010

Hi Bill,

Thanks a million for your very detailed response.

We are using Call Manager 4.1(3) SR7. I had a look at the DC and this gives me the serial number of the phone.

What I would like is to output all devices associated with JTAPI with the following info:

serial number, extension number

SEP001434E2EB45, 12345

I also looked into the SQL query tool (although SQL is far from my strong point) and getting nowhere at all.

What Db do I run this against?

Thanks

Anthony.

William Bell · ‎02-16-2010

Anthony,

Now I follow. The SEP001434E2EB45 is the device name not the serial number. The SQL query I provided was for CUCM 6x/7x not 4x. In CM 4x, the device information is stored in SQL but the user name information is stored in DC Directory (by default). User information can also be stored in a LDAP directory like AD.

In CM 4x, the following query will get you the device names and phone numbers:

select d.name as 'devicename', dmap.numplanindex, n.dnorpattern

from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid

inner join numplan as n on dmap.fknumplan = n.pkid

order by d.name, dmap.numplanindex

This will kick out a list of devices with all numbers associated to each device. The "numplanindex" is needed so you can clearly identify the lines associated to devices with more than one directory number. The index actually is the order the number appears on the phone station.

Now, the part about identifying the devices associated to a specific user is a data element of the user object. This means you have to get that from DC Directory or the LDAP. There are several ways to go about doing this. If you have an LDAP browser like Softerra then you can browse the DC Directory (or AD) tree and find what you are looking for. I am not going to go into that here. I will take the leap of faith and assume you are using DC Directory. With DC Directory I have found the quickest way to get the data is to use a tool called avvid_migrate_save.

1. Connect to your publisher and launch a command prompt

2. Enter the command avvid_migrate_save (if provide no arguments you will get the syntax of the command)

example: c:\>avvid_migrate_save mypublisherHostName myadminpassword

- NOTE: the "myadminpassword" is the DC Directory password not the OS password and not the ccmadministrator password. Though, it is always possible the values are the same.

3. The avvid_migrate_save tool will dump the DC Directory LDAP data into a series of files. There is a log file that is created which you may want to check for errors.

4. After the tool completes its execution, go to c:\dcdsrvr\run\dcx500\config\Migration-Backup\

5. In this directory, open the file avvidprofiles.txt in notepad

6. Search for the user ID you are interested in

7. You will find something like this:

dn: cn=myuserid-CCNProfile-{28292828239282},ou=profiles,ou=CCN,o=cisco.com

<..stuff not shown..>

ciscoatProfileOwner: myuserid

<..stuff not shown..>

ciscoCCNatControlDevices: SEP000000000001

ciscoCCNatControlDevices: SEP000000000002

<..etc...>

<..stuff not shown..>

So, what you are looking for are the list of "ciscoCCNatControlDevices" that occur with your ciscoatProfileOwner.

Unfortunately, to get the devices associated with the user ID you are interested along with the directory numbers you are going to have to do some Excel work to marry everything up. Alternately, if you feel comfy with scripting then you can use a script to pull the LDAP data and the CUCM SQL data and splice them together. Using Excel will probably be quicker.

HTH. Please remember to use the rating system (for good or bad).

Regards,
Bill

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

Anthony O'Reilly · ‎02-17-2010

Hi Bill,

I ran the avvid tool and I have the info I am looking for. unfortunately I cannot run the query you gave me, it returns the errors in the screen shot I have attached.

Many thanks for all you great help with this one.

Thanks

Anthony.

William Bell · ‎02-17-2010

Anthony,

The way the query is written assumes that you are running this against the CCM database (e.g. CCM0300, CCM0301, etc.). I apologize for the confusion. You can switch database contexts from the drop down menu located in the tool bar of the SQL query analyzer (see attached screen shot). You will pick the latest CCM database. As an example CCM0301 is later than CCM0300.

Also, I thought of something when I was validating the query syntax. You are probably only interested in IP phones and not the other items stored in the device table (such as Hunt Lists, Route Lists, etc., CTI RP, CTI Ports, etc.). So, the following query(ies) may be better suited to your needs.

IF you want all phone types (including IP phones, analog stations, etc.) THEN use the following

select d.name as 'devicename',dmap.numplanindex, n.dnorpattern

from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid

inner join numplan as n on dmap.fknumplan=n.pkid

where d.tkclass=1

order by d.name, dmap.numplanindex

OR

IF you want only IP phones (i.e. SEPxxxxxxxx) THEN use the following:

select d.name as 'devicename',dmap.numplanindex, n.dnorpattern

from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid

inner join numplan as n on dmap.fknumplan=n.pkid

where d.tkclass=1 and d.name like 'SEP%'

order by d.name, dmap.numplanindex

HTH.

Regards,
Bill

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify