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_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_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_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_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_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_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_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_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_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