09-14-2010 01:07 PM - edited 03-13-2019 07:08 PM
Does anyone know a sql query I can run in the CLI to return all unlocated phones? I am supporting a customer with a large amount of unlocated phones, but the GUI doesn't offer a way to export to a .csv or list more than 20 per page. Any help is appreciated!
Thanks!
09-17-2010 12:20 PM
What is the version of CER you're running? There is no version 2.4.
09-17-2010 02:04 PM
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!
09-29-2010 12:50 PM
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
07-08-2011 12:25 PM
How do you get the list of tables?
07-08-2011 01:33 PM
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 !
07-08-2011 02:08 PM
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.
06-08-2016 08:33 AM
Hello Aaron,
Did you ever find a query for phones in the default? Having the same issue.
06-08-2016 10:38 AM
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.
06-08-2016 02:13 PM
Thanks for responding and taking a look. Thought I'd give it a shot.
11-05-2018 08:41 AM
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
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide