Wednesday, October 26, 2011

CUCM forwarding destination SQL query examples

This is just a repost of William Bell's great answer to a CUCM forward destination report question at https://supportforums.cisco.com/thread/2010685 . 

It's another really great and practical example of what's possible via SQL in CUCM.  Thank you William Bell for your time and information sharing.With some minor tweaks, it provided me with what I was looking for today in moments.

There is no native report in CUCM that can retrieve this information but you can use a SQL query (ether from the command line or via SQL Query Tool). Command line example of what you seek:

admin: run sql select d.name as device, n.dnorpattern, cfd.cfadestination from device as d inner join devicenumplanmap as dmap on dmap.fkdevice = d.pkid inner join numplan as n on n.pkid=dmap.fknumplan inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid where (cfd.cfadestination != '')

If you have an interest in seeing stations that are call forwarded to voicemail AND other destinations, then the following query can be used:

admin: run sql select d.name as device, n.dnorpattern, cfd.cfadestination from device as d inner join devicenumplanmap as dmap on dmap.fkdevice = d.pkid inner join numplan as n on n.pkid=dmap.fknumplan inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid where ((cfd.cfadestination != '') or (cfd.cfaVoicemailEnabled='t'))

If you are looking for a particular number like 17035551212 as the call forward destination, then the following could be used:

admin: run sql select d.name as device, n.dnorpattern, cfd.cfadestination from device as d inner join devicenumplanmap as dmap on dmap.fkdevice = d.pkid inner join numplan as n on n.pkid=dmap.fknumplan inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid where (cfd.cfadestination = '917035551212')

(NOTE: The above assumes that the off net access code is "9")

http://www.netcraftsmen.net/resources/blogs/running-sql-queries-on-cucm-6x7x-using-axl-soap-toolkit-part-1.html


3 comments:

  1. Anonymous3:15 PM

    Thanks for the shout out. I am hosting a series on CUCM SQL queries in weekly installments on my blog: http://ucguerrilla.com.

    -William Bell
    Chesapeake NetCraftsmen
    @ucguerrilla

    ReplyDelete
  2. This query will show the history for extensions that have forward to specific extension (also included unix time convert) run sql select n.dnorpattern, cfhd.dnorpattern, DBINFO('utc_to_datetime',cfhd.datetimestamp) from numplan as n inner join callforwardhistorydynamic as cfhd on cfhd.fknumplan=n.pkid where cfhd.dnorpattern = '1000'

    ReplyDelete
    Replies
    1. Love it. Thanks for sharing.

      Delete