Cisco Timestamps - Converting

This was so freaking confusing for me! In Java, I would convert my timestamps one way, but then, if I needed to convert them in Excel, I was 70 years off?!? WTF.

Well, here’s how you convert those timestamps to something meaningful, and why you are getting different results with different systems.

First, some terms:

Unix Epoch: number of seconds elapsed since Jan 1, 1970

NTP Epoch: number of seconds elapsed since Jan 1, 1900

I have no idea why the different Epochs, but did you notice that the difference is 70 years? YEP!!!

Okay, so Microsoft Products use the NTP Epoch (i.e. SQL Server, Excel, Access, etc) as a reference to build it’s Date objects. Unix, Macintosh, Java, and C / C++ uses the Unix Epoch as a reference for it’s date objects. Cisco uses Unix Epoch to export its timestamps.

So, basically, we have to add 25,569 days (70 years, approx) to the NTP Epoch, in order to get valid results in NTP Epoch-type systems (Excel, SQL Server, etc).

Convert a timestamp in JAVA:

public static void main(String[] args){

// declare our timestamp in seconds

long timestamp = 1198167416;

// Since timestamp is in seconds, but Java Date works in milliseconds,

// convert to milliseconds
Date mydate = new Date(timestamp*1000);

// Format how the date is displayed to us

SimpleDateFormat formatter = new SimpleDateFormat(”dd MMM yyyy HH:mm:ss”);

// Print the date to standard out.

System.out.println(formatter.format(mydate));
}

Convert a timestamp in Excel (Assuming your timestamp to convert is in column A1):

A1/86400+25569

Then, just format the cells as “Date”. So, to explain the Excel formula:

1). Divide the timestamp by the number of seconds in a day. This will give you the number of days

2). Add 25569 days to the already converted days, to take into account the difference between Unix Epoch and NTP Epoch shift.

Rightfax and Cisco integration using PRI

Rightfax, a Captaris product that centralizes Desktop Faxing and Electronic Document Delivery. I purchased Rightfax to help my company integrating Fax solutions, and save on maintenance and labor costs associated with manual faxing.

I purchased Rightfax with a digital PRI Brooktrout board (specifically a TR1034+E4H+T1+1N). Now, this board also supports T.38. So, why did I choose to use the digital PRI? Well, up till now, I haven’t had alot of luck using the T.38 protocol (probably due to my ignorance), and my users are starting to get very frustrated. So, for the time being, I chose to use PRI.

It took some soft massaging on both the Brooktrout, as well as on the Serial interface on my Cisco 3845, but I have it working. I will start with an overview of my design, before the implementation.

Here is the corresponding configuration on my Cisco 3845:

controller T1 0/0/0
  framing esf
  clock source internal
  linecode b8zs
  pri-group timeslots 1-4,24
  description RIGHTFAX
!
! Config Snipped
!
interface Serial0/0/0:23
  no ip address
  encapsulation hdlc
  isdn switch-type primary-dms100
  isdn protocol-emulate network
  isdn incoming-voice voice
  no cdp enable
!
! Config snipped
!
dial-peer voice 6799 pots
  destination-pattern 6799
  no digit-strip
  port 0/0/0:23

Okay, so here is some specifics:

CONTROLLER T1 0/0/0

• clock source internal - I am receiving my clock source from one of my T1’s from the Telco side.  This command passes the clock source internally on the backplane, for this controller
• pri-group timeslots 1-4,24 - I only have 4 channels licensed on the PRI card for RightFax.  Thus, I only want to allocate those channels.  Channel 24 is required for the D-channel.

INTERFACE SERIAL 0/0/0:23

• isdn switch-type primary-dms100 - This will change based on your configuration.  I am only using it, since I’m using it on my other PRI’s from the telco.  Whatever you choose, you must make sure it’s matched on the Brooktrout card.
• isdn protocol-emulate network - THIS IS INCREDIBLY IMPORTANT!!  Rightfax expects to be talking to the telco, not another device.

DIAL-PEER VOICE 6799 POTS

• destination-pattern 6799 - This will change based on your dialplan.  I chose to use a specific number for testing.  Generally, you will create a pattern for your fax numbers.
• no digit-strip - If you will be using DID numbers (you probably are), then you’ll need to send the number along to RightFax.  In order to do that, we need to send the digits on, so RightFax can use them to sort out the correct Fax mailbox.
• port 0/0/0:23 - This just directs the call to the Serial interface for Rightfax.

That’s really it.  I kept the default configuration on the Brooktrout card, except for the ISDN config (Protocol Options under the Port A tab).  For the ISDN config, I just chose what I’ve configured here (i.e. B8ZS, DMS-100 switch, etc).  Oh, and you need to modify the max. DID digits.  By default, it is set to ‘0′, so I assumed that meant no limit.  No, that means 0 digits.  Change it.
Good LUCK!!!

Parsing Cisco CDR into PostgreSQL DB

Over the last 2 days, I’ve been wanting to develop a program which will report Call Metrics from my Cisco Call Manager systems. The first thing I needed to do was create a quick script that will parse the CDR’s from Cisco, and place them into a Database. More specifically, a PostgreSQL database. Personally, I’m a MySQL guy, but that’s another posting……. Technically, if you are Python-aware, it shouldn’t be very hard to change this to use MySQL, SQL, etc.

I first started out using Java to parse the data, but I needed to develop a prototype faster, so I chose Python. Basically, it goes through each CDR in a folder, parses it, places it in the database, then archives it for historical reasons.