(RADIATOR) Sending Accounting details to a postgres database
Toomas Kärner
tomkar at estpak.ee
Fri Jan 21 04:51:38 CST 2005
Hi,
Alternative that I use in some cases is to do a quite interesting query's
for accounting (in that case MySQL):
AcctSQLStatement insert into wnsession set \
SESSION_ID=if(strcmp('%{Acct-Session-Id}',''),'%{Acct-Session-Id}',null),\
Location=if(strcmp('%{Location}',''),'%{Location}',null),\
NETWORK='Local', \
MAC_ADDRESS='%{Mac}', \
ACLIN=if(strcmp('%{In-Filter-Id}',''),'%{In-Filter-Id}',null),\
ACLOUT=if(strcmp('%{Out-Filter-Id}',''),'%{Out-Filter-Id}',null)
and so on. It basically compares it with empty string and based on result
inserts 'value' or NULL.
I actually use that in a SessionDB module for some interesting reasons.
Rgds.
Toomas
----- Original Message -----
From: <dthaba at uunet.co.ke>
To: <radiator at open.com.au>
Sent: Thursday, January 20, 2005 7:45 PM
Subject: (RADIATOR) Sending Accounting details to a postgres database
> Hi there,
>
> I have installed Raditator 3.11 and am trying to send some accounting
> details to a postgres database.
> The problem is that all non-integer values appear as blank in the insert
> query. What could I be missing.
> Attached find my config file as well as level 4 trace.
>
>
>
##############################################radius.cfg####################
#################################
>
> LogDir /var/log/radius
> DbDir /usr/local/radiator/db
> AuthPort 1812
> AcctPort 1813
> Trace 4
> LogFile %L/voip-cdr-logfile.%Y%m%d
>
> # For testing: this allows us to honour requests from radpwtst
> # on the same host.
> <Client localhost>
> Secret mysecret
> DupInterval 0
> </Client>
>
> <Client 195.202.80.91>
> Secret mysecret
> DupInterval 0
> </Client>
>
>
> <AuthBy SQL>
> DBSource
dbi:Pg:dbname=voip_cdr_accounting;host=195.202.80.89
> DBUsername voipradius
> DBAuth voipradius
>
> Identifier Voip_Cdr_Accounting
> AuthSelect
> AccountingTable VOIP_CDR_ACCOUNTING
> AcctColumnDef NASIPADDRESS,NAS-IP-Address
> AcctColumnDef QUINTUMNASPORT,Quintum-NAS-Port
> AcctColumnDef NASPORTTYPE,NAS-Port-Type
> AcctColumnDef USERNAME,User-Name
> AcctColumnDef CALLEDSTATIONID,Called-Station-Id
> AcctColumnDef CALLINGSTATIONID,Calling-Station-Id
> AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
> AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer
> AcctColumnDef ACCTINPUTOCTES,Acct-Input-Octets,integer
> AcctColumnDef ACCTSESSIONID,Acct-Session-Id
> AcctColumnDef ACCTSESSIONTIME,Acct-Session-Time,integer
> AcctColumnDef ACCTINPUTPACKETS,Acct-Input-Packets,integer
> AcctColumnDef ACCTOUTPUTACKETS,Acct-Input-Packets,integer
> AcctColumnDef SERVICETYPE,Service-Type
> AcctColumnDef quintum_h323_conf_id,Quintum-h323-conf-id
> AcctColumnDef quintum_avpair,Quintum-AVPair
> AcctColumnDef quintum_h323_gw_id,Quintum-h323-gw-id
> AcctColumnDef
quintum_h323_call_origin,Quintum-h323-call-origin
> AcctColumnDef
quintum_h323_call_type,Quintum-h323-call-type
> AcctColumnDef
quintum_h323_setup_time,Quintum-h323-setup-time
> AcctColumnDef
quintum_h323_connect_time,Quintum-h323-connect-time
> AcctColumnDef
quintum_h323_disconnect_time,Quintum-h323-disconnect-time
> AcctColumnDef
quintum_h323_disconnect_cause,Quintum-h323-disconnect-cause
> AcctColumnDef
quintum_h323_voice_quality,Quintum-h323-voice-quality
> AcctColumnDef timestamp,Timestamp,integer
> </AuthBy>
>
> #<Realm DEFAULT>
> # <AuthBy FILE>
> # # The filename defaults to %D/users
> # </AuthBy>
> # # Log accounting to the detail file in LogDir
> # AcctLogFileName %L/detail/voip-cdr-detail.%Y%m%d
> #</Realm>
> <Handler>
> AuthBy Voip_Cdr_Accounting
> </Handler>
>
>
############################################################################
##########################
>
> Code: Accounting-Request
> Identifier: 128
> Authentic: -x<153><179><223><4>=<19>S<230>T<192><12>j<138><182>
> Attributes:
> NAS-IP-Address = 195.202.80.91
> Quintum-NAS-Port = "0 2/1/1"
> NAS-Port-Type = Async
> User-Name = "195.202.80.91"
> Called-Station-Id = "102"
> Calling-Station-Id = "6988*"
> Acct-Status-Type = Stop
> Acct-Delay-Time = 0
> Acct-Input-Octets = 0
> Acct-Output-Octets = 0
> Acct-Session-Id = "00005D44000014A0"
> Acct-Session-Time = 5
> Acct-Input-Packets = 0
> Acct-Output-Packets = 0
> Service-Type = Annex-Framed-Tunnel
> Quintum-h323-conf-id = "h323-conf-id=34316566 65633731 32313166
63313633"
> Quintum-AVPair = "h323-incoming-conf-id=34316566 65633731 32313166
63313633"
> Quintum-h323-gw-id = "h323-gw-id="
> Quintum-h323-call-origin = "h323-call-origin=answer"
> Quintum-h323-call-type = "h323-call-type=Telephony"
> Quintum-h323-setup-time = "h323-setup-time=17:37:53.945 UTC Thu
Jan 20 2005"
> Quintum-h323-connect-time = "h323-connect-time=17:38:10.205 UTC
Thu Jan 20 2005"
> Quintum-h323-disconnect-time = "h323-disconnect-time=17:38:15.070
UTC Thu Jan 20 2005"
> Quintum-h323-disconnect-cause = "h323-disconnect-cause=10"
> Quintum-h323-voice-quality = "h323-voice-quality=0"
>
> Thu Jan 20 20:38:15 2005: DEBUG: Handling request with Handler ''
> Thu Jan 20 20:38:15 2005: DEBUG: Deleting session for 195.202.80.91,
195.202.80.91,
> Thu Jan 20 20:38:15 2005: DEBUG: Handling with Radius::AuthSQL
> Thu Jan 20 20:38:15 2005: DEBUG: Handling accounting with Radius::AuthSQL
> Thu Jan 20 20:38:15 2005: DEBUG: do query is: 'insert into
VOIP_CDR_ACCOUNTING
>
(ACCTDELAYTIME,ACCTINPUTOCTES,ACCTINPUTPACKETS,ACCTOUTPUTACKETS,ACCTSESSIONI
D,ACCTSESSIONTIME,
>
ACCTSTATUSTYPE,CALLEDSTATIONID,CALLINGSTATIONID,NASIPADDRESS,NASPORTTYPE,QUI
NTUMNASPORT,
>
SERVICETYPE,USERNAME,quintum_avpair,quintum_h323_call_origin,quintum_h323_ca
ll_type,
>
quintum_h323_conf_id,quintum_h323_connect_time,quintum_h323_disconnect_cause
,
> quintum_h323_disconnect_time,quintum_h323_gw_id,quintum_h323_setup_time,
> quintum_h323_voice_quality,timestamp) values
(0,0,0,0,,5,,,,,,,,,,,,,,,,,,,1106242695)':
>
>
>
--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.
More information about the radiator
mailing list