#!/usr/bin/perl
#
# syslog2db - Extract Cisco VoIP call information from a syslog log and 
# insert/compare/update a Postgresql database. I use this to verify that
# my radius accounting data is infact complete, and to import call data
# that is missing in case of a failure of the radius server(s).
#
# Author:       Peter Nixon <codemonkey@peternixon.net>
# Date:         2002-11-14
# Summary:      Cisco,  VoIP, syslog, database, radius
# Copyright:    2002, Peter Nixon <codemonkey@peternixon.net>
# Copy Policy:  Free to copy and distribute provided all headers are left
#               intact and no charge is made for this program.  I would
#               appreciate copies of any modifications to the script.
# URL:          http://www.peternixon.net/code/
#

$VERSION = '0.1';

# Modules we use to make things easier
use POSIX;
use DBI;
use Parse::Syslog;
# http://search.cpan.org/author/DSCHWEI/Parse-Syslog-1.01/lib/Parse/Syslog.pm
use Carp;
use Symbol;
use Time::Local;
use Date::Parse;
# http://www.perldoc.com/cpan/Date/Parse.html
#use strict;	# Errrm. That looks like effort :-)
use vars qw($VERSION);

#Unbuffered output
$| = 1;

# Program and File locations
my $FILE = "/home/peter/Documents/ipterminator/cdr/01102002-30102002.csv";


# Database Information
my $database    = "radius";
my $hostname    = "localhost";
my $port        = "3306";
my $user        = "postgres";
my $password    = "";

# Defaults
my $defaulttimezone = "EST";
my $defaultyear = 2002;



sub init {
  # initialise all data structures
}


my %months_map = (
    'Jan' => 0, 'Feb' => 1, 'Mar' => 2,
    'Apr' => 3, 'May' => 4, 'Jun' => 5,
    'Jul' => 6, 'Aug' => 7, 'Sep' => 8,
    'Oct' => 9, 'Nov' =>10, 'Dec' =>11,
    'jan' => 0, 'feb' => 1, 'mar' => 2,
    'apr' => 3, 'may' => 4, 'jun' => 5,
    'jul' => 6, 'aug' => 7, 'sep' => 8,
    'oct' => 9, 'nov' =>10, 'dec' =>11,
);

sub main {
 my $parser = Parse::Syslog->new( "$FILE"
                                , year   => 2002
                                , locale => qw(tr_TR tr_TR.UTF-8)
				, repeat => false
                                );
 while(my $sl = $parser->next) {
     	# $sl->{timestamp|host|program|pid|text}
	$text1 = $sl->{text};
	# print "$text1\n";
	&bleh;
	}
}

sub resetvars {
  # initialise all data structures
  $AcctSessionId = ""; $UserName = ""; $NasPort=""; $NasPortType="";
  $NasIPAddress = "";
  $AcctStatusType=""; $AcctSessionTime=""; $AcctInputOctets="";
  $AcctOutputOctets=""; $AcctTerminateCause=""; $ServiceType="";
  $FramedProtocol=""; $FramedIPAddress=""; $Timestamp=""; $AcctDelayTime="";
  $ConnectInfo="";
  $Called_Station_Id=""; $SQL_User_Name=""; $Cisco_NAS_Port=""; $Client_IP_Address="";
  $h323_remote_address=""; $h323_disconnect_cause=""; $h323_gw_id=""; $h323_conf_id=""; $h323_call_type="";
  $h323_disconnect_time=""; $h323_connect_time=""; $h323_setup_time="";
}

sub printmyvars {
        print "Gateway:: $gateway\nCallLegType:: $CallLegType\nSetupTime:: $SetupTime\nConnectID:: $ConnectionId\nAddress:: $PeerAddress\nDisCause:: $DisconnectCause\nConTime:: $ConnectTime\nDisconnTime:: $DisconnectTime\nTranBytes:: $TransmitBytes\nRecBytes:: $ReceiveBytes\nCOrigin:: $CallOrigin\n *************\n\n";

}
sub substitutevars {
        if ($gateway eq 'GIRNE-GW2-5300') {
                $h323_gw_id='girne-gw2-5300.'; $NasIPAddress = "212.50.53.201"; $Client_IP_Address="212.50.53.201";
        } elsif ($gateway eq 'ist-gw1-5350') {
                $h323_gw_id='istanbul-gw1-5350.'; $NasIPAddress = "212.50.52.10"; $Client_IP_Address="212.50.52.10";
        }

}
sub grabmyvars {
        $CallLegType = $_ if s/CallLegType //;
        $SetupTime = $_ if s/SetupTime //;
        $ConnectionId = $_ if s/ConnectionId //;
        $PeerAddress = $_ if s/PeerAddress //;
        $DisconnectCause = $_ if s/DisconnectCause //;
        $ConnectTime = $_ if s/ConnectTime //;
        $DisconnectTime = $_ if s/DisconnectTime //;
        $TransmitBytes = $_ if s/TransmitBytes //;
        $ReceiveBytes = $_ if s/ReceiveBytes //;
        $CallOrigin = $_ if s/CallOrigin //;
}

sub cleanoffcrap {
        chomp($flag);
        $flag =~ s/^\s+//;      # Strip leading spaces.
        $flag =~ s/\s+$//o;     # Remove trailing whitespace
}

# fast timelocal
my $str2time_last_time;
my $str2time_last_day;
my $str2time_last_month;
my $enable_year_decrement = 1; # year-increment algorithm: if in january, if december is seen, decrement
                               # year
# 0: sec, 1: min, 2: h, 3: day, 4: month, 5: year
sub str2timez($$$$$$$)
{
    my $GMT = pop @_;
    my $day_secs = $_[2]*3600+$_[1]*60+$_[0];
    if(defined $str2time_last_time) {
        if( $_[3] == $str2time_last_day and
            $_[4] == $str2time_last_month )
        {
            return $str2time_last_time + $day_secs;
        }
    }

    my $time;
    if($GMT) {
        $time = timegm(@_);
    }
    else {
        $time = timelocal(@_);
    }

    $str2time_last_time = $time - $day_secs;
    $str2time_last_day = $_[3];
    $str2time_last_month = $_[4];

    return $time;
}

sub getseconds($$)
{
	my $time1 = pop @_;
	my $time2 = pop @_;
	
	my $seconds = $time = str2time($time1) - str2time($time2);
	print "******** Seconds: $seconds\n";
	return $seconds;
}

sub bleh
{
        # Check to see if it is a Cisco Syslog message
	# StartTime;Duration;DNIS;ANI;IP Addr
	# 2002-10-01 12:21:40.000;0;90-266-42;240;212.50.53.201
            #(\d{4})-(\d{2})-(\d{2})   	# date  -- 1, 2
        $text1 =~ /^
            .*(\d+)   	# date  -- 1, 2
            (.*)              # IP Addr  -- 9
            $/x or do
        {
            carp "line not in Cisco syslog format: $str";
	exit;
        };
        my $mon = $months_map{$1};
        defined $mon or croak "unknown month $1\n";

        my $recordtype = "$8"; my $callrecord = "$9";
        print "DEBUG: $callrecord\n";

        # convert to unix time
        my $time = str2timez($5,$4,$3,$2,$mon,$defaultyear,$defaulttimezone);

	&mangle($callrecord);
}

sub blehold {
        # Check to see if it is a Cisco Syslog message
        # StartTime;Duration;DNIS;ANI;IP Addr
        # 2002-10-01 12:21:40.000;0;90-266-42;240;212.50.53.201
            #(\d{4})-(\d{2})-(\d{2})    # date  -- 1, 2
        $text1 =~ /^
            (\d+).(\d+).(\d+).(\d+)     # date  -- 1, 2
            \s
            (\d+):(\d+):(\d+)   # time  -- 3, 4, 5
            ;(\d+)              # Duration  -- 6
            (\d+).(\d+).(\d+)   # DNIS  -- 3, 4, 5
            ;(\d+)              # ANI  -- 6
            ;(.*)              # IP Addr  -- 9
            $/x or do
        {
            carp "line not in Cisco syslog format: $str";
        };

}

sub mangle($)
{
	my $callrecord = pop @_;
        print "DEBUG2: $callrecord\n\n";
	
     	&resetvars;
        # Spilt the Call record up into Items
        my @callrecordcells = split(/,/, $callrecord);

        my $args=1;
        while ($args) {
                $flag = shift(@callrecordcells);
                &cleanoffcrap;
                $_ = $flag;
                &grabmyvars;
                print "Debug: $flag\n";

                $args = 0 if (!$callrecordcells[0]);
        }
	&getseconds($ConnectTime, $DisconnectTime);


}
&main;

exit 0;
 
