Database Schema

From UntangleWiki
Revision as of 19:09, 15 January 2016 by Kbennett (talk | contribs) (sessions)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

The global DB schema shows the tables and columns used for tracking all logged events in Untangle. These can be used to add conditions to reports and event logs and in the reporting system to create or edit reports.

sessions


Report Condition [Column Name] Type Description
Session Id [session_id] bigint The session Unique session id. (Example: 95143416767254. This number can be referenced to other events/sessions with the same number.)
Timestamp [time_stamp] timestamp without time zone The time of the event (Example: 2016-01-13 2:22:08 pm)
End Timestamp [end_time] timestamp without time zone The time the session ended (Example: 2016-01-13 2:22:08 pm)
Bypassed [bypassed] boolean True if the session was bypassed, false otherwise
filter_prefix / Filter Prefix [filter_prefix] text The network filter that blocked the connection
Protocol [protocol] smallint The IP protocol of session (Example: UDP (17), TCP (6), ICMP (1).)
Hostname [hostname] text The hostname (Internal Device name (my-pc, officecomputer, conferanceroompc))
Username [username] text The username (Username associated with the event Examples: Jason, Jben)
Policy Id [policy_id] smallint The policy (Name of rack (Policy) along with the number. Example: Marketing (5) The number and name of the policy can be found in the Policy Manager application.)
Client [c_client_addr] inet The client-side client IP address (Internal devices IP address. Example: 192.168.3.1)
Original Server [c_server_addr] inet The client-side server IP address (Server IP first used for connection. Example: 8.8.8.8)
Original Server Port [c_server_port] integer The client-side server port (Server port first used for connection. Example: 22,21,80,443)
Client Port [c_client_port] integer The client-side client port (Port used by the Client device for current session. Example: 22,21,80,443)
New Client [s_client_addr] inet The server-side client IP address (IP of the external interface used for connection)
Server [s_server_addr] inet The server-side server IP address (IP being used to connect to server. (Server can be defined as the device the connection is being made to. This can be a internal or external address.))
Server Port [s_server_port] integer The server-side server port (Port being used to connect to server. (Server can be defined as the device the connection is being made to. This can be a internal or external address.) )
New Client Port [s_client_port] integer The server-side client port (Port used for connection with Client [c_client_addr])
Client Interface [client_intf] smallint The client interface (Interface number used for connection to client device. Example: 1. This number can be found and correlated with the interface in the Config>Network> Interfaces section.)
Server Interface [server_intf] smallint The server interface (Interface number used for connection to server. Example: 1. This number can be found and correlated with the interface in the Config>Network> Interfaces section.)
From-Client Bytes [c2p_bytes] bigint The number of bytes the client sent to Untangle (client-to-pipeline) (Example: 96120)
To-Client Bytes [p2c_bytes] bigint The number of bytes Untangle sent to client (pipeline-to-client) (Example: 96120)
From-Server Bytes [s2p_bytes] bigint The number of bytes the server sent to Untangle (client-to-pipeline) (Example: 96120)
To-Server Bytes [p2s_bytes] bigint The number of bytes Untangle sent to server (pipeline-to-client)
Shield Blocked [shield_blocked] boolean True if the shield blocked the session, false otherwise
Blocked (Firewall) [firewall_blocked] boolean True if Firewall blocked the session, false otherwise
Flagged (Firewall) [firewall_flagged] boolean True if Firewall flagged the session, false otherwise
Rule Id (Firewall) [firewall_rule_index] integer The matching rule in Firewall (if any) (Example: 500004. ID can be correlated with rule in the Firewall Application. ID will be 0 if no match)
Protocol (Application Control Lite)[application_control_lite_protocol] text The application protocol according to Application Control Lite (Example: BITTORRE. Name can be correlated back to Applications in Application Control lite for more details. )
Blocked (Application Control Lite) [application_control_lite_blocked] boolean True if Application Control Lite blocked the session
Captured (Captive Portal) [captive_portal_blocked] boolean True if Captive Portal blocked the session
Rule Id (Captive Portal) [captive_portal_rule_index] integer The matching rule in Captive Portal (if any) (Example: 5000001. ID Can be correlated back to Capture rule in the Captive Portal application. )
Application (Application Control) [application_control_application] text The application according to Application Control (Example: BITTORRE. Name can be correlated back to Applications in Application Control for more details. )
ProtoChain (Application Control) [application_control_protochain] text The protochain according to Application Control ( Example: /UDP/BITTORRE)
Blocked (Application Control) [application_control_blocked] boolean True if Application Control blocked the session
Flagged (Application Control) [application_control_flagged] boolean True if Application Control flagged the session
Confidence (Application Control) [application_control_confidence] integer 100 if Application Control confidence of this session's identification 0 if not.
Rule Id (Application Control) [application_control_ruleid] integer The matching rule in Application Control (if any) (Example: 500001. ID Can be correlated back to Rule in the Application Control application.)
Detail (Application Control) [application_control_detail] text The text detail from the Application Control engine (Example: *.google.com, i.ytimg.com)
Priority (Bandwidth Control) [bandwidth_control_priority] integer The priority given to this session (Example: Very High, High, Medium, Low, Limited, Limited More, Limited Severely)
Rule (Bandwidth Control) [bandwidth_control_rule]) integer The matching rule in Bandwidth Control rule (if any) (Example: 500001. ID Can be correlated back to Rule in the Bandwidth Control application.)
Rule Id (HTTPS Inspector) [ssl_inspector_ruleid] integer The matching rule in HTTPS Inspector rule (if any) (Example: 500001. ID Can be correlated back to Rule in theHttps Inspector application.)
Status (HTTPS Inspector) [ssl_inspector_status] text The status/action of the SSL session (INSPECTED/IGNORED/BLOCKED/UNTRUSTED/ABANDONED)
Detail (HTTPS Inspector) [ssl_inspector_detail] text Additional text detail about the SSL connection (SNI, IP Address) (Example: clients4.google.com)
ICMP Type [icmp_type] smallint The ICMP type of session if ICMP (Example:ICMPV6)


openvpn_events


Column Name Type Description
time_stamp timestamp without time zone The time of the event
remote_address inet The remote IP address of the client
pool_address inet The pool IP address of the client
client_name text The name of the client
type text The type of the event (CONNECT/DISCONNECT)


openvpn_stats


Column Name Type Description
time_stamp timestamp without time zone The time of the event
start_time timestamp without time zone The time the OpenVPN session started
end_time timestamp without time zone The time the OpenVPN session ended
rx_bytes bigint The total bytes received from the client during this session
tx_bytes bigint The total bytes sent to the client during this session
remote_address inet The remote IP address of the client
pool_address inet The pool IP address of the client
remote_port integer The remote port of the client
client_name text The name of the client
event_id bigint The unique event ID


ipsec_user_events


Column Name Type Description
event_id bigint The unique event ID
time_stamp timestamp without time zone The time of the event
connect_stamp timestamp without time zone The time the connection started
goodbye_stamp timestamp without time zone The time the connection ended
client_address text The remote IP address of the client
client_protocol text The protocol the client used to connect
client_username text The username of the client
net_process text The PID of the PPP process for L2TP connections or the connection ID for Xauth connections
net_interface text The PPP interface for L2TP connections or the client interface for Xauth connections
elapsed_time text The total time the client was connected
rx_bytes bigint The number of bytes received from the client in this connection
tx_bytes bigint The number of bytes sent to the client in this connection


ipsec_tunnel_stats


Column Name Type Description
time_stamp timestamp without time zone The time of the event
tunnel_name text The name of the IPsec tunnel
in_bytes bigint The number of bytes received during this time frame
out_bytes bigint The number of bytes transmitted during this time frame
event_id bigint The unique event ID


smtp_tarpit_events


Column Name Type Description
time_stamp timestamp without time zone The time of the event
ipaddr inet The client IP address
hostname text The hostname
policy_id bigint The policy
vendor_name character varying(255) The "vendor name" of the app that logged the event
event_id bigint The unique event ID


server_events


Column Name Type Description
time_stamp timestamp without time zone The time of the event
load_1 numeric(6,2) The 1-minute CPU load
load_5 numeric(6,2) The 5-minute CPU load
load_15 numeric(6,2) The 15-minute CPU load
cpu_user numeric(6,3) The user CPU percent utilization
cpu_system numeric(6,3) The system CPU percent utilization
mem_total bigint The total bytes of memory
mem_free bigint The number of free bytes of memory
disk_total bigint The total disk size in bytes
disk_free bigint The free disk space in bytes
swap_total bigint The total swap size in bytes
swap_free bigint The free disk swap in bytes


webcache_stats


Column Name Type Description
time_stamp timestamp without time zone The time of the event
hits bigint The number of cache hits during this time frame
misses bigint The number of cache misses during this time frame
bypasses bigint The number of cache user bypasses during this time frame
systems bigint The number of cache system bypasses during this time frame
hit_bytes bigint The number of bytes saved from cache hits
miss_bytes bigint The number of bytes not saved from cache misses
event_id bigint The unique event ID


http_query_events


Column Name Type Description
event_id bigint The unique event ID
time_stamp timestamp without time zone The time of the event
session_id bigint The session
client_intf smallint The client interface
server_intf smallint The server interface
c_client_addr inet The client-side client IP address
s_client_addr inet The server-side client IP address
c_server_addr inet The client-side server IP address
s_server_addr inet The server-side server IP address
c_client_port integer The client-side client port
s_client_port integer The server-side client port
c_server_port integer The client-side server port
s_server_port integer The server-side server port
policy_id bigint The policy
username text The username
hostname text The hostname
request_id bigint The HTTP request ID
method character(1) The HTTP method
uri text The HTTP URI
term text The search term
host text The HTTP host
c2s_content_length bigint The client-to-server content length
s2c_content_length bigint The server-to-client content length
s2c_content_type text The server-to-client content type


configuration_backup_events


Column Name Type Description
time_stamp timestamp without time zone The time of the event
success boolean The result of the backup (true if the backup succeeded, false otherwise)
description text Text detail of the event
event_id bigint The unique event ID


capture_user_events


Column Name Type Description
time_stamp timestamp without time zone The time of the event
policy_id bigint The policy
event_id bigint The unique event ID
login_name text The login username
event_info text The type of event (LOGIN, FAILED, TIMEOUT, INACTIVE, USER_LOGOUT, ADMIN_LOGOUT)
auth_type text The authorization type for this event
client_addr text The remote IP address of the client


ftp_events


Column Name Type Description
event_id bigint The unique event ID
time_stamp timestamp without time zone The time of the event
session_id bigint The session
client_intf smallint The client interface
server_intf smallint The server interface
c_client_addr inet The client-side client IP address
s_client_addr inet The server-side client IP address
c_server_addr inet The client-side server IP address
s_server_addr inet The server-side server IP address
policy_id bigint The policy
username text The username
hostname text The hostname
request_id bigint The FTP request ID
method character(1) The FTP method
uri text The FTP URI
virus_blocker_lite_clean boolean The cleanliness of the file according to Virus Blocker Lite
virus_blocker_lite_name text The name of the malware according to Virus Blocker Lite
virus_blocker_clean boolean The cleanliness of the file according to Virus Blocker
virus_blocker_name text The name of the malware according to Virus Blocker


mail_addrs


Column Name Type Description
time_stamp timestamp without time zone The time of the event
session_id bigint The session
client_intf smallint The client interface
server_intf smallint The server interface
c_client_addr inet The client-side client IP address
s_client_addr inet The server-side client IP address
c_server_addr inet The client-side server IP address
s_server_addr inet The server-side server IP address
c_client_port integer The client-side client port
s_client_port integer The server-side client port
c_server_port integer The client-side server port
s_server_port integer The server-side server port
policy_id bigint The policy
username text The username
msg_id bigint The message ID
subject text The email subject
addr text The address of this event
addr_name text The name for this address
addr_kind character(1) The type for this address (F=From, T=To, C=CC, G=Envelope From, B=Envelope To, X=Unknown)
hostname text The hostname
event_id bigint The unique event ID
sender text The address of the sender
virus_blocker_lite_clean boolean The cleanliness of the file according to Virus Blocker Lite
virus_blocker_lite_name text The name of the malware according to Virus Blocker Lite
virus_blocker_clean boolean The cleanliness of the file according to Virus Blocker
virus_blocker_name text The name of the malware according to Virus Blocker
spam_blocker_lite_score real The score of the email according to Spam Blocker Lite
spam_blocker_lite_is_spam boolean The spam status of the email according to Spam Blocker Lite
spam_blocker_lite_action character(1) The action taken by Spam Blocker Lite
spam_blocker_lite_tests_string text The tess results for Spam Blocker Lite
spam_blocker_score real The score of the email according to Spam Blocker
spam_blocker_is_spam boolean The spam status of the email according to Spam Blocker
spam_blocker_action character(1) The action taken by Spam Blocker
spam_blocker_tests_string text The tess results for Spam Blocker
phish_blocker_score real The score of the email according to Phish Blocker
phish_blocker_is_spam boolean The phish status of the email according to Phish Blocker
phish_blocker_tests_string text The tess results for Phish Blocker
phish_blocker_action character(1) The action taken by Phish Blocker


mail_msgs


Column Name Type Description
time_stamp timestamp without time zone The time of the event
session_id bigint The session
client_intf smallint The client interface
server_intf smallint The server interface
c_client_addr inet The client-side client IP address
s_client_addr inet The server-side client IP address
c_server_addr inet The client-side server IP address
s_server_addr inet The server-side server IP address
c_client_port integer The client-side client port
s_client_port integer The server-side client port
c_server_port integer The client-side server port
s_server_port integer The server-side server port
policy_id bigint The policy
username text The username
msg_id bigint The message ID
subject text The email subject
hostname text The hostname
event_id bigint The unique event ID
sender text The address of the sender
receiver text The address of the receiver
virus_blocker_lite_clean boolean The cleanliness of the file according to Virus Blocker Lite
virus_blocker_lite_name text The name of the malware according to Virus Blocker Lite
virus_blocker_clean boolean The cleanliness of the file according to Virus Blocker
virus_blocker_name text The name of the malware according to Virus Blocker
spam_blocker_lite_score real The score of the email according to Spam Blocker Lite
spam_blocker_lite_is_spam boolean The spam status of the email according to Spam Blocker Lite
spam_blocker_lite_tests_string text The tess results for Spam Blocker Lite
spam_blocker_lite_action character(1) The action taken by Spam Blocker Lite
spam_blocker_score real The score of the email according to Spam Blocker
spam_blocker_is_spam boolean The spam status of the email according to Spam Blocker
spam_blocker_tests_string text The tess results for Spam Blocker
spam_blocker_action character(1) The action taken by Spam Blocker
phish_blocker_score real The score of the email according to Phish Blocker
phish_blocker_is_spam boolean The phish status of the email according to Phish Blocker
phish_blocker_tests_string text The tess results for Phish Blocker
phish_blocker_action character(1) The action taken by Phish Blocker


http_events


Column Name Type Description
request_id bigint The HTTP request ID
time_stamp timestamp without time zone The time of the event
session_id bigint The session
client_intf smallint The client interface
server_intf smallint The server interface
c_client_addr inet The client-side client IP address
s_client_addr inet The server-side client IP address
c_server_addr inet The client-side server IP address
s_server_addr inet The server-side server IP address
c_client_port integer The client-side client port
s_client_port integer The server-side client port
c_server_port integer The client-side server port
s_server_port integer The server-side server port
policy_id smallint The policy
username text The username
hostname text The hostname
method character(1) The HTTP method
uri text The HTTP URI
host text The HTTP host
domain text The HTTP domain (shortened host)
c2s_content_length bigint The client-to-server content length
s2c_content_length bigint The server-to-client content length
s2c_content_type text The server-to-client content type
ad_blocker_cookie_ident text This name of cookie blocked by Ad Blocker
ad_blocker_action character(1) This action of Ad Blocker on this request
web_filter_lite_reason character(1) This reason Web Filter Lite blocked/flagged this request
web_filter_lite_category text This category according to Web Filter Lite
web_filter_lite_blocked boolean If Web Filter Lite blocked this request
web_filter_lite_flagged boolean If Web Filter Lite flagged this request
web_filter_reason character(1) This reason Web Filter blocked/flagged this request
web_filter_category text This category according to Web Filter
web_filter_blocked boolean If Web Filter blocked this request
web_filter_flagged boolean If Web Filter flagged this request
virus_blocker_lite_clean boolean The cleanliness of the file according to Virus Blocker Lite
virus_blocker_lite_name text The name of the malware according to Virus Blocker Lite
virus_blocker_clean boolean The cleanliness of the file according to Virus Blocker
virus_blocker_name text The name of the malware according to Virus Blocker


directory_connector_login_events


Column Name Type Description
time_stamp timestamp without time zone The time of the event
login_name text The login name
domain text The AD domain
type text The type of event (I=Login,U=Update,O=Logout)
client_addr inet The client IP address


wan_failover_action_events


Column Name Type Description
time_stamp timestamp without time zone The time of the event
interface_id integer This interface ID
action text This action (CONNECTED/DISCONNECTED)
os_name text This O/S name of the interface
name text This name of the interface
event_id bigint The unique event ID


intrusion_prevention_events


Column Name Type Description
time_stamp timestamp without time zone The time of the event
sig_id bigint This ID of the rule
gen_id bigint The grouping ID for the rule, The gen_id + sig_id specify the rule's unique identifier
class_id bigint The numeric ID for the classtype
source_addr inet The source IP address of the packet
source_port integer The source port of the packet (if applicable)
dest_addr inet The destination IP address of the packet
dest_port integer The destination port of the packet (if applicable)
protocol integer The protocol of the packet
blocked boolean If the packet was blocked/dropped
category text The application specific grouping
classtype text The generalized threat rule grouping (unrelated to gen_id)
msg text The "title" or "description" of the rule


wan_failover_test_events


Column Name Type Description
time_stamp timestamp without time zone The time of the event
interface_id integer This interface ID
name text This name of the interface
description text The description from the test rule
success boolean The result of the test (true if the test succeeded, false otherwise)
event_id bigint The unique event ID


settings_changes


Column Name Type Description
time_stamp timestamp without time zone The time of the event
settings_file text The name of the file changed
username text The username logged in at the time of the change
hostname text The remote hostname


alerts


Column Name Type Description
time_stamp timestamp without time zone The time of the event
description text The description from the alert rule.
summary_text text The summary text of the alert
json text The summary JSON representation of the event causing the alert


host_table_updates


Column Name Type Description
address inet The IP address of the host
key text The key being updated
value text The new value for the key
time_stamp timestamp without time zone The time of the event


quotas


Column Name Type Description
time_stamp timestamp without time zone The time of the event
address inet The IP address of the host
action integer The action (1=Quota Given, 2=Quota Exceeded)
size bigint The size of the quota
reason text The reason for the action


penaltybox


Column Name Type Description
address inet The IP address of the host
reason text The reason for the action
start_time timestamp without time zone The time the client entered the penalty box
end_time timestamp without time zone The time the client exited the penalty box
time_stamp timestamp without time zone The time of the event


admin_logins


Column Name Type Description
time_stamp timestamp without time zone The time of the event
login text The login name
local boolean True if it is a login attempt through a local process
client_addr inet The client IP address
succeeded boolean True if the login succeeded, false otherwise
reason character(1) The reason for the login (if applicable)