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 Compositionselect n.dnorpattern, cfhd.dnorpattern, cfhd.datetimestamp
from numplan as n
inner join callforwardhistorydynamic as cfhd on cfhd.fknumplan=n.pkid
where n.dnorpattern = 1000
SyntaxFrom 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
Outputdnorpattern 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!
InterpretationFirst 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.
- Launch Microsoft Excel.
- In cell A1 type the datetimestamp from column 3.
- In cell A2, paste the formula =A1/86400+DATE(1970,1,1)
- Right-click on cell A2 and select format cells.
- Under the Number tab select Time where the format is 3/14/98 1:30 PM.
- 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!