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