I'm sorry ... it's CER 2.0.4.

I would imagine the sql statement would be the same for 2.x and 7.x.

Thanks!

I don't have a CER 2.0.4 to check the list of tables. However, I checked in a CER 7.1.1 server, and found that the 'cerunlocatedphone' table saves the information about Unlocated Phones on CER.

You can SSH to the CER server and run the following command :

run sql select * from cerunlocatedphone

Hope this helps.

- Sriram

How do you get the list of tables?

Hi,

The 'show tech systables' would give you the list of tables in the sysmater - this option will not be available on 2.0.4 CER. You can run sql queries on the database for the tables other than the cdr_deltatab and sys entries.

admin:

admin:show version active

Active Master Version: 8.5.1.10000-7

Active Version Installed Software Options:

No Installed Software Options Found.

admin:

admin:show tech systables

-------------------- show tech systables --------------------

System Tables

tabname

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

GL_COLLATE

GL_CTYPE

VERSION

applicationuser

applicationusertousergroupmap

cdr_deltab_000001

cdr_deltab_000002

cdr_deltab_000003

cdr_deltab_000004

cdr_deltab_000005

cdr_deltab_000006

cdr_deltab_000007

cdr_deltab_000008

cdr_deltab_000009

cdr_deltab_000010

cdr_deltab_000011

cdr_deltab_000012

cdr_deltab_000013

cdr_deltab_000014

cdr_deltab_000015

cdr_deltab_000016

cdr_deltab_000017

cdr_deltab_000018

cdr_deltab_000019

cdr_deltab_000020

cdr_deltab_000021

cdr_deltab_000022

cdr_deltab_000023

cdr_deltab_000024

cdr_deltab_000025

cdr_deltab_000026

cdr_deltab_000027

cdr_deltab_000028

cdr_deltab_000029

cdr_deltab_000030

cdr_deltab_000031

cdr_deltab_000032

cdr_deltab_000033

cdr_deltab_000034

ceraniextmapping

cercallhistory

cerccmclusterattributes

cerccmlist

cerdiscoveryengine

ceremergencynumber

cererl

cererlaudit

ceripsubnets

cerlicenses

cermanualentryphones

cerremote

cersecuritycontact

cerseedswitches

cerserver

cerservergroup

cersnmpcmtystring

cerswitchport

cerswitchportlocation

cerswitchporttableview

cersystemparameters

cerunlocatedphone

cervhmphones

dblschemaorder

functionrole

functionroletoresourcemap

functionroletousergroupmap

intradoelinstatus

offpremiseslocation

offpremiseslocationassociation

sysaggregates

sysams

sysattrtypes

sysblobs

syscasts

syschecks

syscolattribs

syscolauth

syscoldepend

syscolumns

sysconstraints

sysdefaults

sysdepend

sysdirectives

sysdistrib

sysdomains

syserrors

sysfragauth

sysfragments

sysindexes

sysindices

sysinherits

syslangauth

syslogmap

sysobjstate

sysopclasses

sysopclstr

sysprocauth

sysprocbody

sysproccolumns

sysprocedures

sysprocplan

sysreferences

sysroleauth

sysroutinelangs

sysseclabelauth

sysseclabelcomponentelements

sysseclabelcomponents

sysseclabelnames

sysseclabels

syssecpolicies

syssecpolicycomponents

syssecpolicyexemptions

syssequences

syssurrogateauth

syssynonyms

syssyntable

systabamdata

systabauth

systables

systraceclasses

systracemsgs

systrigbody

systriggers

sysusers

sysviews

sysviolations

sysxadatasources

sysxasourcetypes

sysxtddesc

sysxtdtypeauth

sysxtdtypes

typeapplication

typeresource

usergroup

admin:

- Sriram

Please rate helpful posts !

Thanks for the quick response.  That helped.  However I also did the "run sql select * from cerunlocatedphone" and it only came up with a couple of phones.  Not the 8000+ phones that show up when I run the unlocated phone report in the QUI.  Is there another command that will just show me phones in the Default ERL?  I searched through the different tables and couldnt find any.

Hello Aaron,

Did you ever find a query for phones in the default? Having the same issue.

Sorry Jay I can't remember.  I have moved off that group and no longer support IP phones.  Maybe I opened a TAC case?  I tried to go back and look at my old cases and it won't let me anymore.

To be honest I forgot I had an account still.  Chrome saved my password from 5 years ago.

Hope you find what you need.

Thanks for responding and taking a look. Thought I'd give it a shot.

Thanks Sriram! +5, I couldn't find any documented database dictionaries for CER and your post led me to a solution for a different CER sql query.  I was having trouble automating an export for ERL to switch port mapping.  The use case being if a switch is removed/replaced/decommissioned from the network there wasn't an easy way to see what ERL's were assigned.  The nightly CER Phone Tracking email alert is great to let you know if a switch is unreachable, but it doesn't specify any ERL info.  The gui allows you to manually export (ERL Membership/Switch Ports) but there are very limited scheduling functions in CER, definitely not as robust as CUCM.  The DRS process archives this info but isn't easy to browse the .tar files.  From the 'show tech systables' I was able to find the table which holds this data and setup a cron job to export with <pick your coding language of choice>.  Below is the query for ERL to switch port in case anyone finds it useful.

run sql select sw.switchid, erl.name, sw.switchports
from cerswitchport as sw
inner join cererl as erl on sw.fkerl=erl.erlid
order by sw.switchid