[Serusers] Accounting Script

[Andrew Fullford]

Well, I have no problem releasing the code, but there are a few
technical constraints.

	-  This is very much a test jig.  For example, it uses
	   psql via a pipe to do all database access.  That stuff is
	   layered out, so it can easily be directed at a real postgres
	   perl module, but we have some issues with perl modules so
	   tend not to use them, at least in early development.  I
	   would expect it to start groaning at around 100
	   calls/minute.  We're in a beta test at the moment and will
	   be in production within a month.  When our call volume
	   increases, we will certainly recode this in C.  However,
	   using direct database access from perl would probably give
	   an 80% of the gains, so that might be the next step.

	-  It depends on a special syslogd version.  The change
	   is simple -- the timestamp is written as "seconds since
	   1970" to msec precision instead of the human-readable
	   representation.  Unix time_t is simply more natural for this
	   kind of work.  I didn't attempt to layer the timestamp
	   processing, but it only occurs in one or two places, so
	   introducing mktime() and giving up on msecs would generalize
	   this.  I have a converted Linux syslogd.c.  I'm pretty sure
	   less than 10 lines changed.  I know I converted a FreeBSD
	   syslogd as well, but I can't find it.

	-  The script uses the database to keep state.  The first
	   INVITE inserts the call detail and the call is active (no
	   call_end time).  The first BYE with matching call_id updates
	   the call_end time.  Heuristics are then applied to match
	   each log entry from the gateway (cisco based) to the
	   database entry.  This allows detection of missed BYEs.  The
	   point is that the the database is integral in the matching
	   algorithm.  The side-benefit is that the database can be
	   used to display active calls.  The algorithm is dependent
	   on efficient database indexing.

	-  There is no particular dependency on database type except I
	   do record endpoint IPs as Postgres "inet" types, but these
	   are not searched so could as easily be "char" type.
	   A "timestamp" type of some kind is pretty much crucial.

So, I'm happy to push the code out to the group "as is".  If nothing
else, there are probably code fragments worthy of harvest.  I can also
put a bit of time into generalizing it a little before making it
available.  Opinions?

Andy
--
Andrew Fullford            Email: andy at august.net
August.Net Services, LLC      Web: www.august.net

> Date: Wed, 26 Mar 2003 08:18:44 +0100
> To: "Greg Fausak" <greg at august.net>,
> 	"'Ricardo Villa'" <ricvil at epm.net.co>, <serusers at lists.iptel.org>
> From: Michael Haberler <mah at eunet.at>
> Subject: RE: [Serusers] Accounting Script
> Cc: <sip at august.net>
> 
> --=======55A13AEF=======
> Content-Type: multipart/alternative; x-avg-checked=avg-ok-722294; boundary="=====================_54395666==.ALT"
> 
> 
> --=====================_54395666==.ALT
> Content-Type: text/plain; x-avg-checked=avg-ok-722294; charset=us-ascii; format=flowed
> Content-Transfer-Encoding: 8bit
> 
> At 18:21 25.03.2003 -0600, Greg Fausak wrote:
> 
> >My partner (Andy Fullford) wrote our code that does this.
> >
> >We turned on SER accounting, deliver via SYSLOG, and have a script that
> >scrapes all of that up and creates accounting records.
> >
> >In addition, we apply the syslog accounting output from our
> >gateway routers to verify the INVITE/BYE sequences. Finally,
> >we populate a database with the results.  We were going to
> >use radius, but it didn't quite work right for us.  I think what we have
> >cobbled together works really well.  We don't have any 'hanging' calls
> >anymore....and even if we did we would catch them with the gateway
> >accounting.
> >
> >We did not think the SER user base would be interested.  We use a postgres 
> >database and
> >this doesn't seem to be mainstream.  However, if there is interest for this
> >sort of thing perhaps we could donate it?
> 
> me as including the SER user base would be interested.
> 
> Also, we are migrating towards a Postgres shop here, so I would be very 
> interested in Postgressifying the whole thing, accounting and SER itself.
> 
> There was a post a while ago that somebody set out to do a DB interface 
> SER/Postgres; if this is under way, fine - if this project was abandoned, 
> we might chip in and do it - pleas get in touch with me
> 
> -Michael
> nic.at
> 
> >
> >Anyway, you could respond to <mailto:sip at august.net>sip at august.net and ask 
> >Andy if he can
> >hack you out a piece of it to do what you want.
> >
> >---greg
> >Greg Fausak
> >
> >-----Original Message-----
> >From: serusers-admin at iptel.org [mailto:serusers-admin at lists.iptel.org] On Behalf 
> >Of Ricardo Villa
> >Sent: Tuesday, March 25, 2003 3:53 PM
> >To: serusers at lists.iptel.org
> >Subject: [Serusers] Accounting Script
> >
> >Hi,
> >
> >Has anybody developed some sort of crude accounting script for SER that 
> >would match all BYEs with their respective INVITEs in the syslog file and 
> >say how many seconds each call lasted?
> >
> >Thanks,
> >Ricardo