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