Mercurial > hg > freeDiameter
view contrib/app_acct_tools/database.sql @ 533:4cdf146f11d5
Added a set of example PHP scripts to parse the app_acct.fdx data.
author | Sebastien Decugis <sdecugis@nict.go.jp> |
---|---|
date | Tue, 07 Sep 2010 16:57:48 +0900 |
parents | |
children |
line wrap: on
line source
-- database.sql -- Script to create the tables for process_records.php script to perform. -- IMPORTANT NOTE: No constraint and almost no index are created by this script. -- One should consider adding the appropriate indexes according to their utilization of the data. -- Incoming table table. -- This table is used by app_acct.fdx to store the Accounting records. CREATE TABLE incoming ( "Origin-Host" bytea NOT NULL, "Origin-Realm" bytea NOT NULL, "Destination-Realm" bytea, "Destination-Host" bytea, "Session-Id" bytea NOT NULL, "Origin-State-Id" integer, "Accounting-Record-Type" integer NOT NULL, "Accounting-Record-Number" integer NOT NULL, "User-Name" bytea, "Event-Timestamp" bytea, "Acct-Application-Id" integer, "Accounting-Sub-Session-Id" bigint, "Acct-Session-Id" bytea, "Acct-Multi-Session-Id" bytea, "Origin-AAA-Protocol" integer, "Acct-Delay-Time" integer, "NAS-Identifier" bytea, "NAS-IP-Address" bytea, "NAS-IPv6-Address" bytea, "NAS-Port" integer, "NAS-Port-Id" bytea, "NAS-Port-Type" integer, "Service-Type" integer, "Termination-Cause" integer, "Accounting-Input-Octets" bigint, "Accounting-Input-Packets" bigint, "Accounting-Output-Octets" bigint, "Accounting-Output-Packets" bigint, "Acct-Authentic" integer, "Acct-Link-Count" integer, "Acct-Session-Time" integer, "Acct-Tunnel-Connection" bytea, "Acct-Tunnel-Packets-Lost" integer, "Callback-Id" bytea, "Callback-Number" bytea, "Called-Station-Id" bytea, "Calling-Station-Id" bytea, "Connect-Info" bytea, "Originating-Line-Info" bytea, "Authorization-Lifetime" integer, "Session-Timeout" integer, "Idle-Timeout" integer, "Port-Limit" integer, "Accounting-Realtime-Required" integer, "Acct-Interim-Interval" integer, "Filter-Id" bytea, "NAS-Filter-Rule" bytea, "QoS-Filter-Rule" bytea, "Login-IP-Host" bytea, "Login-IPv6-Host" bytea, "Login-LAT-Group" bytea, "Login-LAT-Node" bytea, "Login-LAT-Port" bytea, "Login-LAT-Service" bytea, "Login-Service" integer, "Login-TCP-Port" integer, "Route-Record1" bytea, "Route-Record2" bytea, "Route-Record3" bytea, "Route-Record4" bytea, "Route-Record5" bytea, "recorded_on" timestamp with time zone NOT NULL, "recorded_serv" bytea ); -- Accounting Data. -- This is is the processed data that is used also by display_results.php CREATE TABLE processed ( user_name bytea, user_device bytea, nas_info bytea, sess_start timestamp with time zone, sess_duration interval, downl_bytes bigint, upl_bytes bigint, downl_packets bigint, upl_packets bigint ); CREATE INDEX un_index ON processed (user_name); -- Orphans table. -- This is optional, and it must match the structure of your incoming table. CREATE TABLE orphans ( "Origin-Host" bytea NOT NULL, "Origin-Realm" bytea NOT NULL, "Destination-Realm" bytea, "Destination-Host" bytea, "Session-Id" bytea NOT NULL, "Origin-State-Id" integer, "Accounting-Record-Type" integer NOT NULL, "Accounting-Record-Number" integer NOT NULL, "User-Name" bytea, "Event-Timestamp" bytea, "Acct-Application-Id" integer, "Accounting-Sub-Session-Id" bigint, "Acct-Session-Id" bytea, "Acct-Multi-Session-Id" bytea, "Origin-AAA-Protocol" integer, "Acct-Delay-Time" integer, "NAS-Identifier" bytea, "NAS-IP-Address" bytea, "NAS-IPv6-Address" bytea, "NAS-Port" integer, "NAS-Port-Id" bytea, "NAS-Port-Type" integer, "Service-Type" integer, "Termination-Cause" integer, "Accounting-Input-Octets" bigint, "Accounting-Input-Packets" bigint, "Accounting-Output-Octets" bigint, "Accounting-Output-Packets" bigint, "Acct-Authentic" integer, "Acct-Link-Count" integer, "Acct-Session-Time" integer, "Acct-Tunnel-Connection" bytea, "Acct-Tunnel-Packets-Lost" integer, "Callback-Id" bytea, "Callback-Number" bytea, "Called-Station-Id" bytea, "Calling-Station-Id" bytea, "Connect-Info" bytea, "Originating-Line-Info" bytea, "Authorization-Lifetime" integer, "Session-Timeout" integer, "Idle-Timeout" integer, "Port-Limit" integer, "Accounting-Realtime-Required" integer, "Acct-Interim-Interval" integer, "Filter-Id" bytea, "NAS-Filter-Rule" bytea, "QoS-Filter-Rule" bytea, "Login-IP-Host" bytea, "Login-IPv6-Host" bytea, "Login-LAT-Group" bytea, "Login-LAT-Node" bytea, "Login-LAT-Port" bytea, "Login-LAT-Service" bytea, "Login-Service" integer, "Login-TCP-Port" integer, "Route-Record1" bytea, "Route-Record2" bytea, "Route-Record3" bytea, "Route-Record4" bytea, "Route-Record5" bytea, "recorded_on" timestamp with time zone NOT NULL, "recorded_serv" bytea ); -- Archives table. -- This is also optional, and it must match the structure of your incoming table. CREATE TABLE archived ( "Origin-Host" bytea NOT NULL, "Origin-Realm" bytea NOT NULL, "Destination-Realm" bytea, "Destination-Host" bytea, "Session-Id" bytea NOT NULL, "Origin-State-Id" integer, "Accounting-Record-Type" integer NOT NULL, "Accounting-Record-Number" integer NOT NULL, "User-Name" bytea, "Event-Timestamp" bytea, "Acct-Application-Id" integer, "Accounting-Sub-Session-Id" bigint, "Acct-Session-Id" bytea, "Acct-Multi-Session-Id" bytea, "Origin-AAA-Protocol" integer, "Acct-Delay-Time" integer, "NAS-Identifier" bytea, "NAS-IP-Address" bytea, "NAS-IPv6-Address" bytea, "NAS-Port" integer, "NAS-Port-Id" bytea, "NAS-Port-Type" integer, "Service-Type" integer, "Termination-Cause" integer, "Accounting-Input-Octets" bigint, "Accounting-Input-Packets" bigint, "Accounting-Output-Octets" bigint, "Accounting-Output-Packets" bigint, "Acct-Authentic" integer, "Acct-Link-Count" integer, "Acct-Session-Time" integer, "Acct-Tunnel-Connection" bytea, "Acct-Tunnel-Packets-Lost" integer, "Callback-Id" bytea, "Callback-Number" bytea, "Called-Station-Id" bytea, "Calling-Station-Id" bytea, "Connect-Info" bytea, "Originating-Line-Info" bytea, "Authorization-Lifetime" integer, "Session-Timeout" integer, "Idle-Timeout" integer, "Port-Limit" integer, "Accounting-Realtime-Required" integer, "Acct-Interim-Interval" integer, "Filter-Id" bytea, "NAS-Filter-Rule" bytea, "QoS-Filter-Rule" bytea, "Login-IP-Host" bytea, "Login-IPv6-Host" bytea, "Login-LAT-Group" bytea, "Login-LAT-Node" bytea, "Login-LAT-Port" bytea, "Login-LAT-Service" bytea, "Login-Service" integer, "Login-TCP-Port" integer, "Route-Record1" bytea, "Route-Record2" bytea, "Route-Record3" bytea, "Route-Record4" bytea, "Route-Record5" bytea, "recorded_on" timestamp with time zone NOT NULL, "recorded_serv" bytea );