Informix query question

[gbwatkins]

All,

I am trying to integrate a system with cucm and need to pull the device (mac address), device profile actively logged in and extension information.  I am not sure the filed nams on which to query and how to formulate the query itself.  Essentially, I need to be able to run the equivalent of the actively logged in device profiles query but via sql.

Cheers,

Geoff

[Christos Georgiadis]

Hi Geoff,

I don't have access to my lab right now but hopefully the info below will get you started

Find the database dictionary document for your version of callmanager in cisco.com

ie here is the link for the one for cucm 7.0.3

http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/datadict/7_0_1/DD_701.pdf

In there you find all the tables in the informix db with the respective columns

The tables you should be looking at for starters are the device and enduser tables

There is this extensionmobilitydynamic table but not sure if this is useful for you (need to test when login and logout since this is a synamic table)

A query from the callmanager cli is essentially similar to a microsoft sql query. You just need to add the 'run sql' before

for example

run sql select * from device where description='Myphone'

You can also see all the db tables with the following command from cli

show tech systables

Hope this helps,

Christos

[Christos Georgiadis]

Ok I did some quick testing today

First you query the extensionmobilitydynamic table and you see the users currently logged it

run sql select fkdevice,fkenduser from extensionmobilitydynamic

then you go to the device table to find the device

run sql select name from device where pkid='the fkdevice pkid you got from the previous query'

this will give you the mac address of the phone

Then go to the enduser table to get the user who is logged in

run sql select userid from enduser where pkid='the fkenduser pkid you got from the 1st query'

Obviously you can get a lot more information from these tables if you want to.

Let me know if this helps,

Christos