Inner identity in accounting logs

[Alexander Clouter]

* Jonathan Gazeley <jonathan.gazeley at bristol.ac.uk> [Wed, 21 Jan 2009 09:55:04 +0000]:
>
> Thanks for your reply.
>
Not a problem.

> I've just got round to looking at your SQL statement - I take it you've 
> had to edit your queries in dialup.conf to get it to insert some extra 
> fields? If you wouldn't mind, could you post your changes to the 
> query/queries?
>
I'll give you the scheme I use to, it will not match up with the SQL 
identically, but you should be able to work it out (I have not updated 
my SQL query recently to the schema changes I have made):
----
CREATE TABLE postauth
(
  id serial NOT NULL,
  sql_user character varying(256) NOT NULL DEFAULT ((("session_user"())::text || '@'::text) || host(inet_client_addr())),
  timegm timestamp with time zone NOT NULL,
  user_name character varying(256) NOT NULL,
  called_station_id character varying(256) NOT NULL,
  calling_station_id character varying(256) NOT NULL,
  packet_type character varying(64) NOT NULL,
  module_message character varying(256) NOT NULL,
  packet_src_ip_address inet NOT NULL,
  nas_ip_address inet NOT NULL,
  nas_identifier character varying(256) NOT NULL,
  nas_port integer,
  nas_port_type character varying(256) NOT NULL,
  nas_port_id character varying(256) NOT NULL,
  tunnel_private_group_id character varying(256) NOT NULL,
  reply_message character varying(256) NOT NULL,
  auth_type character varying(256),
  eap_type character varying(256),
  CONSTRAINT lw_postauth_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;

CREATE TABLE acct
(
  sql_user character varying(256) NOT NULL DEFAULT ((("session_user"())::text || '@'::text) || host(inet_client_addr())),
  acct_unique_session_id character(16) NOT NULL,
  start_timestamp timestamp with time zone NOT NULL,
  end_timestamp timestamp with time zone,
  acct_session_time integer,
  user_name character varying(256) NOT NULL,
  called_station_id character varying(256) NOT NULL,
  calling_station_id character varying(256) NOT NULL,
  packet_src_ip_address inet NOT NULL,
  nas_ip_address inet NOT NULL,
  nas_identifier character varying(256) NOT NULL,
  nas_port integer,
  nas_port_type character varying(256) NOT NULL,
  nas_port_id character varying(256) NOT NULL,
  acct_status_type character varying(8) NOT NULL,
  acct_terminate_cause character varying(256),
  acct_input_octets bigint,
  acct_output_octets bigint,
  acct_input_packets bigint,
  acct_output_packets bigint,
  CONSTRAINT lw_acct_pkey PRIMARY KEY (acct_unique_session_id)
)
WITHOUT OIDS;
----

As for the SQL bits (bear in mind we're all 802.1X focus'ed here) I use:
----
postauth_table = "postauth"
postauth_query = "\
INSERT INTO ${postauth_table} \
  ( timegm, user_name, called_station_id, calling_station_id, \
    packet_src_ip_address, packet_type, nas_ip_address, nas_identifier, \
    nas_port, nas_port_type, nas_port_id, tunnel_private_group_id, \
    reply_message, module_message, auth_type, eap_type ) \
VALUES \
  ( '%S', '%{User-Name}', \
    lower('%{Called-Station-Id}'), lower('%{Calling-Station-Id}'), \
    '%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}', '%{reply:Packet-Type}', \
    '%{NAS-IP-Address}', '%{NAS-Identifier}', %{%{NAS-Port}:-NULL}, \
    '%{NAS-Port-Type}', '%{NAS-Port-Id}', '%{reply:Tunnel-Private-Group-Id}', \
    '%{reply:Reply-Message}', '%{%{Module-Failure-Message}:-%{Module-Success-Message}}', \
    '%{control:Auth-Type}', '%{outer.request:EAP-Type}' )"

acct_table = "acct"
accounting_start_query = "\
INSERT INTO ${acct_table} \
  ( start_timestamp, user_name, called_station_id, \
    calling_station_id, packet_src_ip_address, nas_ip_address, nas_identifier, \
    nas_port, nas_port_type, nas_port_id, acct_status_type, \
    acct_unique_session_id ) \
VALUES \
  ( to_timestamp(%{expr:%l - %{%{Acct-Delay-Time}:-0}}), '%{User-Name}', \
    lower('%{Called-Station-Id}'), lower('%{Calling-Station-Id}'), \
    '%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}', 
'%{NAS-IP-Address}', '%{NAS-Identifier}', \
    %{%{NAS-Port}:-NULL}, '%{NAS-Port-Type}', '%{NAS-Port-Id}', 'Start', \
    '%{Acct-Unique-Session-Id}' )"

accounting_update_query = "\
UPDATE ${acct_table} SET \
  acct_session_time      = %{Acct-Session-Time}, \
  acct_input_octets      = %{%{Acct-Input-Octets}:-0}, \
  acct_output_octets     = %{%{Acct-Output-Octets}:-0}, \
  acct_input_packets     = %{%{Acct-Input-Packets}:-0}, \
  acct_output_packets    = %{%{Acct-Output-Packets}:-0} \
WHERE \
  acct_unique_session_id = '%{Acct-Unique-Session-Id}'"

accounting_stop_query = "\
UPDATE ${acct_table} SET \
  end_timestamp          = to_timestamp(%{expr:%l - %{%{Acct-Delay-Time}:-0}}), \
  acct_session_time      = %{Acct-Session-Time}, \
  acct_status_type       = 'Stop', \
  acct_terminate_cause   = '%{Acct-Terminate-Cause}', \
  acct_input_octets      = %{%{Acct-Input-Octets}:-0}, \
  acct_output_octets     = %{%{Acct-Output-Octets}:-0}, \
  acct_input_packets     = %{%{Acct-Input-Packets}:-0}, \
  acct_output_packets    = %{%{Acct-Output-Packets}:-0} \
WHERE \
  acct_unique_session_id = '%{Acct-Unique-Session-Id}'"
----

Cheers

-- 
Alexander Clouter
.sigmonster says: To teach is to learn.