[Freeswitch-users] Script parsing a TPORT_DUMP sip log file to Mysql

[seven]

Hi all,

I wrote a ruby script. it works for me. The script is in <FS>/scripts/ 
contrib/seven/sip/.
All ideas and suggestions are welcome!

Comment in script:

Now and then we need to look at sip traces to see want happened on a  
failed call. There are lots of ways
  to monitor sip messages. However, not all of them are convinient as  
we want. Let's say a simple example:

FreeSWITCH :> originate sofia/gateways/carrier1/5550000|sofia/gateways/ 
carrier2/5550000|sofia/carrier3...

It's hard to tell what happend if the call fails. Because it's  
different sip sessions.
The idea is to group them in one super session and see what happend. I  
do this by
adding an arbitary sip header to do cross reference. And by parse the  
sip messages to
a DB we can easily show it as html. I even can build a simple graph  
based on the DB data:

http://skitch.com/seven1240/b8xj2/voip-master-idapted

You can easily add a sip header to INVITE by(I use x_interaction):
FreeSWITCH :> originate {sip_h_x_interaction=TEST0001}sofia/ 
gateways/.....

And I can get all the messages from DB:
SELECT * FROM `sip_messages` WHERE (call_id IN
         (SELECT distinct call_id FROM sip_messages WHERE  
x_interaction = 'TEST0001'))  ORDER BY created_at;

There are two aproches to get sip packets:
1) tcpdump/tshark
2) FreeSWITCH

I use the second. Note, there is no way to actually get sip messages  
from FS currently, but sofia-sip
has the ability to log all sip messages to a disk file by using  
TPORT_DUMP

And you can use this script to parse them to a DB. I know it hurt  
performance,
but we don't have tons of traffic and you know there are only 5-10  
messages for each
sip call. While we get about 1G bytes each day in the sip log, most of  
them are OPTIONS/NOTIFY etc.
I filtered them before inserting to DB, but it will be better if sofia- 
sip can filter that :)

The script will monitor the log file and parse and insert to DB in  
real time. It's written in
the Ruby on Rails framework, however, I think it can run out of Rails  
with or without
modification. But you still need ruby and rubygems if you want to use  
it.

on Ubuntu/Debian
# apt-get install ruby rubygems
# gem install mysql file-tail yaml

It's just an idea, you may like to write your own tools to parse the  
sip log file. Also
the log file need to be rotated regularly. And I think it maybe  
possible to store the log
file on a memory disk, whatever... :)




Best
-Seven.