As an Amazon Associate I earn from qualifying purchases.

CUCM (BE) SQL Query: Exposing Call Forwarding History

So, you're spending a perfectly good business day browsing the latest Apple rumor websites when your phone rings.  It's Anthony from "Flying Saucer Pizzeria" across town - and he's not happy.  He reports that he's been getting random calls from your company's main phone number.  The troubling part of this report is that Anthony says that he's talking to confused customers that called your company and don't know why they are talking to the best Pizza joint (555-9000) in town.  Perhaps karma?  No, not likely.  Though, pizza does sound pretty good right now!

Instead, like a good voice engineer, you reference the CDR records on your CUCM server and find out that someone has forwarded their line (1000) to the pizza shop.  Perhaps, they thought this would be funny?  You take a quick look at the DN configuration in CUCM and no longer find the evidence you wanted.  It's not call forwarded?

What to do now?  Well, it turns out that (the Cisco BE version of) CUCM maintains a list of the last five numbers to which a DN was forwarded AND when each was configured!  To my knowledge this information isn't available via the GUI, but you can get to it via a well-formed SQL query.   

The Cisco Business Edition (BE) CUCM database has a table called "callforwardhistorydynamic".  See information about the fields contained within this table at the bottom of this post.  Pay close attention to the "Description" in the Data Dictionary Table - if it begins with "Cisco BE", it will only be available in the Cisco BE version.  There are exactly 28 tables in the 8.6(1) version of the Data Dictionary that are specific to the Cisco BE version.  In the 9.1(1) CUCM Data Dictionary, the number of tables specific to Cisco BE has grown to 40!

Let's get right to the fun part! 

CUCM SQL Query Example:  Exposing Call Forwarding History


Query Composition

select n.dnorpattern, cfhd.dnorpattern, cfhd.datetimestamp
from numplan as n
inner join callforwardhistorydynamic as cfhd on cfhd.fknumplan=n.pkid
where n.dnorpattern = 1000


Syntax

From the CLI of the CUCM, enter the command below.

admin:  run sql select n.dnorpattern, cfhd.dnorpattern, cfhd.datetimestamp from numplan as n inner join callforwardhistorydynamic as cfhd on cfhd.fknumplan=n.pkid where n.dnorpattern = 1000

Output

dnorpattern dnorpattern datetimestamp
=========== =========== =============
1000        5551900     1397782752
1000        5559000     1397786930  <-
1000        5551213     1397786943
1000        5559000     1397786952  <-
1000        5551215     1397786961


This output proves it!  The DN was forwarded to the pizza joint on two different occasions!

Interpretation

First column:  The DN being targeted, in this case, "1000".
Second column:  The number to which 1000 was forwarded.
Third column:  The date and timestamp when the forwarding was done.

Translating datetimestamp

  1. Launch Microsoft Excel.
  2. In cell A1 type the datetimestamp from column 3. 
  3. In cell A2, paste the formula =A1/86400+DATE(1970,1,1)
  4. Right-click on cell A2 and select format cells.
  5. Under the Number tab select Time where the format is 3/14/98 1:30 PM.
  6. The result is the actual time in readable format (Universal Time)
1397786930 4/18/14 2:08 AM
1397786952 4/18/14 2:09 AM

 

CUCM 8.6 Data Dictionary Table:  callforwardhistorydynamic

callforwardhistorydynamic from CUCM 8.6 Data Dictionary

Additional CUCM SQL Information


CUCM SQL Series: A Series:  From UCGuerrilla.com, this is really a fantastic starting point for anyone trying to get into CUCM SQL and understand how the tables are structured.
CUCM SQL Series: Listing Call Forward All Settings:   From UCGuerrilla.com, great examples of how to query call forward all values from the database.
Running SQL Queries on CUCM 6x/7x Using AXL SOAP Toolkit Part 1:  From NetCraftsmen.net, this article has a wonderful explanation of the table structures in the CUCM database and the different ways that they relate to one another.  I can't give @ucguerrilla enough credit, his articles on CUCM SQL queries are the best on the net!

4 comments:

  1. Hi,
    I am trying to run this query on CUCM 8.6(2a)SU3.

    The table seems to be empty (even if I run): run sql select * from callforwardhistorydynamic
    I run it on the Publisher and on all my subs.

    According to the data dictionnary, could it apply only to Business Edition ?

    ReplyDelete
    Replies
    1. Vincent, thanks for your note. I actually tested this on a Business Edition 9.x CUCM. So.. you may be correct. The description in the data dictionary does mention BE, but it didn't occur to me that perhaps this table is ONLY available on BE. I'll try to find a non-BE CUCM to test this on and let you know.

      Delete
    2. As luck would have it, I chose one of 28 tables in the 8.6(1) data dictionary that appears to be specific to only the Cisco BE version. (yes, I counted) My apologies for the confusion that this caused you and thank you for pointing out the error of my ways.

      Delete
  2. David,

    Thanks for the kudos. Great points on CFWD Dynamic and also on the BE platform.

    -Bill (@ucguerrilla)

    ReplyDelete