def get_rules():
if not hmac.compare_digest(request.cookies.get("sid", ""), SECRET):
return jsonify(rules=[])
rule_type = request.args.get('type', '%').upper()
rule_target = request.args.get('target', '%').upper()
logger.info(f'Fetching {rule_target}_{rule_type} rules...')
oauth = json.loads(request.headers.get('Authorization') or '{}')
if not oauth and not dbconfig.PRIVATE_KEY:
return jsonify(success=False, message='please log in')
ctx = db.connect(oauth=oauth)
rules = db.fetch(
ctx, f"SHOW VIEWS LIKE '%_{rule_target}\_{rule_type}' IN rules")
return jsonify(rules=[{
"title":
re.sub('_(alert|violation|policy)_(query|suppression|definition)$',
'',
rule['name'],
flags=re.I),
"target":
rule['name'].split('_')[-2].upper(),
"type":
rule['name'].split('_')[-1].upper(),
"body":
replace_config_vals(rule['text']),
"results": (list(db.fetch(ctx, f"SELECT * FROM rules.{rule['name']};"))
if rule['name'].endswith("_POLICY_DEFINITION") else None),
} for rule in rules if db.is_valid_rule_name(rule['name'])])
def run_suppression_query(squelch_name):
try:
query = SUPPRESSION_QUERY.format(suppression_name=squelch_name)
return next(db.fetch(query, fix_errors=False))['number of rows updated']
except Exception:
log.info(f"{squelch_name} warning: query broken, might need 'id' column, trying 'alert:ALERT_ID'.")
query = OLD_SUPPRESSION_QUERY.format(suppression_name=squelch_name)
return next(db.fetch(query))['number of rows updated']
def handle(alert,
correlation_id,
project=PROJECT,
assignee=None,
custom_field=None):
global PROJECT
PROJECT = project
if PROJECT == '':
return "No Jira Project defined"
if URL == '':
return "No Jira URL defined."
CORRELATION_QUERY = f"""
SELECT *
FROM results.alerts
WHERE correlation_id = '{correlation_id}'
AND iff(alert:HANDLERS is null, ticket is not null, handled is not null)
ORDER BY EVENT_TIME DESC
LIMIT 1
"""
alert_id = alert['ALERT_ID']
# We check against the correlation ID for alerts in that correlation with the same ticket
correlated_results = list(
db.fetch(CORRELATION_QUERY)) if correlation_id else []
log.info(f"Discovered {len(correlated_results)} correlated results")
if len(correlated_results) > 0:
# There is a correlation with a ticket that exists, so we should append to that ticket
ticket_id = correlated_results[0]['TICKET']
try:
ticket_status = check_ticket_status(ticket_id)
except Exception:
log.error(f"Failed to get ticket status for {ticket_id}")
return
if ticket_status == 'To Do':
try:
append_to_body(ticket_id, alert)
except Exception as e:
log.error(
f"Failed to append alert {alert_id} to ticket {ticket_id}.",
e)
try:
ticket_id = create_jira_ticket(alert)
except Exception as e:
log.error(e,
f"Failed to create ticket for alert {alert_id}")
return e
else:
# There is no correlation with a ticket that exists
# Create a new ticket in JIRA for the alert
try:
ticket_id = create_jira_ticket(alert, assignee, custom_field)
except Exception as e:
log.error(e, f"Failed to create ticket for alert {alert_id}")
return e
record_ticket_id(ticket_id, alert_id)
return ticket_id
def pull_aws_data():
finished = False
offset = 0
limit = 1000000
finished = False
aws_writer = None
with open('aws_inventory.csv', 'w') as fou:
while not finished:
data = db.fetch(
f'''SELECT distinct INSTANCE:InstanceId::string as instance_id
, min(distinct case when value:"Key"='SFROLE' then value:"Value" else NULL end ) as role FROM (
SELECT distinct INSTANCE FROM SNOWALERT.BASE_DATA.EC2_INSTANCE_SNAPSHOTS_T where timestamp > dateadd(day,-30,current_timestamp)
and INSTANCE:"Tags" not like '%{{"Key":"SFROLE","Value":"XP"}}%'
and INSTANCE:"Tags" not like '%{{"Key":"SFROLE","Value":"IMS_PENDING_SHUTDOWN"}}%'
), lateral flatten(input=>INSTANCE:"Tags")
group by instance_id having ROLE != 'XP' AND ROLE != 'IMS_PENDING_SHUTDOWN' limit {limit} offset {offset}'''
)
num_results = 0
for row in data:
num_results += 1
if aws_writer is None:
aws_writer = csv.DictWriter(fou, row.keys())
aws_writer.writeheader()
aws_writer.writerow(row)
if num_results < limit:
finished = True
offset += limit
def create_metadata_table(table, cols, addition):
db.create_table(table, cols, ifnotexists=True)
db.execute(f"GRANT INSERT, SELECT ON {table} TO ROLE {SA_ROLE}")
table_names = (row['name'] for row in db.fetch(f'desc table {table}'))
if any(name == addition[0].upper() for name in table_names):
return
db.execute(f'ALTER TABLE {table} ADD COLUMN {addition[0]} {addition[1]}')
def create_rule():
if not hmac.compare_digest(request.cookies.get("sid", ""), SECRET):
return jsonify(success=False, message="bad sid", rule={})
data = request.get_json()
rule_title, rule_type, rule_target, rule_body = data['title'], data['type'], data['target'], data['body']
logger.info(f'Creating rule {rule_title}_{rule_target}_{rule_type}')
# support for full queries with comments frontend sends comments
rule_body = re.sub(r"^CREATE [^\n]+\n", "", rule_body, flags=re.I)
m = re.match(r"^ COMMENT='((?:\\'|[^'])*)'\nAS\n", rule_body)
comment, rule_body = (m.group(1), rule_body[m.span()[1]:]) if m else ('', rule_body)
comment_clause = f"\n COMMENT='{comment}'\n"
view_name = f"{rule_title}_{rule_target}_{rule_type}"
rule_body = f"CREATE OR REPLACE VIEW {RULES_SCHEMA}.{view_name} COPY GRANTS{comment_clause}AS\n{rule_body}"
try:
oauth = json.loads(request.headers.get('Authorization') or '{}')
ctx = db.connect(oauth=oauth, run_preflight_checks=False)
ctx.cursor().execute(rule_body).fetchall()
if 'body' in data and 'savedBody' in data:
data['savedBody'] = rule_body
data['results'] = (
list(db.fetch(ctx, f"SELECT * FROM {RULES_SCHEMA}.{view_name};"))
if view_name.endswith("_POLICY_DEFINITION")
else None
)
except snowflake.connector.errors.ProgrammingError as e:
return jsonify(success=False, message=e.msg, rule=data)
return jsonify(success=True, rule=data)
def run_suppression(squelch_name):
metadata = {
'QUERY_NAME': squelch_name,
'RUN_ID': RUN_ID,
'ATTEMPTS': 1,
'START_TIME': datetime.datetime.utcnow(),
'ROW_COUNT': {
'SUPPRESSED': 0
}
}
log.info(f"{squelch_name} processing...")
try:
query = VIOLATION_SUPPRESSION_QUERY.format(squelch_name=squelch_name)
num_violations_suppressed = next(
db.fetch(query))['number of rows updated']
log.info(f"{squelch_name} updated {num_violations_suppressed} rows.")
metadata['ROW_COUNT']['SUPPRESSED'] = num_violations_suppressed
db.record_metadata(metadata, table=QUERY_METADATA_TABLE)
RULE_METADATA_RECORDS.append(metadata)
except Exception as e:
db.record_metadata(metadata, table=QUERY_METADATA_TABLE, e=e)
log.error("Suppression query {squelch_name} execution failed.", e)
print(f"Suppression query {squelch_name} executed")
def main():
RUN_METADATA = {
'RUN_TYPE': 'VIOLATION SUPPRESSION',
'START_TIME': datetime.datetime.utcnow(),
'RUN_ID': RUN_ID,
}
for squelch_name in db.load_rules(VIOLATION_SQUELCH_POSTFIX):
run_suppression(squelch_name)
num_violations_passed = next(
db.fetch(SET_SUPPRESSED_FALSE))['number of rows updated']
RUN_METADATA['ROW_COUNT'] = {
'SUPPRESSED':
sum(rmr['ROW_COUNT']['SUPPRESSED'] for rmr in RULE_METADATA_RECORDS),
'PASSED':
num_violations_passed,
}
db.record_metadata(RUN_METADATA, table=RUN_METADATA_TABLE)
if CLOUDWATCH_METRICS:
log.metric('Run', 'SnowAlert', [{
'Name': 'Component',
'Value': 'Violation Suppression Runner'
}], 1)
def main(squelch_name=None):
RUN_METADATA = {
'RUN_TYPE': 'ALERT SUPPRESSION',
'START_TIME': datetime.datetime.utcnow(),
'RUN_ID': RUN_ID,
}
rules = (
db.load_rules(ALERT_SQUELCH_POSTFIX) if squelch_name is None else [squelch_name]
)
for squelch_name in rules:
run_suppressions(squelch_name)
num_rows_updated = next(db.fetch(SET_SUPPRESSED_FALSE))['number of rows updated']
log.info(
f'All suppressions done, {num_rows_updated} remaining alerts marked suppressed=FALSE.'
)
RUN_METADATA['ROW_COUNT'] = {
'PASSED': num_rows_updated,
'SUPPRESSED': sum(m['ROW_COUNT']['SUPPRESSED'] for m in METADATA_HISTORY),
}
db.record_metadata(RUN_METADATA, table=RUN_METADATA_TABLE)
try:
if CLOUDWATCH_METRICS:
log.metric(
'Run',
'SnowAlert',
[{'Name': 'Component', 'Value': 'Alert Suppression Runner'}],
1,
)
except Exception as e:
log.error("Cloudwatch metric logging failed: ", e)
def finalize(connection_name):
base_name = f'ldap_{connection_name}'
pipe = f'data.{base_name}_pipe'
table = next(
db.fetch(f"SHOW TABLES LIKE '{base_name}_connection' IN data"))
options = yaml.safe_load(table['comment'])
stage = options.get('existing_stage', f'data.{base_name}_stage')
# IAM change takes 5-15 seconds to take effect
sleep(5)
db.retry(
lambda: db.create_pipe(
name=pipe,
sql=(f"COPY INTO data.{base_name}_connection "
f"FROM (SELECT $1, $2, $3, $4,"
f" to_timestamp_ltz($5, 'mm/dd/yyyy hh24:mi:ss (UTC)'),"
f" to_timestamp_ltz($6, 'mm/dd/yyyy hh24:mi:ss (UTC)'),"
f" to_timestamp_ltz($7, 'mm/dd/yyyy hh24:mi:ss (UTC)'),"
f" to_timestamp_ltz($8, 'mm/dd/yyyy hh24:mi:ss (UTC)') "
f"FROM @{stage}/)"),
replace=True,
autoingest=True,
),
n=10,
sleep_seconds_btw_retry=1,
)
pipe_description = next(db.fetch(f'DESC PIPE {pipe}'), None)
if pipe_description is None:
return {
'newStage':
'error',
'newMessage':
f"{pipe} does not exist; please reach out to Snowflake Security for assistance.",
}
else:
sqs_arn = pipe_description['notification_channel']
return {
'newStage':
'finalized',
'newMessage':
(f"Please add this SQS Queue ARN to the bucket event notification "
f"channel for all object create events:\n\n {sqs_arn}\n\n"
f"If you'd like to backfill the table, please run\n\n ALTER PIPE {pipe} REFRESH;"
),
}
def finalize(connection_name):
base_name = f'osquery_log_{connection_name}'
table = next(
db.fetch(f"SHOW TABLES LIKE '{base_name}_connection' IN data"))
options = yaml.load(table['comment'])
stage = options.get('existing_stage', f'data.{base_name}_stage')
pipe = f'data.{base_name}_pipe'
# IAM change takes 5-15 seconds to take effect
sleep(5)
db.retry(
lambda: db.create_pipe(
name=pipe,
sql
=(f'COPY INTO data.{base_name}_connection '
f'FROM (SELECT PARSE_JSON($1), HASH($1), $1:unixTime::TIMESTAMP_LTZ(9), $1:action, '
f'$1:calendarTime, $1:columns, $1:counter, $1:epoch, $1:hostIdentifier, $1:instance_id, '
f'$1:name, $1:unixTime, $1:decorations '
f'FROM @{stage}/)'),
replace=True,
autoingest=True,
),
n=10,
sleep_seconds_btw_retry=1,
)
pipe_description = next(db.fetch(f'DESC PIPE {pipe}'), None)
if pipe_description is None:
return {
'newStage':
'error',
'newMessage':
f"{pipe} does not exist; please reach out to Snowflake Security for assistance.",
}
else:
sqs_arn = pipe_description['notification_channel']
return {
'newStage':
'finalized',
'newMessage':
(f"Please add this SQS Queue ARN to the bucket event notification "
f"channel for all object create events:\n\n {sqs_arn}\n\n"
f"If you'd like to backfill the table, please run\n\n ALTER PIPE {pipe} REFRESH;"
),
}
def main():
db.connect()
for table in db.fetch(f"show tables like '%_BASELINE' in {DATA_SCHEMA}"):
name = table['name']
comment = table['comment']
log.info(f'{name} started...')
run_baseline(name, comment)
log.info(f'{name} done.')
def get_newest_timestamp():
# check table in snowflake and get most recent timestamp
query = f"SELECT raw FROM {AGARI_TABLE} ORDER BY event_time DESC LIMIT 1"
try:
return list(db.fetch(query))[0]['RAW']['date']
except Exception:
log.error("no earlier data found")
return None
def grab_osquery_details(deployments):
osquery_schema = environ.get('SECURITY_SCHEMA')
osquery_query = db.fetch("SHOW VIEWS LIKE 'osquery_v' IN {}".format(osquery_schema))
query_text = None
for row in osquery_query:
query_text= row["text"]
query_text = query_text.split('union all')
for query in query_text:
deployments.append(re.findall('from (.*)', query)[0])
def run_baseline(name, comment):
from rpy2 import robjects as ro
try:
metadata = yaml.safe_load(comment)
assert type(metadata) is dict
source = metadata['log source']
required_values = metadata['required values']
code_location = metadata['module name']
time_filter = metadata['filter']
time_column = metadata['history']
except Exception as e:
log.error(e, f"{name} has invalid metadata: >{metadata}<, skipping")
return
os.mkdir(FORMATTED_CODE_DIRECTORY)
files = os.listdir(f'../baseline_modules/{code_location}')
shutil.copyfile("../baseline_modules/run_module.R",
f"{FORMATTED_CODE_DIRECTORY}/run_module.R")
for file in files:
print(file)
if not file.startswith('.'):
with open(f"../baseline_modules/{code_location}/{file}") as f:
r_code = f.read()
r_code = format_code(r_code, required_values)
with open(f"{FORMATTED_CODE_DIRECTORY}/{file}", 'w+') as ff:
ff.write(r_code)
with open(f"{FORMATTED_CODE_DIRECTORY}/run_module.R") as fr:
r_code = fr.read()
frame = query_log_source(source, time_filter, time_column)
ro.globalenv['input_table'] = frame
ro.r(f"setwd('./{FORMATTED_CODE_DIRECTORY}')")
output = ro.r(r_code)
output = output.to_dict()
results = unpack(output)
# Get the columns of the baseline table; find the timestamp column and pop it from the list
columns = [
row['name'] for row in db.fetch(f'desc table {DATA_SCHEMA}.{name}')
]
columns.remove('EXPORT_TIME')
try:
log.info(f"{name} generated {len(results)} rows")
db.insert(f"{DATA_SCHEMA}.{name}",
results,
columns=columns,
overwrite=True)
except Exception as e:
log.error("Failed to insert the results into the target table", e)
finally:
shutil.rmtree(f"../{FORMATTED_CODE_DIRECTORY}")
def run_suppression_query(squelch_name):
try:
query = SUPPRESSION_QUERY.format(suppression_name=squelch_name)
return next(db.fetch(query, fix_errors=False))['number of rows updated']
except Exception as e:
log.info(e, f"{squelch_name} query broken, attempting fallback")
query = OLD_SUPPRESSION_QUERY.format(suppression_name=squelch_name)
try:
result = next(db.fetch(query))
except StopIteration:
result = []
if not result:
# if neither query worked, re-raise original error
raise
return result[0]['number of rows updated']
def get_timestamp(table):
timestamp_query = f"""
SELECT loaded_on
FROM {table}
ORDER BY loaded_on DESC
LIMIT 1
"""
ts = next(db.fetch(timestamp_query), None)
return ts['LOADED_ON'] if ts else None
def create(options):
base_table = options['base_table']
groups = list(
filter(None, [g.strip() for g in options.get('groups', '').split(',')])
)
days = int(options.get('history_size_days', '30'))
return [
next(db.fetch(sql, fix_errors=False), {}).get('status')
for sql in generate_baseline_sql(base_table, groups, days)
]
def ingest(table_name, options: dict):
landing_table = f'data.{table_name}'
connection_type = options['connection_type']
aws_access_key = options.get('aws_access_key')
aws_secret_key = options.get('aws_secret_key')
source_role_arn = options.get('source_role_arn')
destination_role_name = options.get('destination_role_name')
external_id = options.get('external_id')
accounts_connection_name = options.get('accounts_connection_name', '')
if not accounts_connection_name.startswith('data.'):
accounts_connection_name = 'data.' + accounts_connection_name
ingest_of_type = {
'EC2': ec2_dispatch,
'SG': sg_dispatch,
'ELB': elb_dispatch,
'IAM': iam_dispatch,
'AMI': ami_dispatch,
}[connection_type]
if (
source_role_arn
and destination_role_name
and external_id
and accounts_connection_name
):
# get accounts list, pass list into ingest ec2
query = (
f"SELECT account_id, account_alias "
f"FROM {accounts_connection_name} "
f"WHERE created_at = ("
f" SELECT MAX(created_at)"
f" FROM {accounts_connection_name}"
f")"
)
accounts = db.fetch(query)
count = ingest_of_type(
landing_table,
accounts=accounts,
source_role_arn=source_role_arn,
destination_role_name=destination_role_name,
external_id=external_id,
)
elif aws_access_key and aws_secret_key:
count = ingest_of_type(
landing_table, aws_access_key=aws_access_key, aws_secret_key=aws_secret_key
)
log.info(f'Inserted {count} rows.')
yield count
else:
log.error()
def test_violation_tags_in_rule_tags_view(violation_queries):
test_violation_tag_row = next(
db.fetch(
"SELECT * FROM data.rule_tags WHERE tag='test-violation-tag'"))
assert test_violation_tag_row == {
'TYPE': 'QUERY',
'TARGET': 'VIOLATION',
'RULE_NAME': '_TEST1_VIOLATION_QUERY',
'RULE_ID': 'test-violation-query-id',
'TAG': 'test-violation-tag',
}
def query_log_source(source, time_filter, time_column):
cutoff = f"DATEADD(day, -{time_filter}, CURRENT_TIMESTAMP())"
query = f"SELECT * FROM {source} WHERE {time_column} > {cutoff};"
try:
data = list(db.fetch(query))
except Exception as e:
log.error("Failed to query log source: ", e)
f = pack(data)
frame = pandas.DataFrame(f)
pandas2ri.activate()
r_dataframe = pandas2ri.py2rpy(frame)
return r_dataframe
def main(connection_table=None, run_now=False):
if connection_table is not None:
# for a single table, we ignore schedule and run now
run_now = True
else:
connection_table = "%_CONNECTION"
tables = list(db.fetch(f"SHOW TABLES LIKE '{connection_table}' IN data"))
if len(tables) == 1:
connection_run(tables[0], run_now=run_now)
else:
Pool(DC_POOLSIZE).map(connection_run, tables)
def main(baseline='%_BASELINE'):
db.connect()
baseline_tables = list(
db.fetch(f"show tables like '{baseline}' in {DATA_SCHEMA}"))
for table in baseline_tables:
name = table['name']
comment = table['comment']
log.info(f'{name} started...')
if len(baseline_tables) > 1:
subprocess.call(f"python ./run.py baseline {name}", shell=True)
else:
run_baseline(name, comment)
log.info(f'{name} done.')
def create_rule():
if not hmac.compare_digest(request.cookies.get("sid", ""), SECRET):
return jsonify(success=False, message="bad sid", rule={})
data = request.get_json()
rule_title, rule_type, rule_target, rule_body = (
data['title'],
data['type'],
data['target'],
data['body'],
)
logger.info(f'Creating rule {rule_title}_{rule_target}_{rule_type}')
for name, value in CONFIG_VARS:
rule_body = rule_body.replace(f'{name}', value)
# support for full queries with comments frontend sends comments
rule_body = re.sub(r"^CREATE [^\n]+\n", "", rule_body, flags=re.I)
m = re.match(r"^ COMMENT='((?:\\'|[^'])*)'\nAS\n", rule_body)
comment, rule_body = ((m.group(1), rule_body[m.span()[1]:]) if m else
('', rule_body))
comment_clause = f"\n COMMENT='{comment}'\n"
view_name = f"rules.{rule_title}_{rule_target}_{rule_type}"
rule_body = (
f"CREATE OR REPLACE VIEW {view_name} COPY GRANTS{comment_clause}AS\n{rule_body}"
)
try:
oauth = json.loads(request.headers.get('Authorization') or '{}')
ctx = db.connect(oauth=oauth)
ctx.cursor().execute(rule_body).fetchall()
try: # errors expected, e.g. if permissions managed by future grants on schema
ctx.cursor().execute(
f"GRANT SELECT ON VIEW {view_name} TO ROLE {dbconfig.ROLE}"
).fetchall()
except Exception:
pass
if 'body' in data and 'savedBody' in data:
data['savedBody'] = replace_config_vals(rule_body)
data['results'] = (list(db.fetch(ctx, f"SELECT * FROM {view_name};"))
if view_name.endswith("_POLICY_DEFINITION") else
None)
except snowflake.connector.errors.ProgrammingError as e:
return jsonify(success=False, message=e.msg, rule=data)
return jsonify(success=True, rule=data)
def main():
reqenv = {'ZENGRC_ID', 'ZENGRC_SECRET', 'ZENGRC_URL', 'ZENGRC_TABLE'}
missingenv = reqenv - set(environ)
if missingenv:
log.fatal(f"missing env vars: {missingenv}")
print("starting")
last_time = list(db.fetch(GET_FRESH_ENTRIES_QUERY))
if len(last_time) == 0:
for e in ENDPOINTS:
process_endpoint(e)
else:
log.info("Not time to ingest ZenGRC data")
def main(connection_table="%_CONNECTION"):
for table in db.fetch(f"SHOW TABLES LIKE '{connection_table}' IN data"):
table_name = table['name']
table_comment = table['comment']
log.info(f"-- START DC {table_name} --")
try:
metadata = {'START_TIME': datetime.utcnow()}
options = yaml.load(table_comment) or {}
if 'module' in options:
module = options['module']
metadata.update({
'RUN_ID': RUN_ID,
'TYPE': module,
'LANDING_TABLE': table_name,
'INGEST_COUNT': 0
})
connector = importlib.import_module(f"connectors.{module}")
for module_option in connector.CONNECTION_OPTIONS:
name = module_option['name']
if module_option.get('secret') and name in options:
options[name] = vault.decrypt_if_encrypted(options[name])
if module_option.get('type') == 'json':
options[name] = json.loads(options[name])
if callable(getattr(connector, 'ingest', None)):
ingested = connector.ingest(table_name, options)
if isinstance(ingested, int):
metadata['INGEST_COUNT'] += ingested
elif isinstance(ingested, GeneratorType):
for n in ingested:
metadata['INGEST_COUNT'] += n
else:
metadata['INGESTED'] = ingested
db.record_metadata(metadata, table=DC_METADATA_TABLE)
except Exception as e:
log.error(f"Error loading logs into {table_name}: ", e)
db.record_metadata(metadata, table=DC_METADATA_TABLE, e=e)
log.info(f"-- END DC --")
def finalize(connection_name):
base_name = f'GITHUB_WEBHOOKS_S3_{connection_name}_EVENTS'.upper()
pipe = f'data.{base_name}_PIPE'
# IAM change takes 5-15 seconds to take effect
sleep(5)
db.retry(
lambda: db.create_pipe(
name=pipe,
sql
=(f"COPY INTO data.{base_name}_connection "
f"FROM (SELECT current_timestamp, $1, HASH($1), $1:ref, $1: before, $1:after, $1:created, $1:deleted,"
f"$1:forced, $1:base_ref, $1:compare, $1:commits, $1:head_commit,"
f"$1:repository, $1:pusher, $1:organization, $1:sender, $1:action, $1:check_run, $1:check_suite, $1:number, $1:pull_request,"
f"$1:label, $1:requested_team, $1:ref_type, $1:master_branch, $1:description, $1:pusher_type, $1:review, $1:changes, $1:comment, "
f"$1:issue, $1:id, $1:sha, $1:name, $1:target_url, $1:context, $1:state, $1:commit, $1:branches, $1:created_at, $1:updated_at, $1:assignee, "
f"$1:release, $1:membership, $1:alert, $1:scope, $1:member, $1:requested_reviewer, $1:team, $1:starred_at, $1:pages, $1:project_card, "
f"$1:build, $1:deployment_status, $1:deployment, $1:forkee, $1:milestone, $1:key, $1:project_column, $1:status, $1:avatar_url FROM @data.{base_name}_stage/)"
),
replace=True,
autoingest=True,
),
n=10,
sleep_seconds_btw_retry=1,
)
pipe_description = next(db.fetch(f'DESC PIPE {pipe}'), None)
if pipe_description is None:
return {
'newStage':
'error',
'newMessage':
f"{pipe} does not exist; please reach out to Snowflake Security for assistance.",
}
else:
sqs_arn = pipe_description['notification_channel']
return {
'newStage':
'finalized',
'newMessage':
(f"Please add this SQS Queue ARN to the bucket event notification "
f"channel for all object create events:\n\n {sqs_arn}\n\n"
f"To backfill the landing table with existing data, please run:\n\n ALTER PIPE {pipe} REFRESH;\n\n"
),
}
def ingest_agents(table_name, options):
last_export_time = next(
db.fetch(
f'SELECT MAX(export_at) as time FROM data.{table_name}'))['TIME']
timestamp = datetime.now(timezone.utc)
if (last_export_time is None
or (timestamp - last_export_time).total_seconds() > 86400):
agents = {a['uuid']: a for a in get_agent_data()}.values()
for page in groups_of(10000, agents):
db.insert(
table=f'data.{table_name}',
values=[(agent, timestamp) for agent in page],
select=db.derive_insert_select(AGENT_LANDING_TABLE),
columns=db.derive_insert_columns(AGENT_LANDING_TABLE),
)
else:
log.info('Not time to import Tenable Agents')
def ingest_agents(table_name, options):
last_export_time = next(
db.fetch(
f'SELECT MAX(export_at) as time FROM data.{table_name}'))['TIME']
timestamp = datetime.now(timezone.utc)
if (last_export_time is None
or (timestamp - last_export_time).total_seconds() > 86400):
all_agents = sorted(get_agent_data(),
key=lambda a: a.get('last_connect', 0))
unique_agents = {a['uuid']: a for a in all_agents}.values()
rows = [{'raw': ua, 'export_at': timestamp} for ua in unique_agents]
log.debug(f'inserting {len(unique_agents)} unique (by uuid) agents')
db.insert(f'data.{table_name}', rows)
return len(rows)
else:
log.info('Not time to import Tenable Agents')
return 0
def query_snowflake(query):
global writer # , lock
finished = False
offset = 0
limit = 10000000
while not finished:
num_results = 0
query_with_limit = query + " limit %s offset %s" % (limit, offset)
data = db.fetch(query_with_limit)
for row in data:
num_results += 1
# with lock:
if writer is None:
writer = csv.DictWriter(sys.stdout, row.keys())
writer.writeheader()
writer.writerow(row)
if num_results < limit:
finished = True
offset += limit
