Database Schema

From Edge Threat Management Wiki - Arista
Revision as of 04:26, 26 February 2016 by Dmorris (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigationJump to 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.

Database Tables

admin_logins

<section begin='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)

<section end='admin_logins' />


sessions

<section begin='sessions' />

Column Name Type Description
session_id bigint The session
time_stamp timestamp without time zone The time of the event
end_time timestamp without time zone The time the session ended
bypassed boolean True if the session was bypassed, false otherwise
entitled boolean True if the session is entitled to premium functionality
protocol smallint The IP protocol of session
icmp_type smallint The ICMP type of session if ICMP
hostname text The hostname
username text The username
policy_id smallint The policy
c_client_addr inet The client-side client IP address
c_server_addr inet The client-side server IP address
c_server_port integer The client-side server port
c_client_port integer The client-side client port
s_client_addr inet The server-side client IP address
s_server_addr inet The server-side server IP address
s_server_port integer The server-side server port
s_client_port integer The server-side client port
client_intf smallint The client interface
server_intf smallint The server interface
c2p_bytes bigint The number of bytes the client sent to Untangle (client-to-pipeline)
p2c_bytes bigint The number of bytes Untangle sent to client (pipeline-to-client)
s2p_bytes bigint The number of bytes the server sent to Untangle (client-to-pipeline)
p2s_bytes bigint The number of bytes Untangle sent to server (pipeline-to-client)
filter_prefix text The network filter that blocked the connection
shield_blocked boolean True if the shield blocked the session, false otherwise
firewall_blocked boolean True if Firewall blocked the session, false otherwise
firewall_flagged boolean True if Firewall flagged the session, false otherwise
firewall_rule_index integer The matching rule in Firewall (if any)
application_control_lite_protocol text The application protocol according to Application Control Lite
application_control_lite_blocked boolean True if Application Control Lite blocked the session
captive_portal_blocked boolean True if Captive Portal blocked the session
captive_portal_rule_index integer The matching rule in Captive Portal (if any)
application_control_application text The application according to Application Control
application_control_protochain text The protochain according to Application Control
application_control_category text The category according to Application Control
application_control_blocked boolean True if Application Control blocked the session
application_control_flagged boolean True if Application Control flagged the session
application_control_confidence integer True if Application Control confidence of this session's identification
application_control_ruleid integer The matching rule in Application Control (if any)
application_control_detail text The text detail from the Application Control engine
bandwidth_control_priority integer The priority given to this session
bandwidth_control_rule integer The matching rule in Bandwidth Control rule (if any)
ssl_inspector_ruleid integer The matching rule in HTTPS Inspector rule (if any)
ssl_inspector_status text The status/action of the SSL session (INSPECTED/IGNORED/BLOCKED/UNTRUSTED/ABANDONED)
ssl_inspector_detail text Additional text detail about the SSL connection (SNI, IP Address)

<section end='sessions' />


penaltybox

<section begin='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

<section end='penaltybox' />


quotas

<section begin='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

<section end='quotas' />


host_table_updates

<section begin='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

<section end='host_table_updates' />


device_table_updates

<section begin='device_table_updates' />

Column Name Type Description
mac_address text The MAC address of the device
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

<section end='device_table_updates' />


alerts

<section begin='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

<section end='alerts' />


settings_changes

<section begin='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

<section end='settings_changes' />


wan_failover_action_events

<section begin='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

<section end='wan_failover_action_events' />


wan_failover_test_events

<section begin='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

<section end='wan_failover_test_events' />


mail_msgs

<section begin='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

<section end='mail_msgs' />


mail_addrs

<section begin='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

<section end='mail_addrs' />


smtp_tarpit_events

<section begin='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

<section end='smtp_tarpit_events' />


http_events

<section begin='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
referer text The Referer URL

<section end='http_events' />


ftp_events

<section begin='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

<section end='ftp_events' />


ipsec_user_events

<section begin='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

<section end='ipsec_user_events' />


configuration_backup_events

<section begin='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
destination text The location of the backup
event_id bigint The unique event ID

<section end='configuration_backup_events' />


ipsec_tunnel_stats

<section begin='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

<section end='ipsec_tunnel_stats' />


server_events

<section begin='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
active_hosts integer The number of active hosts

<section end='server_events' />


captive_portal_user_events

<section begin='captive_portal_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

<section end='captive_portal_user_events' />


directory_connector_login_events

<section begin='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

<section end='directory_connector_login_events' />


web_cache_stats

<section begin='web_cache_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

<section end='web_cache_stats' />


http_query_events

<section begin='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

<section end='http_query_events' />


openvpn_stats

<section begin='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

<section end='openvpn_stats' />


openvpn_events

<section begin='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)

<section end='openvpn_events' />


intrusion_prevention_events

<section begin='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

<section end='intrusion_prevention_events' />


interface_stat_events

<section begin='interface_stat_events' />

Column Name Type Description
time_stamp timestamp without time zone The time of the event
interface_id integer The interface ID
rx_rate double precision The RX rate (bytes/s)
tx_rate double precision The TX rate (bytes/s)

<section end='interface_stat_events' />

Retrieved from "https://wiki.edge.arista.com/index.php?title=Database_Schema&oldid=3743"