Database Schema
From Edge Threat Management Wiki - Arista
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.
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' />
