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!