1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
# -*- text -*-
##
## voip-postpaid.conf -- PostgreSQL configuration for H323 VoIP billingx
## (cisco_h323_db_schema.sql)
##
## $Id$
#######################################################################
# Query config: Username
#######################################################################
# This is the username that will get substituted, escaped, and added
# as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used below
# everywhere a username substitution is needed so you you can be sure
# the username passed from the client is escaped properly.
#
# Uncomment the next line, if you want the sql_user_name to mean:
#
# Use Stripped-User-Name, if it's there.
# Else use User-Name, if it's there,
# Else use hard-coded string "none" as the user name.
#
#sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-none}}"
#
sql_user_name = "%{User-Name}"
accounting {
reference = "%{tolower:type.%{Acct-Status-Type}.query}"
# Write SQL queries to a logfile. This is potentially useful for bulk inserts
# when used with the rlm_sql_null driver.
# logfile = ${logdir}/accounting.sql
type {
start {
query = "INSERT INTO ${....acct_table1}%{h323-call-type} \
(RadiusServerName, UserName, NASIPAddress, AcctTime, CalledStationId, \
CallingStationId, AcctDelayTime, h323gwid, h323callorigin, \
h323setuptime, H323ConnectTime, callid) \
VALUES(\
'${radius_server_name}', '%{SQL-User-Name}', \
'%{NAS-IP-Address}', now(), '%{Called-Station-Id}', \
'%{Calling-Station-Id}', '%{%{Acct-Delay-Time}:-0}', '%{h323-gw-id}', \
'%{h323-call-origin}', strip_dot('%{h323-setup-time}'), \
strip_dot('%{h323-connect-time}'), pick_id('%{h323-conf-id}', \
'%{call-id}'))"
}
stop {
query = "INSERT INTO $....acct_table2}%{h323-call-type} \
(RadiusServerName, UserName, NASIPAddress, AcctTime, \
AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, \
CallingStationId, AcctDelayTime, H323RemoteAddress, H323VoiceQuality, \
CiscoNASPort, h323callorigin, callid, h323connecttime, \
h323disconnectcause, h323disconnecttime, h323gwid, h323setuptime) \
VALUES(\
'${radius_server_name}', '%{SQL-User-Name}', '%{NAS-IP-Address}', \
NOW(), '%{%{Acct-Session-Time}:-0}', \
'%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Octets}:-0}', \
'%{Called-Station-Id}', '%{Calling-Station-Id}', \
'%{%{Acct-Delay-Time}:-0}', NULLIF('%{h323-remote-address}', '')::inet, \
NULLIF('%{h323-voice-quality}','')::integer, \
NULLIF('%{Cisco-NAS-Port}', ''), \
'%{h323-call-origin}', pick_id('%{h323-conf-id}', '%{call-id}'), \
strip_dot('%{h323-connect-time}'), '%{h323-disconnect-cause}', \
strip_dot('%{h323-disconnect-time}'), '%{h323-gw-id}', \
strip_dot('%{h323-setup-time}'))"
}
}
}
|
