Endpoint vulnerability management samples

This file is a companion to Canva's blog post on how we do endpoint vulnerability management at scale. To avoid overloading readers with technical details, we've split the write-up. This lets more technical readers dive deep and learn how they can implement a similar solution for their organization.

The queries shown here support the How we measure success section of the blog and will follow the same pattern of headings.

⚠️ Be aware that the following data is not real. The data is for illustrative purposes only and might showcase issues with the vulnerability management program. This is intentional, and we do it explicitly to demonstrate what issues can look like. The data doesn't reflect the actual data or status of the vulnerability management process at Canva.

The sample data used in the graphs published on the blog is available in the sample_data folder.

How we measure success

After ingesting the data into Snowflake, we can use the following query to see some basic information.

SELECT
	application,
	applicationname,
	applicationvendor,
	applicationversion,
	cveid,
	detectiondate,
	endpoint_data_id,
	id,
	lastscandate,
	lastscanresult,
	nvdbasescore,
	nvdcvssversion,
	ostype,
	publisheddate,
	ingestion_date
FROM
	vulnerability

The integration that reads the data from S3 and ingests it into Snowflake adds the ingestion_date field, allowing us to see a snapshot of vulnerability data at any previous point in time and review trends over time.

Vulnerabilities over time

This view shows us whether our methods to reduce vulnerabilities work.

:note: The following query, as well as other queries, use a statement to limit the data returned by the queries to the last 90 days. If you're experimenting with the sample data, you might need to adjust this value accordingly.

:note: Be aware that the average used here is relative to the previous rows returned by the query. Days where no data is returned might skew the results. You can address the issue, but you need to use a CTE containing all the dates and then perform a JOIN operation against the returned data. Because of the added complexity, this work is considered out of scope for this document.

SELECT
    DATE_TRUNC('DAY', ingestion_date) AS "Date",
    -- Sets the severity of the vulnerability based on the score.
    CASE
        WHEN nvdbasescore < 4 THEN 'Low'
        WHEN nvdbasescore < 7 THEN 'Medium'
        WHEN nvdbasescore < 9 THEN 'High'
        WHEN nvdbasescore <= 10 THEN 'Critical'
        ELSE 'Unknown'
    END AS "CVSS Severity",
    -- Each vulnerability ID is a different record, we want to count those.
    COUNT(DISTINCT id) as "Vulnerability Count",
    -- This average is useful to see the general trend for the data.
    AVG("Vulnerability Count") OVER (
        PARTITION BY "CVSS Severity" ORDER BY "Date" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS "3 day average"
FROM
    vulnerability
WHERE
    -- Only show data for the past 90 days.
    DATE_TRUNC('DAY', ingestion_date) > CURRENT_DATE - INTERVAL '90 days'
GROUP BY "Date", "CVSS Severity"
ORDER BY "Date"

Top widespread vulnerable applications

With this view, you can visualize if a set of applications, in particular, introduces the most risk to the organization. It can help prioritize efforts into which applications to manage.

SELECT TOP 10
	-- We're not using vendor in the graph, but would be just as easy to see vendor data if we were interested in it.
	applicationvendor,
	applicationname,
	-- This will give us the number of endpoints where the vulnerable application is deployed.
	COUNT(DISTINCT endpoint_data_id) as "Vulnerable Endpoints"
FROM
	vulnerability
WHERE
	-- For this query, we're only interested in the data from the latest report.
	ingestion_date = (SELECT MAX(ingestion_date) FROM vulnerability)
GROUP BY
	applicationvendor,
	applicationname
ORDER BY
	"Vulnerable Endpoints"
DESC

Vulnerability age through time

This view provides interesting data about the vulnerability age in the environment. The graph tends to climb steadily because each day, vulnerabilities become a day older, but it can give you some insights into how old the unaddressed vulnerabilities are.

SELECT
	-- Calculate percentiles using days since the vulnerability was detected.
	PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY daysdetected) AS p50,
	PERCENTILE_DISC(0.95) WITHIN GROUP(ORDER BY daysdetected) AS p95,
	MAX(daysdetected::INTEGER) AS "Max",
	DATE_TRUNC('DAY', ingestion_date) AS "Date"
FROM
	vulnerability
WHERE
	-- Only show data for the past 90 days.
	DATE_TRUNC('DAY', ingestion_date) > DATE_TRUNC('DAY', CURRENT_DATE - INTERVAL '90 days')
GROUP BY
	-- Group by date so we can see how the numbers change daily and look at trends over time.
	"Date"
ORDER BY
	"Date"

Widespread managed vulnerable applications out of SLA

The view produced by the following query is what we've found most impactful at Canva. It’s more complex than previous views, using an external table to determine which applications we centrally manage. Because non-managed applications are excluded from results, it should, in theory, return no data. Realistically, however, we expect spikes from vulnerabilities being published and then resolved automatically throughout the fleet. Any sustained numbers indicate issues with the vulnerability management process.

WITH vulnerabilities_out_of_sla_managed_apps AS (
    SELECT
        applicationname,
        applicationvendor,
        applicationversion,
        daysdetected,
        nvdbasescore,
        endpoint_data_id,
        ingestion_date
    FROM
        vulnerability
    JOIN
        /* We are joining the vulnerability data with a table that contains managed data, this
        /* allows us to discard vulnerable applications that are not centrally managed. */
        managed_applications
    ON
        LOWER(applicationname) = LOWER(managed_applications.application_name)
        AND
        (
            LOWER(applicationvendor) = LOWER(application_vendor)
            OR managed_applications.application_vendor IS NULL
        )
        AND LOWER(ostype) = LOWER(managed_applications.application_os)
    WHERE
        -- Only get data for the past 90 days.
        DATE_TRUNC('DAY', ingestion_date) > CURRENT_DATE - INTERVAL '90 days'
        -- The logic below checks whether a vulnerability is breaching SLA.
        AND
        (
            (
                -- For low vulnerabilities, the SLA is 10 days.
                nvdbasescore >= 0 AND nvdbasescore < 4 AND daysdetected >= 10
            )
            OR
            (
                -- For medium vulnerabilities, the SLA is 3 days.
                nvdbasescore >= 4 AND nvdbasescore < 7 AND daysdetected >= 3
            )
            OR
            (
                -- For high vulnerabilities, the SLA is 2 days.
                (
                    (
                        nvdbasescore >= 7 AND nvdbasescore < 9
                    )
                    /* Sometimes the CVSS score is not known. Out of caution we want to treat
                    /* vulnerabilities with unknown CVSS scores as high. */
                    OR nvdbasescore IS NULL
                )
                AND daysdetected >= 2
            )
            OR
            (
                -- For critical vulnerabilities, the SLA is 1 day.
                nvdbasescore >= 9 AND nvdbasescore <= 10 AND daysdetected >= 1
            )
        )
/* This CTE takes the previous one as input and ouputs one record for every vulnerable application
/* per endpoint per day. So for instance instead of having 50 rows referencing software x on date y
/* and endpoint z it will show a single record referencing software x, date y and endpoint z. */
), per_app AS (
    SELECT
        DISTINCT applicationname,
        endpoint_data_id,
        /* Different vulnerabilities could be affecting the same application, we're only interested
        /* in knowing the highest severity and oldest vulnerability. While the maximum values don't
        /* necessarily always belong to the same vulnerability, it is the case most of the time so
        /* it is good enough for us not to have to make the distinction. */
        MAX(nvdbasescore) AS max_severity_score,
        MAX(daysdetected) AS vulnerability_age,
        ingestion_date
    FROM
        vulnerabilities_out_of_sla_managed_apps
    group by
        applicationname,
        endpoint_data_id,
        ingestion_date
/* This CTE is the same as the previous one but adds a new column where it translates the CVSS
/* scores to human readable labels which will make it easier to graph. */
), per_app_with_severity_label AS (
    SELECT
        *,
        CASE
            WHEN max_severity_score >= 0 AND max_severity_score < 4 THEN 'Low'
            WHEN max_severity_score >= 4 AND max_severity_score < 7 THEN 'Medium'
            WHEN max_severity_score >= 7 AND max_severity_score < 9 THEN 'High'
            WHEN max_severity_score >= 9 AND max_severity_score <= 10 THEN 'Critical'
            ELSE 'Unknown'
        END AS "CVSS Severity"
    FROM
        per_app
)
SELECT
    -- This will give us the number of vulnerable applications per endpoint.
    COUNT(DISTINCT applicationname, endpoint_data_id),
    "CVSS Severity",
    ingestion_date
FROM
    per_app_with_severity_label
GROUP BY
    "CVSS Severity",
    ingestion_date
ORDER BY
    ingestion_date desc,
    "CVSS Severity"