def connect(connection_name, options):
table_prefix = f'aws_collect' + ('' if connection_name in ('', 'default')
else connection_name)
table_name = f'{table_prefix}_organizations_list_accounts_connection'
landing_table = f'data.{table_name}'
audit_assumer_arn = options['audit_assumer_arn']
org_account_ids = options['org_account_ids']
audit_reader_role = options['audit_reader_role']
reader_eid = options.get('reader_eid', '')
comment = yaml_dump(
module='aws_collect',
audit_assumer_arn=audit_assumer_arn,
org_account_ids=org_account_ids,
audit_reader_role=audit_reader_role,
reader_eid=reader_eid,
collect_apis='all',
)
db.create_table(name=landing_table,
cols=LANDING_TABLE_COLUMNS,
comment=comment)
db.execute(f'GRANT INSERT, SELECT ON {landing_table} TO ROLE {SA_ROLE}')
for table_postfix, cols in SUPPLEMENTARY_TABLES.items():
supp_table = f'data.{table_prefix}_{table_postfix}'
db.create_table(name=supp_table, cols=cols)
db.execute(f'GRANT INSERT, SELECT ON {supp_table} TO ROLE {SA_ROLE}')
return {
'newStage': 'finalized',
'newMessage': "AWS Collect connector tables created.",
}
def connect(connection_name, options):
base_name = f'AZURE_VM_{connection_name}'
client_id = options['client_id']
client_secret = options['client_secret']
tenant_id = options['tenant_id']
subscription_connection_name = options['subscription_connection_name']
comment = (
f'---',
f'module: azure_vm',
f'client_id: {client_id}',
f'client_secret: {client_secret}',
f'tenant_id: {tenant_id}',
f'subscription_connection_name: {subscription_connection_name}',
)
db.create_table(name=f'data.{base_name}_CONNECTION',
cols=LANDING_TABLE_COLUMNS,
comment=comment)
db.execute(
f'GRANT INSERT, SELECT ON data.{base_name}_CONNECTION TO ROLE {SA_ROLE}'
)
cols = [
('SNAPSHOT_AT', 'TIMESTAMP_LTZ'),
('RUN_ID', 'STRING(100)'),
('SUBSCRIPTION_ID', 'STRING(500)'),
('VM_INSTANCE_COUNT', 'NUMBER'),
]
create_metadata_table(AZURE_COLLECTION_METADATA, cols, cols[3])
return {
'newStage':
'finalized',
'newMessage':
'Landing and metadata tables created for collectors to populate.',
}
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 connect(connection_name, options):
base_name = f"azure_subscription_{connection_name}"
tenant_id = options['tenant_id']
client_id = options['client_id']
client_secret = options['client_secret']
comment = f'''
---
module: azure_subscription
client_id: {client_id}
tenant_id: {tenant_id}
client_secret: {client_secret}
'''
db.create_table(name=f'data.{base_name}_connection',
cols=LANDING_TABLE_COLUMNS,
comment=comment)
db.execute(
f'GRANT INSERT, SELECT ON data.{base_name}_connection TO ROLE {SA_ROLE}'
)
return {
'newStage': 'finalized',
'newMessage': 'Landing table created for collectors to populate.'
}
def create_user_table(connection_name, options):
table_name = f'data.TENABLE_SETTINGS_{connection_name}_USER_CONNECTION'
token = options['token']
secret = options['secret']
comment = f"""
---
module: tenable_settings
token: {token}
secret: {secret}
"""
db.create_table(table_name, cols=USER_LANDING_TABLE, comment=comment)
db.execute(f'GRANT INSERT, SELECT ON {table_name} TO ROLE {SA_ROLE}')
def connect(connection_name, options):
connection_type = options['connection_type']
base_name = f'gsuite_logs_{connection_name}_{connection_type}'
landing_table = f'data.{base_name}_connection'
comment = yaml_dump(module='gsuite_logs', **options)
db.create_table(
name=landing_table, cols=LANDING_TABLES_COLUMNS['login'], comment=comment
)
db.execute(f'GRANT INSERT, SELECT ON data.{base_name}_connection TO ROLE {SA_ROLE}')
return {
'newStage': 'finalized',
'newMessage': 'Landing table created for collectors to populate.',
}
def connect(connection_name, options):
table_name = f'crowdstrike_devices_{connection_name}_connection'
landing_table = f'data.{table_name}'
comment = yaml_dump(module='crowdstrike_devices', **options)
db.create_table(name=landing_table,
cols=LANDING_TABLE_COLUMNS,
comment=comment)
db.execute(f'GRANT INSERT, SELECT ON {landing_table} TO ROLE {SA_ROLE}')
return {
'newStage': 'finalized',
'newMessage': "Crowdstrike Devices ingestion table created!",
}
def connect(connection_name, options):
base_name = f"azure_subscription_{connection_name}"
comment = yaml_dump(module='azure_subscription', **options)
db.create_table(
name=f'data.{base_name}_connection', cols=LANDING_TABLE_COLUMNS, comment=comment
)
db.execute(f'GRANT INSERT, SELECT ON data.{base_name}_connection TO ROLE {SA_ROLE}')
return {
'newStage': 'finalized',
'newMessage': 'Landing table created for collectors to populate.',
}
def connect(connection_name, options):
landing_table = f'data.assetpanda_{connection_name}_connection '
comment = yaml_dump(module='assetpanda', **options)
db.create_table(name=landing_table,
cols=LANDING_TABLE_COLUMNS,
comment=comment)
db.execute(f'GRANT INSERT, SELECT ON {landing_table} TO ROLE {SA_ROLE}')
return {
'newStage': 'finalized',
'newMessage': "AssetPanda ingestion tables created!",
}
def connect(connection_name, options):
table_name = f'duo_{connection_name}'
landing_admin_table = f'data.{table_name}_admins_connection'
db.create_table(
name=landing_admin_table,
cols=LANDING_ADMIN_TABLE_COLUMNS,
comment=yaml_dump(module='duo_collect', **options),
rw_role=ROLE,
)
return {
'newStage': 'finalized',
'newMessage': "Duo ingestion admin table created!",
}
def connect(connection_name, options):
ctype = options['connection_type']
ctable = f'data.tenable_io_{connection_name}_{ctype}_connection'
cols = {
'user': USER_LANDING_TABLE,
'agent': AGENT_LANDING_TABLE,
'vuln': VULN_LANDING_TABLE,
}[ctype]
comment = yaml_dump(module='tenable_io', **options)
db.create_table(ctable, cols=cols, comment=comment)
db.execute(f'GRANT INSERT, SELECT ON {ctable} TO ROLE {SA_ROLE}')
return {
'newStage': 'finalized',
'newMessage': 'Landing table created for collectors to populate.',
}
def create_asset_table(connection_name, asset_type, columns, options):
# create the tables, based on the config type (i.e. SG, EC2, ELB)
table_name = f'aws_asset_inv_{asset_type}_{connection_name}_connection'
landing_table = f'data.{table_name}'
aws_access_key = options['aws_access_key']
aws_secret_key = options['aws_secret_key']
comment = f'''
---
module: aws_inventory
aws_access_key: {aws_access_key}
aws_secret_key: {aws_secret_key}
'''
db.create_table(name=landing_table, cols=columns, comment=comment)
db.execute(f'GRANT INSERT, SELECT ON {landing_table} TO ROLE {SA_ROLE}')
return f"AWS {asset_type} asset ingestion table created!"
def connect(connection_name, options):
landing_table_device = f'data.airwatch_devices_{connection_name}_device_connection'
landing_table_custom_attributes = (
f'data.airwatch_devices_{connection_name}_custom_attributes_connection'
)
comment = yaml_dump(module='airwatch_devices', **options)
db.create_table(
name=landing_table_device, cols=LANDING_TABLE_COLUMNS_DEVICE, comment=comment, rw_role=SA_ROLE
)
db.create_table(
name=landing_table_custom_attributes,
cols=LANDING_TABLE_COLUMNS_CUSTOM_ATTRIBUTES,
comment=comment,
rw_role=SA_ROLE
)
return {'newStage': 'finalized', 'newMessage': "Airwatch ingestion tables created!"}
def connect(connection_name, options):
table_name = f'salesforce_events_{connection_name}'
landing_log_table = f'data.{table_name}_connection'
comment = yaml_dump(module='salesforce_event_log', **options)
db.create_table(
name=landing_log_table,
cols=LANDING_TABLE_COLUMNS,
comment=comment,
stage_file_format='TYPE = JSON STRIP_OUTER_ARRAY = TRUE',
stage_copy_options='PURGE = TRUE',
)
db.execute(
f'GRANT INSERT, SELECT ON {landing_log_table} TO ROLE {SA_ROLE}')
return {
'newStage': 'finalized',
'newMessage': "Salesforce Event Log ingestion table created!",
}
def connect(connection_name, options):
landing_table_client = f'data.meraki_devices_{connection_name}_client_connection'
landing_table_device = f'data.meraki_devices_{connection_name}_device_connection'
comment = yaml_dump(module='meraki_devices', **options)
db.create_table(name=landing_table_client,
cols=LANDING_TABLE_COLUMNS_CLIENT,
comment=comment)
db.execute(
f'GRANT INSERT, SELECT ON {landing_table_client} TO ROLE {SA_ROLE}')
db.create_table(name=landing_table_device,
cols=LANDING_TABLE_COLUMNS_DEVICE,
comment=comment)
db.execute(
f'GRANT INSERT, SELECT ON {landing_table_device} TO ROLE {SA_ROLE}')
return {
'newStage': 'finalized',
'newMessage': "Meraki ingestion tables created!"
}
def connect(connection_name, options):
table_name = f'okta_{connection_name}_connection'
landing_table = f'data.{table_name}'
api_key = options['api_key']
subdomain = options['subdomain']
comment = f'''
---
module: okta
api_key: {api_key}
subdomain: {subdomain}
'''
db.create_table(name=landing_table,
cols=LANDING_TABLE_COLUMNS,
comment=comment)
db.execute(f'GRANT INSERT, SELECT ON {landing_table} TO ROLE {SA_ROLE}')
return {
'newStage': 'finalized',
'newMessage': "Okta ingestion table created!",
}
def connect(connection_name, options):
table_name = f'okta_{connection_name}'
landing_log_table = f'data.{table_name}_connection'
landing_user_table = f'data.{table_name}_users_connection'
landing_group_table = f'data.{table_name}_groups_connection'
comment = yaml_dump(module='okta', **options)
db.create_table(name=landing_log_table,
cols=LANDING_LOG_TABLE_COLUMNS,
comment=comment)
db.execute(
f'GRANT INSERT, SELECT ON {landing_log_table} TO ROLE {SA_ROLE}')
db.create_table(name=landing_user_table,
cols=LANDING_USER_TABLE_COLUMNS,
comment=comment)
db.execute(
f'GRANT INSERT, SELECT ON {landing_user_table} TO ROLE {SA_ROLE}')
db.create_table(name=landing_group_table,
cols=LANDING_GROUP_TABLE_COLUMNS,
comment=comment)
db.execute(
f'GRANT INSERT, SELECT ON {landing_group_table} TO ROLE {SA_ROLE}')
return {
'newStage': 'finalized',
'newMessage': "Okta ingestion table, user table, group table created!",
}
def connect(connection_name, options):
table_name = 'okta' + (
'' if connection_name in ('default', 'undefined') else f'_{connection_name}'
)
landing_log_table = f'data.{table_name}_system_log_connection'
landing_user_table = f'data.{table_name}_users_connection'
landing_group_table = f'data.{table_name}_groups_connection'
comment = yaml_dump(module='okta', **options)
db.create_table(
name=landing_log_table,
cols=LANDING_LOG_TABLE_COLUMNS,
comment=comment,
rw_role=SA_ROLE,
)
db.create_table(
name=landing_user_table,
cols=LANDING_USER_TABLE_COLUMNS,
comment=comment,
rw_role=SA_ROLE,
)
db.create_table(
name=landing_group_table,
cols=LANDING_GROUP_TABLE_COLUMNS,
comment=comment,
rw_role=SA_ROLE,
)
return {
'newStage': 'finalized',
'newMessage': "Okta ingestion table, user table, group table created!",
}
def connect(connection_name, options):
table_name = f'aws_accounts_{connection_name}_connection'
landing_table = f'data.{table_name}'
source_role_arn = options['source_role_arn']
destination_role_arn = options['destination_role_arn']
destination_role_external_id = options['destination_role_external_id']
comment = yaml_dump(
module='aws_accounts',
source_role_arn=source_role_arn,
destination_role_arn=destination_role_arn,
destination_role_external_id=destination_role_external_id,
)
db.create_table(name=landing_table,
cols=LANDING_TABLE_COLUMNS,
comment=comment)
db.execute(f'GRANT INSERT, SELECT ON {landing_table} TO ROLE {SA_ROLE}')
return {
'newStage': 'finalized',
'newMessage': "AWS Account ingestion table created!",
}
def create_asset_table(connection_name, asset_type, columns, options):
# create the tables, based on the config type (i.e. SG, EC2, ELB)
table_name = f'aws_asset_inv_{asset_type}_{connection_name}_connection'
landing_table = f'data.{table_name}'
comment = yaml_dump(module='aws_inventory', **options)
db.create_table(name=landing_table, cols=columns, comment=comment)
metadata_cols = [
('snapshot_at', 'TIMESTAMP_LTZ'),
('run_id', 'VARCHAR(100)'),
('account_id', 'VARCHAR(100)'),
('account_alias', 'VARCHAR(100)'),
(f'{asset_type}_count', 'NUMBER'),
('error', 'VARCHAR'),
]
create_metadata_table(table=AWS_ACCOUNTS_METADATA,
cols=metadata_cols,
addition=metadata_cols[4])
db.execute(f'GRANT INSERT, SELECT ON {landing_table} TO ROLE {SA_ROLE}')
return f"AWS {asset_type} asset ingestion table created!"
def connect(connection_name, options):
base_name = f'AWS_CONFIG_{connection_name}_EVENTS'.upper()
stage = f'data.{base_name}_STAGE'
staging_table = f'data.{base_name}_STAGING'
landing_table = f'data.{base_name}_CONNECTION'
bucket = options['bucket_name']
prefix = options.get('filter', 'AWSLogs/')
role = options['aws_role']
comment = f"""
---
module: aws_config
"""
db.create_stage(
name=stage,
url=f's3://{bucket}',
prefix=prefix,
cloud='aws',
credentials=role,
file_format=FILE_FORMAT
)
db.create_table(
name=staging_table,
cols=[
('v', 'VARIANT'),
('filename', 'STRING(200)')
]
)
db.create_table(
name=landing_table,
cols=LANDING_TABLE_COLUMNS,
comment=comment
)
stage_props = db.fetch_props(
f'DESC STAGE {stage}',
filter=('AWS_EXTERNAL_ID', 'SNOWFLAKE_IAM_USER')
)
prefix = prefix.rstrip('/')
return {
'newStage': 'created',
'newMessage': CONNECT_RESPONSE_MESSAGE.format(
role=role,
role_trust_relationship=dumps({
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"AWS": stage_props['SNOWFLAKE_IAM_USER']
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId": stage_props['AWS_EXTERNAL_ID']
}
}
}
]
}, indent=4),
role_policy=dumps({
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3:GetObject",
"s3:GetObjectVersion",
],
"Resource": f"arn:aws:s3:::{bucket}/{prefix}/*"
},
{
"Effect": "Allow",
"Action": "s3:ListBucket",
"Resource": f"arn:aws:s3:::{bucket}",
"Condition": {
"StringLike": {
"s3:prefix": [
f"{prefix}/*"
]
}
}
}
]
}, indent=4),
)
}
def connect(connection_name, options):
connection_type = options['connection_type']
base_name = f"azure_log_{connection_name}_{connection_type}"
account_name = options['account_name']
container_name = options['container_name']
suffix = options['suffix']
cloud_type = options['cloud_type']
sas_token = options['sas_token']
comment = yaml_dump(module='azure_log')
db.create_stage(
name=f'data.{base_name}_stage',
url=f"azure://{account_name}.blob.{suffix}/{container_name}",
cloud='azure',
prefix='',
credentials=sas_token,
file_format=FILE_FORMAT,
)
db.execute(
f'GRANT USAGE ON STAGE data.{base_name}_stage TO ROLE {SA_ROLE}')
db.create_table(
name=f'data.{base_name}_connection',
cols=LANDING_TABLES_COLUMNS[connection_type],
comment=comment,
ifnotexists=True,
)
db.execute(
f'GRANT INSERT, SELECT ON data.{base_name}_connection TO ROLE {SA_ROLE}'
)
external_table_columns = [(
'timestamp_part',
'TIMESTAMP_LTZ',
GET_TIMESTAMP_FROM_FILENAME_SQL[connection_type],
)]
db.create_external_table(
name=f'data.{base_name}_external',
location=f'@data.{base_name}_stage',
cols=external_table_columns,
partition='timestamp_part',
file_format=db.TypeOptions(type='JSON'),
)
db.execute(f'GRANT SELECT ON data.{base_name}_external TO ROLE {SA_ROLE}')
stored_proc_def = f"""
var sql_command = "ALTER EXTERNAL TABLE data.{base_name}_external REFRESH";
try {{
snowflake.execute ({{sqlText: sql_command}});
return "Succeeded.";
}} catch (err) {{
return "Failed: " + err;
}}
"""
db.create_stored_procedure(
name=f'data.{base_name}_procedure',
args=[],
return_type='string',
executor='OWNER',
definition=stored_proc_def,
)
refresh_task_sql = f'CALL data.{base_name}_procedure()'
db.create_task(
name=f'data.{base_name}_refresh_task',
warehouse=WAREHOUSE,
schedule='5 minutes',
sql=refresh_task_sql,
)
select_statement_sql = {
'reg':
(f"SELECT value "
f"FROM data.{base_name}_external "
f"WHERE timestamp_part >= DATEADD(HOUR, -2, CURRENT_TIMESTAMP())"),
'gov':
(f"SELECT value FROM ("
f" SELECT value AS a "
f" FROM data.{base_name}_external"
f" WHERE timestamp_part >= DATEADD(HOUR, -2, CURRENT_TIMESTAMP())"
f"), LATERAL FLATTEN (INPUT => a:records)"),
}
insert_task_sql = {
'operation':
f"""
INSERT (
RAW, HASH_RAW, CALLER_IP_ADDRESS, CATEGORY, CORRELATION_ID, DURATION_MS,
IDENTITY, IDENTITY_AUTHORIZATION, IDENTITY_CLAIMS, LEVEL, LOCATION,
OPERATION_NAME, PROPERTIES, PROPERTIES_ANCESTORS, PROPERTIES_IS_COMPLIANCE_CHECK,
PROPERTIES_POLICIES, PROPERTIES_RESOURCE_LOCATION, RESOURCE_ID, RESULT_SIGNATURE,
RESULT_TYPE, EVENT_TIME, LOADED_ON
) VALUES (
VALUE, HASH(VALUE), VALUE:callerIpAddress::STRING, VALUE:category::STRING, VALUE:correlationId::STRING,
VALUE:durationMs::NUMBER, VALUE:identity::VARIANT, VALUE:identity.authorization::VARIANT,
VALUE:identity.claims::VARIANT, VALUE:level::STRING, VALUE:location::STRING, VALUE:operationName::STRING,
VALUE:properties::VARIANT, VALUE:properties.ancestors::STRING, VALUE:properties.isComplianceCheck::STRING,
PARSE_JSON(VALUE:properties.policies),VALUE:properties.resourceLocation::STRING, VALUE:resourceId::STRING,
VALUE:resultSignature::STRING,VALUE:resultType::STRING, value:time::TIMESTAMP_LTZ, CURRENT_TIMESTAMP()
)
""",
'audit':
f"""
INSERT (
RAW, HASH_RAW, CALLER_IP_ADDRESS, CATEGORY, CORRELATION_ID,
DURATION_MS, LEVEL, OPERATION_NAME, OPERATION_VERSION, PROPERTIES,
PROPERTIES_ACTIVITY_DATE_TIME, PROPERTIES_ACTIVITY_DISPLAY_NAME,
PROPERTIES_ADDITIONAL_DETAILS, PROPERTIES_CATEGORY, PROPERTIES_ID,
PROPERTIES_INITIATED_BY, PROPERTIES_LOGGED_BY_SERVICE, PROPERTIES_OPERATION_TYPE,
PROPERTIES_RESULT, PROPERTIES_RESULT_REASON, PROPERTIES_TARGET_RESOURCES,
RESOURCE_ID, RESULT_SIGNATURE, TENANT_ID, EVENT_TIME, LOADED_ON
) VALUES (
VALUE, HASH(VALUE), VALUE:callerIpAddress::STRING, VALUE:category::STRING, VALUE:correlationId::STRING,
VALUE:durationMs::NUMBER, VALUE:level::STRING, VALUE:operationName::STRING, VALUE:operationVersion::STRING,
VALUE:properties::VARIANT, VALUE:properties.activityDateTime::TIMESTAMP_LTZ,
VALUE:properties.activityDisplayName::STRING, VALUE:properties.additionalDetails::VARIANT,
VALUE:properties.category::STRING, VALUE:properties.id::STRING, VALUE:properties.initiatedBy::VARIANT,
VALUE:properties.loggedByService::STRING, VALUE:properties.operationType::STRING, VALUE:properties.result::STRING,
VALUE:resultReason::STRING, VALUE:properties.targetResources::VARIANT, VALUE:resourceId::STRING,
VALUE:resultSignature::STRING, VALUE:tenantId::STRING, VALUE:time::TIMESTAMP_LTZ, CURRENT_TIMESTAMP()
)
""",
'signin':
f"""
INSERT (
RAW, HASH_RAW, LEVEL, CALLER_IP_ADDRESS, CATEGORY, CORRELATION_ID, DURATION_MS,
IDENTITY, LOCATION, OPERATION_NAME, OPERATION_VERSION, PROPERTIES,
PROPERTIES_APP_DISPLAY_NAME, PROPERTIES_APP_ID,
PROPERTIES_APPLIED_CONDITIONAL_ACESS_POLICIES, PROPERTIES_AUTHENTICATION_METHODS_USED,
PROPERTIES_AUTHENTICATION_PROCESSING_DETAILS, PROPERTIES_CLIENT_APP_USED,
PROPERTIES_CONDITIONAL_ACCESS_STATUS, PROPERTIES_CREATED_DATE_TIME,
PROPERTIES_DEVICE_DETAIL, PROPERTIES_ID, PROPERTIES_IP_ADDRESS, PROPERTIES_IS_INTERACTIVE, PROPERTIES_LOCATION,
PROPERTIES_MFA_DETAIL, PROPERTIES_NETWORK_LOCATION, PROPERTIES_PROCESSING_TIME_IN_MILLISECONDS,
PROPERTIES_RESOURCE_DISPLAY_NAME, PROPERTIES_RESOURCE_ID, PROPERTIES_RISK_DETAIL,
PROPERTIES_RISK_EVENT_TYPES, PROPERTIES_RISK_LEVEL_AGGREGATED, PROPERTIES_RISK_LEVEL_DURING_SIGNIN,
PROPERTIES_RISK_STATE, PROPERTIES_STATUS, PROPERTIES_TOKEN_ISSUER_TYPE, PROPERTIES_USER_DISPLAY_NAME,
PROPERTIES_USER_ID, PROPERTIES_USER_PRINCIPAL_NAME, RESOURCE_ID, RESULT_DESCRIPTION, RESULT_SIGNATURE,
RESULT_TYPE, TENANT_ID, EVENT_TIME, LOADED_ON
) VALUES (
VALUE, HASH(VALUE), VALUE:Level::NUMBER, VALUE:callerIpAddress::STRING, VALUE:category::STRING,
VALUE:correlationId::STRING, VALUE:durationMs, VALUE:identity::STRING, VALUE:location::STRING,
VALUE:operationName::STRING, VALUE:operationVersion::STRING, VALUE:properties::VARIANT,
VALUE:properties.appDisplayName::STRING, VALUE:properties.appId::STRING,
VALUE:properties.appliedConditionalAccessPolicies::VARIANT, VALUE:properties.authenticationMethodsUsed::VARIANT,
VALUE:properties.authenticationProcessingDetails::VARIANT, VALUE:properties.clientAppUsed::STRING,
VALUE:properties.conditionalAccessStatus::STRING, VALUE:properties.createdDateTime::TIMESTAMP_LTZ,
VALUE:properties.deviceDetail::VARIANT, VALUE:properties.id::STRING, VALUE:properties.ipAddress::STRING,
VALUE:properties.isInteractive::BOOLEAN, VALUE:properties.location::VARIANT,
VALUE:properties.mfaDetail::VARIANT, VALUE:properties.networkLocationDetails::VARIANT,
VALUE:properties.processingTimeInMilliseconds::NUMBER, VALUE:properties.resourceDisplayName::STRING,
VALUE:properties.resourceId::STRING, VALUE:properties.riskDetail::STRING,
VALUE:properties.riskEventTypes::VARIANT, VALUE:properties.riskLevelAggregated::STRING,
VALUE:properties.riskLevelDuringSignIn::STRING, VALUE:properties.riskState::VARIANT,
VALUE:properties.status::VARIANT, VALUE:properties.tokenIssuerType::STRING,
VALUE:properties.userDisplayName::STRING, VALUE:properties.userId::STRING,
VALUE:properties.userPrincipalName::STRING, VALUE:resourceId::STRING, VALUE:resultDescription::STRING,
VALUE:resultSignature::STRING, VALUE:resultType::STRING, VALUE:tenantId::STRING, VALUE:time::TIMESTAMP_LTZ,
CURRENT_TIMESTAMP()
)
""",
}
ingest_task_sql = f"""
MERGE INTO data.{base_name}_connection a
USING (
{select_statement_sql[cloud_type]}
) b
ON a.raw = b.value
WHEN NOT MATCHED THEN
{insert_task_sql[connection_type]}
"""
db.create_task(
name=f'data.{base_name}_ingest_task',
warehouse=WAREHOUSE,
schedule=f'AFTER data.{base_name}_refresh_task',
sql=ingest_task_sql,
)
return {
'newStage': 'finalized',
'newMessage': 'Created Stage, Tables, Stored Procedure, and Tasks.',
}
def connect(connection_name, options):
base_name = f'aws_vpc_flow_log_{connection_name}'
stage = f'data.{base_name}_stage'
landing_table = f'data.{base_name}_connection'
bucket = options['bucket_name']
prefix = options.get('filter', 'AWSLogs/')
role = options['aws_role']
comment = yaml_dump(module='aws_flow_log')
db.create_stage(
name=stage,
url=f's3://{bucket}',
prefix=prefix,
cloud='aws',
credentials=role,
file_format=FILE_FORMAT,
)
db.create_table(name=landing_table, cols=LANDING_TABLE_COLUMNS, comment=comment)
db.create_table_and_upload_csv(
name='data.network_protocol_mapping',
columns=PROTOCOL_MAPPING_TABLE_COLUMNS,
file_path=NETWORK_PROTOCOL_PATH,
file_format=PROTOCOL_FILE_FORMAT,
ifnotexists=True,
)
stage_props = db.fetch_props(
f'DESC STAGE {stage}', filter=('AWS_EXTERNAL_ID', 'SNOWFLAKE_IAM_USER')
)
prefix = prefix.rstrip('/')
return {
'newStage': 'created',
'newMessage': CONNECT_RESPONSE_MESSAGE.format(
role=role,
role_trust_relationship=dumps(
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {"AWS": stage_props['SNOWFLAKE_IAM_USER']},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId": stage_props['AWS_EXTERNAL_ID']
}
},
}
],
},
indent=4,
),
role_policy=dumps(
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:GetObjectVersion"],
"Resource": f"arn:aws:s3:::{bucket}/{prefix}/*",
},
{
"Effect": "Allow",
"Action": "s3:ListBucket",
"Resource": f"arn:aws:s3:::{bucket}",
"Condition": {"StringLike": {"s3:prefix": [f"{prefix}/*"]}},
},
],
},
indent=4,
),
),
}
def connect(connection_name, options):
table_name = f'osquery_log_{connection_name}_connection'
landing_table = f'data.{table_name}'
prefix = ''
bucket_name = ''
db.create_table(
name=landing_table,
cols=LANDING_TABLE_COLUMNS,
comment=yaml_dump(module='osquery_log', **options),
)
db.execute(f'GRANT INSERT, SELECT ON {landing_table} TO ROLE {SA_ROLE}')
stage_name = options.get('existing_stage')
if not stage_name:
stage_name = f'data.osquery_log_{connection_name}_stage'
bucket_name = options['bucket_name']
prefix = options['prefix']
aws_role = options['aws_role']
db.create_stage(
name=stage_name,
url=f's3://{bucket_name}',
prefix=prefix,
cloud='aws',
credentials=aws_role,
file_format=db.TypeOptions(type='JSON'),
)
stage_props = db.fetch_props(
f'DESC STAGE {stage_name}',
filter=('AWS_EXTERNAL_ID', 'SNOWFLAKE_IAM_USER', 'AWS_ROLE', 'URL'),
)
if not bucket_name or not prefix:
m = re.match(r'^\["s3://([a-z-]*)/(.*)"\]$', stage_props['URL'])
if m:
bucket_name, prefix = m.groups()
else:
raise RuntimeError('cannot determine bucket name or prefix')
prefix = prefix.rstrip('/')
return {
'newStage':
'created',
'newMessage':
CONNECT_RESPONSE_MESSAGE.format(
role=stage_props[
'AWS_ROLE'], # this seems better than what we do in other places?
role_trust_relationship=dumps(
{
"Version":
"2012-10-17",
"Statement": [{
"Effect": "Allow",
"Principal": {
"AWS": stage_props['SNOWFLAKE_IAM_USER']
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId":
stage_props['AWS_EXTERNAL_ID']
}
},
}],
},
indent=4,
),
role_policy=dumps(
{
"Version":
"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:GetObjectVersion"],
"Resource":
f"arn:aws:s3:::{bucket_name}/{prefix}/*",
},
{
"Effect": "Allow",
"Action": "s3:ListBucket",
"Resource": f"arn:aws:s3:::{bucket_name}",
"Condition": {
"StringLike": {
"s3:prefix": [f"{prefix}/*"]
}
},
},
],
},
indent=4,
),
),
}
def connect(connection_name, options):
base_name = f'AWS_CLOUDTRAIL_{connection_name}_EVENTS'.upper()
stage = f'data.{base_name}_STAGE'
staging_table = f'data.{base_name}_STAGING'
landing_table = f'data.{base_name}_CONNECTION'
bucket = options['bucket_name']
prefix = options.get('filter', 'AWSLogs/')
role = options['aws_role']
comment = f'''
---
module: cloudtrail
'''
db.create_stage(
name=stage,
url=f's3://{bucket}',
prefix=prefix,
cloud='aws',
credentials=role,
file_format=FILE_FORMAT
)
db.create_table(
name=staging_table,
cols=[('v', 'variant')]
)
db.create_table(
name=landing_table,
cols=LANDING_TABLE_COLUMNS,
comment=comment
)
stage_props = db.fetch_props(
f'DESC STAGE {stage}',
filter=('AWS_EXTERNAL_ID', 'SNOWFLAKE_IAM_USER')
)
prefix = prefix.rstrip('/')
return {
'newStage': 'created',
'newMessage': CONNECT_RESPONSE_MESSAGE.format(
role=role,
role_trust_relationship=dumps({
'Version': '2012-10-17',
'Statement': [
{
'Effect': 'Allow',
'Principal': {
'AWS': stage_props['SNOWFLAKE_IAM_USER']
},
'Action': 'sts:AssumeRole',
'Condition': {
'StringEquals': {
'sts:ExternalId': stage_props['AWS_EXTERNAL_ID']
}
}
}
]
}, indent=4),
role_policy=dumps({
'Version': '2012-10-17',
'Statement': [
{
'Effect': 'Allow',
'Action': [
's3:GetObject',
's3:GetObjectVersion',
],
'Resource': f'arn:aws:s3:::{bucket}/{prefix}/*'
},
{
'Effect': 'Allow',
'Action': 's3:ListBucket',
'Resource': f'arn:aws:s3:::{bucket}',
'Condition': {
'StringLike': {
's3:prefix': [
f'{prefix}/*'
]
}
}
}
]
}, indent=4),
)
}
def connect(connection_name, options):
base_name = f'ldap_{connection_name}'
stage = f'data.{base_name}_stage'
landing_table = f'data.{base_name}_connection'
comment = yaml_dump(module='ldap', **options)
stage = options.get('existing_stage')
if stage:
prefix = ''
aws_role = ''
bucket_name = ''
stage_name = stage
else:
stage_name = f'data.ldap_{connection_name}_stage'
bucket_name = options['bucket_name']
prefix = options['prefix']
aws_role = options['aws_role']
db.create_stage(
name=stage_name,
url=f's3://{bucket_name}',
prefix=prefix,
cloud='aws',
credentials=aws_role,
file_format=FILE_FORMAT,
)
db.create_table(name=landing_table,
cols=LANDING_TABLE_COLUMNS,
comment=comment)
stage_props = db.fetch_props(
f'DESC STAGE {stage_name}',
filter=('AWS_EXTERNAL_ID', 'SNOWFLAKE_IAM_USER', 'AWS_ROLE', 'URL'),
)
url_parts = stage_props['URL'].split('/')
if prefix == '':
prefix = '/'.join(url_parts[3:-1])
if bucket_name == '':
bucket_name = url_parts[2]
if aws_role == '':
aws_role = stage_props['AWS_ROLE']
prefix = prefix.rstrip('/')
return {
'newStage':
'created',
'newMessage':
CONNECT_RESPONSE_MESSAGE.format(
role=aws_role,
role_trust_relationship=dumps(
{
"Version":
"2012-10-17",
"Statement": [{
"Effect": "Allow",
"Principal": {
"AWS": stage_props['SNOWFLAKE_IAM_USER']
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId":
stage_props['AWS_EXTERNAL_ID']
}
},
}],
},
indent=4,
),
role_policy=dumps(
{
"Version":
"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:GetObjectVersion"],
"Resource":
f"arn:aws:s3:::{bucket_name}/{prefix}/*",
},
{
"Effect": "Allow",
"Action": "s3:ListBucket",
"Resource": f"arn:aws:s3:::{bucket_name}",
"Condition": {
"StringLike": {
"s3:prefix": [f"{prefix}/*"]
}
},
},
],
},
indent=4,
),
),
}
def connect(connection_name, options):
base_name = f'GITHUB_WEBHOOKS_S3_{connection_name}_EVENTS'.upper()
stage = f'data.{base_name}_STAGE'
landing_table = f'data.{base_name}_CONNECTION'
bucket = options['bucket_name']
prefix = options.get('filter', S3_BUCKET_DEFAULT_PREFIX)
role = options['aws_role']
comment = yaml_dump(module='github_webhooks_s3')
db.create_stage(
name=stage,
url=f's3://{bucket}',
prefix=prefix,
cloud='aws',
credentials=role,
file_format=FILE_FORMAT,
)
db.create_table(name=landing_table,
cols=LANDING_TABLE_COLUMNS,
comment=comment)
stage_props = db.fetch_props(f'DESC STAGE {stage}',
filter=('AWS_EXTERNAL_ID',
'SNOWFLAKE_IAM_USER'))
prefix = prefix.rstrip('/')
return {
'newStage':
'created',
'newMessage':
CONNECT_RESPONSE_MESSAGE.format(
role=role,
role_trust_relationship=dumps(
{
"Version":
"2012-10-17",
"Statement": [{
"Effect": "Allow",
"Principal": {
"AWS": stage_props['SNOWFLAKE_IAM_USER']
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId":
stage_props['AWS_EXTERNAL_ID']
}
},
}],
},
indent=4,
),
role_policy=dumps(
{
"Version":
"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:GetObjectVersion"],
"Resource": f"arn:aws:s3:::{bucket}/{prefix}/*",
},
{
"Effect": "Allow",
"Action": "s3:ListBucket",
"Resource": f"arn:aws:s3:::{bucket}",
"Condition": {
"StringLike": {
"s3:prefix": [f"{prefix}/*"]
}
},
},
],
},
indent=4,
),
),
}
def connect(connection_name, options):
connection_type = options['connection_type']
base_name = f"azure_{connection_name}_{connection_type}"
account_name = options['account_name']
container_name = options['container_name']
suffix = options['suffix']
sas_token = options['sas_token']
sas_token_ct = vault.encrypt(sas_token)
comment = f'''
---
module: azure
storage_account: {account_name}
container_name: {container_name}
suffix: {suffix}
sas_token: {sas_token_ct}
sa_user: {USER}
snowflake_account: {ACCOUNT}
database: {DATABASE}
'''
db.create_stage(
name=f'data.{base_name}_STAGE',
url=f"azure://{account_name}.blob.{suffix}/{container_name}",
cloud='azure',
prefix='',
credentials=sas_token,
file_format=FILE_FORMAT)
db.execute(
f'GRANT USAGE ON STAGE data.{base_name}_STAGE TO ROLE {SA_ROLE}')
db.create_table(name=f'data.{base_name}_CONNECTION',
cols=LANDING_TABLES_COLUMNS[connection_type],
comment=comment)
db.execute(
f'GRANT INSERT, SELECT ON data.{base_name}_CONNECTION TO ROLE {SA_ROLE}'
)
pipe_sql = {
'operation':
f'''
COPY INTO DATA.{base_name}_CONNECTION(RAW, HASH_RAW, CALLER_IP_ADDRESS, CATEGORY, CORRELATION_ID, DURATION_MS,
IDENTITY, IDENTITY_AUTHORIZATION, IDENTITY_CLAIMS, LEVEL, LOCATION,
OPERATION_NAME, PROPERTIES, PROPERTIES_ANCESTORS, PROPERTIES_IS_COMPLIANCE_CHECK,
PROPERTIES_POLICIES, PROPERTIES_RESOURCE_LOCAATION, RESOURCE_ID, RESULT_SIGNATURE,
RESULT_TYPE, EVENT_TIME, LOADED_ON)
FROM (
SELECT $1, HASH($1), $1:callerIpAddress::STRING, $1:category::STRING, $1:correlationId::STRING,
$1:durationMs::NUMBER, $1:identity::VARIANT, $1:identity.authorization::VARIANT, $1:identity.claims::VARIANT,
$1:level::STRING, $1:location::STRING, $1:operationName::STRING, $1:properties::VARIANT,
$1:properties.ancestors::STRING, $1:properties.isComplianceCheck::STRING, PARSE_JSON($1:properties.policies),
$1:properties.resourceLocation::STRING, $1:resourceId::STRING, $1:resultSignature::STRING,
$1:resultType::STRING, $1:time::TIMESTAMP_LTZ, CURRENT_TIMESTAMP()
FROM @DATA.{base_name}_STAGE)
''',
'audit':
f'''
COPY INTO data.{base_name}_CONNECTION (RAW, HASH_RAW, CALLER_IP_ADDRESS, CATEGORY, CORRELATION_ID,
DURATION_MS, LEVEL, OPERATION_NAME, OPERATION_VERSION, PROPERTIES,
PROPERTIES_ACTIVITY_DATE_TIME, PROPERTIES_ACTIVITY_DISPLAY_NAME,
PROPERTIES_ADDITIONAL_DETAILS, PROPERTIES_CATEGORY, PROPERTIES_ID,
PROPERTIES_INITIATED_BY, PROPERTIES_LOGGED_BY_SERVICE, PROPERTIES_OPERATION_TYPE,
PROPERTIES_RESULT, PROPERTIES_RESULT_REASON, PROPERTIES_TARGET_RESOURCES,
RESOURCE_ID, RESULT_SIGNATURE, TENANT_ID, EVENT_TIME, LOADED_ON)
FROM (
SELECT $1, HASH($1), $1:callerIpAddress::STRING, $1:category::STRING, $1:correlationId::STRING,
$1:durationMs::NUMBER, $1:level::STRING, $1:operationName::STRING, $1:operationVersion::STRING,
$1:properties::VARIANT, $1:properties.activityDateTime::TIMESTAMP_LTZ,
$1:properties.activityDisplayName::STRING, $1:properties.additionalDetails::VARIANT,
$1:properties.category::STRING, $1:properties.id::STRING, $1:properties.initiatedBy::VARIANT,
$1:properties.loggedByService::STRING, $1:properties.operationType::STRING, $1:properties.result::STRING,
$1:resultReason::STRING, $1:properties.targetResources::VARIANT, $1:resourceId::STRING,
$1:resultSignature::STRING, $1:tenantId::STRING, $1:time::TIMESTAMP_LTZ, CURRENT_TIMESTAMP()
FROM @data.{base_name}_STAGE
)
''',
'signin':
f'''
COPY INTO DATA.{base_name}_CONNECTION (
RAW, HASH_RAW, LEVEL, CALLER_IP_ADDRESS, CATEGORY, CORRELATION_ID, DURATION_MS,
IDENTITY, LOCATION, OPERATION_NAME, OPERATION_VERSION, PROPERTIES,
PROPERTIES_APP_DISPLAY_NAME, PROPERTIES_APP_ID,
PROPERTIES_APPLIED_CONDITIONAL_ACESS_POLICIES, PROPERTIES_AUTHENTICATION_METHODS_USED,
PROPERTIES_AUTHENTICATION_PROCESSING_DETAILS, PROPERTIES_CLIENT_APP_USED,
PROPERTIES_CONDITIONAL_ACCESS_STATUS, PROPERTIES_CREATED_DATE_TIME,
PROPERTIES_DEVICE_DETAIL, PROPERTIES_ID, PROPERTIES_IP_ADDRESS, PROPERTIES_IS_INTERACTIVE, PROPERTIES_LOCATION,
PROPERTIES_MFA_DETAIL, PROPERTIES_NETWORK_LOCATION, PROPERTIES_PROCESSING_TIME_IN_MILLISECONDS,
PROPERTIES_RESOURCE_DISPLAY_NAME, PROPERTIES_RESOURCE_ID, PROPERTIES_RISK_DETAIL,
PROPERTIES_RISK_EVENT_TYPES, PROPERTIES_RISK_LEVEL_AGGREGATED, PROPERTIES_RISK_LEVEL_DURING_SIGNIN,
PROPERTIES_RISK_STATE, PROPERTIES_STATUS, PROPERTIES_TOKEN_ISSUER_TYPE, PROPERTIES_USER_DISPLAY_NAME,
PROPERTIES_USER_ID, PROPERTIES_USER_PRINCIPAL_NAME, RESOURCE_ID, RESULT_DESCRIPTION, RESULT_SIGNATURE,
RESULT_TYPE, TENANT_ID, EVENT_TIME, LOADED_ON
)
FROM (
SELECT $1, HASH($1), $1:Level::NUMBER, $1:callerIpAddress::STRING, $1:category::STRING, $1:correlationId::STRING,
$1:durationMs, $1:identity::STRING, $1:location::STRING, $1:operationName::STRING,
$1:operationVersion::STRING, $1:properties::VARIANT, $1:properties.appDisplayName::STRING,
$1:properties.appId::STRING, $1:properties.appliedConditionalAccessPolicies::VARIANT,
$1:properties.authenticationMethodsUsed::VARIANT, $1:properties.authenticationProcessingDetails::VARIANT,
$1:properties.clientAppUsed::STRING, $1:properties.conditionalAccessStatus::STRING,
$1:properties.createdDateTime::TIMESTAMP_LTZ, $1:properties.deviceDetail::VARIANT, $1:properties.id::STRING,
$1:properties.ipAddress::STRING, $1:properties.isInteractive::BOOLEAN, $1:properties.location::VARIANT,
$1:properties.mfaDetail::VARIANT, $1:properties.networkLocationDetails::VARIANT,
$1:properties.processingTimeInMilliseconds::NUMBER, $1:properties.resourceDisplayName::STRING,
$1:properties.resourceId::STRING, $1:properties.riskDetail::STRING, $1:properties.riskEventTypes::VARIANT,
$1:properties.riskLevelAggregated::STRING, $1:properties.riskLevelDuringSignIn::STRING,
$1:properties.riskState::VARIANT, $1:properties.status::VARIANT, $1:properties.tokenIssuerType::STRING,
$1:properties.userDisplayName::STRING, $1:properties.userId::STRING, $1:properties.userPrincipalName::STRING,
$1:resourceId::STRING, $1:resultDescription::STRING, $1:resultSignature::STRING, $1:resultType::STRING,
$1:tenantId::STRING, $1:time::TIMESTAMP_LTZ,
CURRENT_TIMESTAMP()
FROM @DATA.{base_name}_STAGE
)
'''
}
db.create_pipe(name=f"data.{base_name}_PIPE",
sql=pipe_sql[options['connection_type']],
replace=True)
db.execute(
f'ALTER PIPE data.{base_name}_PIPE SET PIPE_EXECUTION_PAUSED=true')
db.execute(
f'GRANT OWNERSHIP ON PIPE data.{base_name}_PIPE TO ROLE {SA_ROLE}')
return {
'newStage': 'finalized',
'newMessage': 'Table, Stage, and Pipe created'
}
