def __create_ftp_events(): sql_helper.create_table( """\ CREATE TABLE reports.ftp_events ( event_id bigserial, time_stamp timestamp without time zone, session_id bigint, client_intf smallint, server_intf smallint, c_client_addr inet, s_client_addr inet, c_server_addr inet, s_server_addr inet, policy_id bigint, username text, hostname text, request_id bigint, method character(1), uri text, virus_blocker_lite_clean boolean, virus_blocker_lite_name text, virus_blocker_clean boolean, virus_blocker_name text)""", ["request_id", "event_id"], [ "policy_id", "session_id", "time_stamp", "hostname", "username", "c_client_addr", "s_server_addr", "virus_blocker_clean", "virus_blocker_lite_clean" ])
def __create_alerts_events_table( ): sql_helper.create_table("""\ CREATE TABLE reports.alerts ( time_stamp timestamp NOT NULL, description text NOT NULL, summary_text text NOT NULL, json text NOT NULL)""")
def __create_settings_changes_table( ): sql_helper.create_table("""\ CREATE TABLE reports.settings_changes ( time_stamp timestamp NOT NULL, settings_file text NOT NULL, username text NOT NULL, hostname text NOT NULL)""")
def __create_http_query_events(): sql_helper.create_table("""\ CREATE TABLE reports.http_query_events ( event_id bigserial, time_stamp timestamp without time zone, session_id bigint, client_intf smallint, server_intf smallint, c_client_addr inet, s_client_addr inet, c_server_addr inet, s_server_addr inet, c_client_port integer, s_client_port integer, c_server_port integer, s_server_port integer, policy_id bigint, username text, hostname text, request_id bigint, method character(1), uri text, term text, host text, c2s_content_length bigint, s2c_content_length bigint, s2c_content_type text)""",["request_id","event_id"],["session_id","policy_id","time_stamp"])
def __create_settings_changes_table( ): sql_helper.create_table("""\ CREATE TABLE reports.settings_changes ( time_stamp timestamp NOT NULL, settings_file text NOT NULL, username text NOT NULL, hostname text NOT NULL)""")
def __create_ftp_events(): sql_helper.create_table("""\ CREATE TABLE reports.ftp_events ( event_id bigserial, time_stamp timestamp without time zone, session_id bigint, client_intf smallint, server_intf smallint, c_client_addr inet, s_client_addr inet, c_server_addr inet, s_server_addr inet, policy_id bigint, username text, hostname text, request_id bigint, method character(1), uri text, virus_blocker_lite_clean boolean, virus_blocker_lite_name text, virus_blocker_clean boolean, virus_blocker_name text)""", ["request_id","event_id"], ["policy_id", "session_id", "time_stamp", "hostname", "username", "c_client_addr", "s_server_addr", "virus_blocker_clean", "virus_blocker_lite_clean"])
def __create_alerts_events_table( ): sql_helper.create_table("""\ CREATE TABLE reports.alerts ( time_stamp timestamp NOT NULL, description text NOT NULL, summary_text text NOT NULL, json text NOT NULL)""")
def __create_mail_msgs(): sql_helper.create_table("""\ CREATE TABLE reports.mail_msgs ( time_stamp timestamp without time zone, session_id bigint, client_intf smallint, server_intf smallint, c_client_addr inet, s_client_addr inet, c_server_addr inet, s_server_addr inet, c_client_port integer, s_client_port integer, c_server_port integer, s_server_port integer, policy_id bigint, username text, msg_id bigint, subject text, hostname text, event_id bigserial, sender text, receiver text, virus_blocker_lite_clean boolean, virus_blocker_lite_name text, virus_blocker_clean boolean, virus_blocker_name text, spam_blocker_lite_score real, spam_blocker_lite_is_spam boolean, spam_blocker_lite_tests_string text, spam_blocker_lite_action character, spam_blocker_score real, spam_blocker_is_spam boolean, spam_blocker_tests_string text, spam_blocker_action character, phish_blocker_score real, phish_blocker_is_spam boolean, phish_blocker_tests_string text, phish_blocker_action character)""", ["msg_id"], ["policy_id","time_stamp"])
def __create_directory_connector_login_events( ): sql_helper.create_table("""\ CREATE TABLE reports.directory_connector_login_events ( time_stamp timestamp without time zone, login_name text, domain text, type text, client_addr inet)""")
def __create_configuration_backup_events(): sql_helper.create_table("""\ CREATE TABLE reports.configuration_backup_events ( time_stamp timestamp without time zone, success boolean, description text, destination text, event_id bigserial)""",["event_id"],["time_stamp"])
def __create_user_table_updates_table( ): sql_helper.create_table(""" CREATE TABLE reports.user_table_updates ( username text, key text, value text, old_value text, time_stamp timestamp)""",[],["time_stamp"])
def __create_wireguard_vpn_events_table(): sql_helper.create_table( """\ CREATE TABLE reports.wireguard_vpn_events ( time_stamp timestamp without time zone, tunnel_name text, event_type text, event_id bigserial)""", ["event_id"], ["time_stamp"])
def __create_ipsec_tunnel_stats_table(): sql_helper.create_table("""\ CREATE TABLE reports.ipsec_tunnel_stats ( time_stamp timestamp without time zone, tunnel_name text, in_bytes bigint, out_bytes bigint, event_id bigserial)""",["event_id"],["time_stamp"])
def __create_tunnel_vpn_stats_table(): sql_helper.create_table("""\ CREATE TABLE reports.tunnel_vpn_stats ( time_stamp timestamp without time zone, tunnel_name text, in_bytes bigint, out_bytes bigint, event_id bigserial)""",["event_id"],["time_stamp"])
def __create_user_table_updates_table( ): sql_helper.create_table(""" CREATE TABLE reports.user_table_updates ( username text, key text, value text, old_value text, time_stamp timestamp)""",[],["time_stamp"])
def __create_http_events(): sql_helper.create_table("""\ CREATE TABLE reports.http_events ( request_id bigint NOT NULL, time_stamp timestamp NOT NULL, session_id bigint, client_intf int2, server_intf int2, c_client_addr inet, s_client_addr inet, c_server_addr inet, s_server_addr inet, c_client_port integer, s_client_port integer, c_server_port integer, s_server_port integer, policy_id int2, username text, hostname text, method character(1), uri text, host text, domain text, referer text, c2s_content_length bigint, s2c_content_length bigint, s2c_content_type text, ad_blocker_cookie_ident text, ad_blocker_action character, web_filter_reason character(1), web_filter_category text, web_filter_blocked boolean, web_filter_flagged boolean, virus_blocker_lite_clean boolean, virus_blocker_lite_name text, virus_blocker_clean boolean, virus_blocker_name text)""", ["request_id"], ["session_id", "policy_id", "time_stamp", "host", "domain", "username", "hostname", "c_client_addr", "client_intf", "server_intf", "web_filter_blocked", "web_filter_flagged", "web_filter_category", "virus_blocker_clean", "virus_blocker_lite_clean", "ad_blocker_action"]) sql_helper.drop_column('http_events','web_filter_lite_blocked') # 13.0 sql_helper.drop_column('http_events','web_filter_lite_flagged') # 13.0 sql_helper.drop_column('http_events','web_filter_lite_category') # 13.0 sql_helper.drop_column('http_events','web_filter_lite_reason') # 13.0
def __create_tunnel_vpn_events_table(): sql_helper.create_table("""\ CREATE TABLE reports.tunnel_vpn_events ( event_id bigint, time_stamp timestamp without time zone, tunnel_name text, server_address text, local_address text, event_type text)""",["event_id"])
def __create_wan_failover_action_events( ): sql_helper.create_table("""\ CREATE TABLE reports.wan_failover_action_events ( time_stamp timestamp without time zone, interface_id int, action text, os_name text, name text, event_id bigserial)""", ["event_id"], ["time_stamp"])
def __create_smtp_tarpit_events(): sql_helper.create_table("""\ CREATE TABLE reports.smtp_tarpit_events ( time_stamp timestamp without time zone, ipaddr inet, hostname text, policy_id int8, vendor_name varchar(255), event_id bigserial)""",["event_id"],["time_stamp"])
def __create_ipsec_vpn_events_table(): sql_helper.create_table("""\ CREATE TABLE reports.ipsec_vpn_events ( event_id bigint, time_stamp timestamp without time zone, local_address text, remote_address text, tunnel_description text, event_type text)""",["event_id"])
def __create_wan_failover_test_events( ): sql_helper.create_table("""\ CREATE TABLE reports.wan_failover_test_events ( time_stamp timestamp without time zone, interface_id int, name text, description text, success bool, event_id bigserial)""", ["event_id"], ["time_stamp"])
def __create_admin_logins_table(): sql_helper.create_table("""\ CREATE TABLE reports.admin_logins ( time_stamp timestamp without time zone, login text, local boolean, client_addr inet, succeeded boolean, reason char(1) )""")
def __create_device_table_updates_table( ): sql_helper.create_table(""" CREATE TABLE reports.device_table_updates ( mac_address text, key text, value text, old_value text, time_stamp timestamp)""",[],["time_stamp"]) sql_helper.add_column('device_table_updates','old_value','text') # 13.0
def __create_device_table_updates_table( ): sql_helper.create_table(""" CREATE TABLE reports.device_table_updates ( mac_address text, key text, value text, old_value text, time_stamp timestamp)""",[],["time_stamp"]) sql_helper.add_column('device_table_updates','old_value','text') # 13.0
def __create_admin_logins_table(): sql_helper.create_table("""\ CREATE TABLE reports.admin_logins ( time_stamp timestamp without time zone, login text, local boolean, client_addr inet, succeeded boolean, reason char(1) )""")
def __create_http_events(): sql_helper.create_table("""\ CREATE TABLE reports.http_events ( request_id bigint NOT NULL, time_stamp timestamp NOT NULL, session_id bigint, client_intf int2, server_intf int2, c_client_addr inet, s_client_addr inet, c_server_addr inet, s_server_addr inet, c_client_port integer, s_client_port integer, c_server_port integer, s_server_port integer, policy_id int2, username text, hostname text, method character(1), uri text, host text, domain text, referer text, c2s_content_length bigint, s2c_content_length bigint, s2c_content_type text, s2c_content_filename text, ad_blocker_cookie_ident text, ad_blocker_action character, web_filter_reason character(1), web_filter_category text, web_filter_blocked boolean, web_filter_flagged boolean, virus_blocker_lite_clean boolean, virus_blocker_lite_name text, virus_blocker_clean boolean, virus_blocker_name text)""", ["request_id"], ["session_id", "policy_id", "time_stamp", "host", "domain", "username", "hostname", "c_client_addr", "client_intf", "server_intf", "web_filter_blocked", "web_filter_flagged", "web_filter_category", "virus_blocker_clean", "virus_blocker_lite_clean", "ad_blocker_action"]) sql_helper.add_column('http_events','s2c_content_filename','text') # 13.2
def __create_openvpn_events_table(): sql_helper.create_table( """\ CREATE TABLE reports.openvpn_events ( time_stamp timestamp without time zone, remote_address inet, pool_address inet, client_name text, type text )""", [], ["time_stamp"])
def __create_wan_failover_test_events(): sql_helper.create_table( """\ CREATE TABLE reports.wan_failover_test_events ( time_stamp timestamp without time zone, interface_id int, name text, description text, success bool, event_id bigserial)""", ["event_id"], ["time_stamp"])
def __create_wan_failover_action_events(): sql_helper.create_table( """\ CREATE TABLE reports.wan_failover_action_events ( time_stamp timestamp without time zone, interface_id int, action text, os_name text, name text, event_id bigserial)""", ["event_id"], ["time_stamp"])
def __create_ipsec_vpn_events_table(): sql_helper.create_table( """\ CREATE TABLE reports.ipsec_vpn_events ( event_id bigint, time_stamp timestamp without time zone, local_address text, remote_address text, tunnel_description text, event_type text)""", ["event_id"])
def __create_directory_connector_login_events(): sql_helper.create_table("""\ CREATE TABLE reports.directory_connector_login_events ( time_stamp timestamp without time zone, login_name text, domain text, type text, client_addr inet)""") sql_helper.add_column('directory_connector_login_events', 'login_type', 'text') #rule_14.2
def __create_quotas_table( ): sql_helper.create_table(""" CREATE TABLE reports.quotas ( time_stamp timestamp, entity text, action integer, size bigint, reason text)""", [], ["time_stamp"]) sql_helper.drop_column("quotas","address") #13.0 conversion sql_helper.add_column("quotas","entity","text") #13.0 conversion
def __create_quotas_table( ): sql_helper.create_table(""" CREATE TABLE reports.quotas ( time_stamp timestamp, entity text, action integer, size bigint, reason text)""", [], ["time_stamp"]) sql_helper.drop_column("quotas","address") #13.0 conversion sql_helper.add_column("quotas","entity","text") #13.0 conversion
def __make_captive_portal_user_events_table(): sql_helper.create_table( """\ CREATE TABLE reports.captive_portal_user_events ( time_stamp timestamp without time zone, policy_id bigint, event_id bigserial, login_name text, event_info text, auth_type text, client_addr text)""", ["event_id"], ["time_stamp"])
def __create_interface_stat_events_table(): sql_helper.create_table("""\ CREATE TABLE reports.interface_stat_events ( time_stamp TIMESTAMP, interface_id INT, rx_rate float8, rx_bytes int8, tx_rate float8, tx_bytes int8)""") sql_helper.add_column('interface_stat_events','rx_bytes','int8') # 13.1 sql_helper.add_column('interface_stat_events','tx_bytes','int8') # 13.1
def __create_web_cache_stats(): sql_helper.create_table("""\ CREATE TABLE reports.web_cache_stats ( time_stamp timestamp without time zone, hits bigint, misses bigint, bypasses bigint, systems bigint, hit_bytes bigint, miss_bytes bigint, event_id bigserial)""",["event_id"],["time_stamp"])
def __create_web_cache_stats(): sql_helper.create_table( """\ CREATE TABLE reports.web_cache_stats ( time_stamp timestamp without time zone, hits bigint, misses bigint, bypasses bigint, systems bigint, hit_bytes bigint, miss_bytes bigint, event_id bigserial)""", ["event_id"], ["time_stamp"])
def __create_ipsec_user_events_table(): sql_helper.create_table("""\ CREATE TABLE reports.ipsec_user_events ( event_id bigint, time_stamp timestamp without time zone, connect_stamp timestamp without time zone, goodbye_stamp timestamp without time zone, client_address text, client_protocol text, client_username text, net_process text, net_interface text, elapsed_time text, rx_bytes bigint, tx_bytes bigint)""",["event_id"])
def __create_openvpn_stats(): sql_helper.create_table( """\ CREATE TABLE reports.openvpn_stats ( time_stamp timestamp without time zone, start_time timestamp without time zone, end_time timestamp without time zone, rx_bytes bigint, tx_bytes bigint, remote_address inet, pool_address inet, remote_port integer, client_name text, event_id bigserial )""", ["event_id"], ["time_stamp"])
def __create_ipsec_user_events_table(): sql_helper.create_table( """\ CREATE TABLE reports.ipsec_user_events ( event_id bigint, time_stamp timestamp without time zone, connect_stamp timestamp without time zone, goodbye_stamp timestamp without time zone, client_address text, client_protocol text, client_username text, net_process text, net_interface text, elapsed_time text, rx_bytes bigint, tx_bytes bigint)""", ["event_id"])
def __create_server_events_table(): sql_helper.create_table("""\ CREATE TABLE reports.server_events ( time_stamp TIMESTAMP, load_1 DECIMAL(6, 2), load_5 DECIMAL(6, 2), load_15 DECIMAL(6, 2), cpu_user DECIMAL(6, 3), cpu_system DECIMAL(6, 3), mem_total INT8, mem_free INT8, disk_total INT8, disk_free INT8, swap_total INT8, swap_free INT8, active_hosts INT4)""")
def __create_intrusion_prevention_events(): sql_helper.create_table("""\ CREATE TABLE reports.intrusion_prevention_events ( time_stamp timestamp NOT NULL, sig_id int8, gen_id int8, class_id int8, source_addr inet, source_port int4, dest_addr inet, dest_port int4, protocol int4, blocked boolean, category text, classtype text, msg text)""", [], ["time_stamp"])
def __create_intrusion_prevention_events(): sql_helper.create_table("""\ CREATE TABLE reports.intrusion_prevention_events ( time_stamp timestamp NOT NULL, sig_id int8, gen_id int8, class_id int8, source_addr inet, source_port int4, dest_addr inet, dest_port int4, protocol int4, blocked boolean, category text, classtype text, msg text, rid text)""", [], ["time_stamp"]) sql_helper.add_column('intrusion_prevention_events', 'rule_id', 'text') #rule_14.2
def __create_mail_addrs(): sql_helper.create_table( """\ CREATE TABLE reports.mail_addrs ( time_stamp timestamp without time zone, session_id bigint, client_intf smallint, server_intf smallint, c_client_addr inet, s_client_addr inet, c_server_addr inet, s_server_addr inet, c_client_port integer, s_client_port integer, c_server_port integer, s_server_port integer, policy_id bigint, username text, msg_id bigint, subject text, addr text, addr_name text, addr_kind char(1), hostname text, event_id bigserial, sender text, virus_blocker_lite_clean boolean, virus_blocker_lite_name text, virus_blocker_clean boolean, virus_blocker_name text, spam_blocker_lite_score real, spam_blocker_lite_is_spam boolean, spam_blocker_lite_action character, spam_blocker_lite_tests_string text, spam_blocker_score real, spam_blocker_is_spam boolean, spam_blocker_action character, spam_blocker_tests_string text, phish_blocker_score real, phish_blocker_is_spam boolean, phish_blocker_tests_string text, phish_blocker_action character)""", ["event_id"], [ "policy_id", "time_stamp", "session_id", "hostname", "username", "c_client_addr", "s_server_addr", "addr", "addr_kind", "virus_blocker_lite_clean", "virus_blocker_clean", "spam_blocker_lite_is_spam", "spam_blocker_is_spam", "phish_blocker_is_spam" ])
def __create_http_query_events(): sql_helper.create_table( """\ CREATE TABLE reports.http_query_events ( event_id bigserial, time_stamp timestamp without time zone, session_id bigint, client_intf smallint, server_intf smallint, c_client_addr inet, s_client_addr inet, c_server_addr inet, s_server_addr inet, c_client_port integer, s_client_port integer, c_server_port integer, s_server_port integer, policy_id bigint, username text, hostname text, request_id bigint, method character(1), web_filter_reason character(1), uri text, term text, host text, c2s_content_length bigint, s2c_content_length bigint, s2c_content_type text)""", ["request_id", "event_id"], ["session_id", "policy_id", "time_stamp"]) sql_helper.add_column('http_query_events', 'blocked', 'boolean') #rule_14.2 sql_helper.add_column('http_query_events', 'flagged', 'boolean') #rule_14.2 sql_helper.add_column('http_query_events', 'web_filter_reason', 'character(1)') #rule_15.1
def __create_http_events(): sql_helper.create_table("""\ CREATE TABLE reports.http_events ( request_id bigint NOT NULL, time_stamp timestamp NOT NULL, session_id bigint, client_intf int2, server_intf int2, c_client_addr inet, s_client_addr inet, c_server_addr inet, s_server_addr inet, c_client_port integer, s_client_port integer, c_server_port integer, s_server_port integer, client_country text, client_latitude real, client_longitude real, server_country text, server_latitude real, server_longitude real, policy_id int2, username text, hostname text, method character(1), uri text, host text, domain text, referer text, c2s_content_length bigint, s2c_content_length bigint, s2c_content_type text, s2c_content_filename text, ad_blocker_cookie_ident text, ad_blocker_action character, web_filter_reason character(1), web_filter_category_id int2, web_filter_rule_id int2, web_filter_blocked boolean, web_filter_flagged boolean, virus_blocker_lite_clean boolean, virus_blocker_lite_name text, virus_blocker_clean boolean, virus_blocker_name text, threat_prevention_blocked boolean, threat_prevention_flagged boolean, threat_prevention_reason character(1), threat_prevention_rule_id integer, threat_prevention_client_reputation int2, threat_prevention_client_categories integer, threat_prevention_server_reputation int2, threat_prevention_server_categories integer)""", ["request_id"], ["session_id", "policy_id", "time_stamp", "host", "domain", "username", "hostname", "c_client_addr", "client_intf", "server_intf", "web_filter_blocked", "web_filter_flagged", "web_filter_category_id", "virus_blocker_clean", "virus_blocker_lite_clean", "threat_prevention_flagged", "threat_prevention_blocked", "ad_blocker_action"]) sql_helper.add_column('http_events','threat_prevention_reason','character') # 16.0
def __create_sessions_table( ): sql_helper.create_table("""\ CREATE TABLE reports.sessions ( session_id int8 NOT NULL, time_stamp timestamp NOT NULL, end_time timestamp, bypassed boolean, entitled boolean, protocol int2, icmp_type int2, hostname text, username text, policy_id int2, policy_rule_id int2, local_addr inet, remote_addr inet, c_client_addr inet, c_server_addr inet, c_server_port int4, c_client_port int4, s_client_addr inet, s_server_addr inet, s_server_port int4, s_client_port int4, client_intf int2, server_intf int2, client_country text, client_latitude real, client_longitude real, server_country text, server_latitude real, server_longitude real, c2p_bytes int8 default 0, p2c_bytes int8 default 0, s2p_bytes int8 default 0, p2s_bytes int8 default 0, filter_prefix text, firewall_blocked boolean, firewall_flagged boolean, firewall_rule_index integer, threat_prevention_blocked boolean, threat_prevention_flagged boolean, threat_prevention_reason character(1), threat_prevention_rule_id integer, threat_prevention_client_reputation int2, threat_prevention_client_categories integer, threat_prevention_server_reputation int2, threat_prevention_server_categories integer, application_control_lite_protocol text, application_control_lite_blocked boolean, captive_portal_blocked boolean, captive_portal_rule_index integer, application_control_application text, application_control_protochain text, application_control_category text, application_control_blocked boolean, application_control_flagged boolean, application_control_confidence integer, application_control_ruleid integer, application_control_detail text, bandwidth_control_priority integer, bandwidth_control_rule integer, ssl_inspector_ruleid integer, ssl_inspector_status text, ssl_inspector_detail text, tags text)""", ["session_id"], ["time_stamp", "hostname", "username", "policy_id", "c_client_addr", "s_server_addr", "client_intf", "server_intf", "firewall_flagged", "firewall_blocked", "threat_prevention_flagged", "threat_prevention_blocked", "application_control_application", "application_control_blocked", "application_control_flagged"])
def __create_sessions_table( ): sql_helper.create_table("""\ CREATE TABLE reports.sessions ( session_id int8 NOT NULL, time_stamp timestamp NOT NULL, end_time timestamp, bypassed boolean, entitled boolean, protocol int2, icmp_type int2, hostname text, username text, policy_id int2, policy_rule_id int2, local_addr inet, remote_addr inet, c_client_addr inet, c_server_addr inet, c_server_port int4, c_client_port int4, s_client_addr inet, s_server_addr inet, s_server_port int4, s_client_port int4, client_intf int2, server_intf int2, client_country text, client_latitude real, client_longitude real, server_country text, server_latitude real, server_longitude real, c2p_bytes int8 default 0, p2c_bytes int8 default 0, s2p_bytes int8 default 0, p2s_bytes int8 default 0, filter_prefix text, firewall_blocked boolean, firewall_flagged boolean, firewall_rule_index integer, application_control_lite_protocol text, application_control_lite_blocked boolean, captive_portal_blocked boolean, captive_portal_rule_index integer, application_control_application text, application_control_protochain text, application_control_category text, application_control_blocked boolean, application_control_flagged boolean, application_control_confidence integer, application_control_ruleid integer, application_control_detail text, bandwidth_control_priority integer, bandwidth_control_rule integer, ssl_inspector_ruleid integer, ssl_inspector_status text, ssl_inspector_detail text, tags text)""", ["session_id"], ["time_stamp", "hostname", "username", "policy_id", "c_client_addr", "s_server_addr", "client_intf", "server_intf", "firewall_flagged", "firewall_blocked", "application_control_application", "application_control_blocked", "application_control_flagged"]) sql_helper.add_column('sessions','tags','text') # 13.0