A guide to reporting on developer security with Snyk and Snowflake

Everyone wants to shift left to stop vulnerabilities as early in the process as possible. This makes sense, a bug (security or otherwise) stopped before it’s even deployed is not only easier to fix, but is less likely to cause any damage in production.

The thing is, nothing exists in a vacuum. Large enterprises often have different teams doing vulnerability management. Some are patching servers, some are securing the cloud and others are looking at code. These teams use different tools, have different metrics and even different KPIs. However, by centralizing metrics in a security data lake, enterprises can effectively drill into the impact of their developer security program as well as other downstream and related programs.

Snyk’s developer security platform uses Snowflake behind the scenes to provide out-of-the-box and accessible reporting for teams to analyze their AppSec program’s effectiveness. For Snowflake customers, Snyk also provides the ability to share this information directly via Snowflake’s native data sharing functionality, making it easy to view the data within the broader context of your entire security portfolio.

Enterprises and government agencies such as the Centers for Medicare and Medicaid Services (CMS) have long realized the value of building security data lakes with Snowflake and Snyk, and now with this integration, it has become even easier to get started.

Getting started

Go to Snyk’s Snowflake documentation page for instructions on how to request a Snowflake data share.

Snyk Analytics for Snowflake will create several tables in a data share.

Tables created in the Snyk Data Share

A view of issue data from the Snyk data share

Taking snapshots

The Snyk data share provides an automatically refreshed view of your data, at the time of this writing refreshing every two hours. Customers who want to track historical metrics can configure automatic snapshots of the table. The following configuration sets up snapshots for the issues table, taken once a day using a task in a separate table.

-- Create a table

create table snyk_issues_historical like SNYK_SNOWFLAKE_DATA_SHARE_LISTING.SNYK.ISSUES__V_1_0;
ALTER TABLE issues___V_1_0v1_historical ADD COLUMN snapshot_date DATE;

-- Create task

CREATE OR REPLACE TASK <TASK_NAME>
  WAREHOUSE = <TASK_NAME>
  SCHEDULE = 'USING CRON 0 8 * * * UTC'
  COMMENT = 'Task to insert data into snyk_issues_historical table daily'
AS
INSERT INTO DATABASE.SCHEMA.SNYK_ISSUES_HISTORICAL
SELECT *, CURRENT_DATE AS snapshot_date
FROM SNYK_SNOWFLAKE_DATA_SHARE_LISTING.SNYK.ISSUES__V_1_0;

-- Start Task 

ALTER TASK snyk_demo.public.daily_insert_issues__V_1_0_historical RESUME;

-- Query Historical Data 
select * from SNYK_DEMO.PUBLIC.ISSUES__V_1_0_HISTORICAL limit 500;

Looking at metrics

Analysts can begin querying Snyk data as soon as the data share is configured. For very large datasets, scheduling a summary table can help increase query performance; for most users this is unnecessary, but creating a view can make queries easier to write.

-- A table or view can make queries easier to write or more performant when scheduled

create view issue_summary as
select snapshot_date,issue_severity,issue_status,count(*) as issue_count from snyk_issues_historical group by all;

select * from issue_summary;

-- Count of open issues with severity of High
select issue_count,snapshot_date from issue_summary where issue_severity='High' and issue_status = 'Open';

Outside of raw queries and Snowsight, Streamlit provides the ability to create custom low code data applications and dashboards.

Enrich with other data

One of the key benefits of a security data lake is the ability to break down silos by analyzing and joining together data from different sources. For example, Snyk’s data share includes associated JIRA ticket IDs. If that data is in Snowflake, (our marketplace provides connectors for sources like Jira or ServiceNow) it’s relatively easy to incorporate that data into your reporting and join it all together.

Learn more

To learn more about building cybersecurity data lake’s on Snowflake, visit Snowflake’s cybersecurity portal or contact your account team.

To learn how to leverage Snyk data through Snowflake Data Sharing, check out this blog post on the new integration. https://snyk.co/uhok2