How Steampipe permits KPIs as code

Ciaran Finnegan is the cybersecurity observe lead at CMD Solutions Australia and Phil Massyn is a senior safety marketing consultant there. A few 12 months in the past they started utilizing Steampipe and its CrowdStrike plugin to scan their prospects’ AWS environments.

Now Finnegan and Massyn are constructing an inner system for what they name “steady controls assurance.” One other technique to say it is perhaps “KPIs as code.” Right here’s an instance of a KPI (key efficiency indicator):

Essential or excessive severity vulnerabilities are remediated throughout the group’s coverage timeframe.

How do you translate that goal into code? With Steampipe, you do it by writing SQL queries that may be a part of throughout the varied APIs that your software program stack exposes. On this case meaning querying an endpoint administration system, CrowdStrike, then becoming a member of with data from a workforce administration system, Salesforce—with the understanding that both or each of those could change—to supply question outcomes that map from a vulnerability to a tool to an individual.

Right here’s the question.

SELECT
    ZTA.system_serial_number || ' (' || salesforce_krow__project_resources__c.title || ')' as useful resource,
    CASE
        WHEN ZTA.evaluation ->> 'os' = '100' THEN 'okay'
        ELSE 'alarm'
    END AS standing,
    ZTA.system_serial_number || ' (' || salesforce_krow__project_resources__c.title || ' has a rating of ' || (ZTA.evaluation ->> 'os') as cause,
    jsonb_path_query_array(ZTA.assessment_items['os_signals'], '$[*] ? (@.meets_criteria != "sure").standards') #>> '{}' as element
FROM   
    crowdstrike_zta_assessment ZTA
-- Hyperlink the serial quantity to the Salesforce knowledge, so we are able to discover the proprietor
-- LEFT JOIN is vital, in case there is not a hyperlink, we nonetheless need to see the information
LEFT JOIN salesforce_fixed_asset__c
    ON ZTA.system_serial_number = serial_number__c
-- Right here an INNER JOIN is critical.  If the serial quantity exists in Krow, however no proprietor, that might point out a
-- a knowledge inconsistency in Krow, which is able to break the question.  We would like an INNER JOIN, as a result of each entries should exist
INNER JOIN salesforce_krow__project_resources__c
    ON salesforce_fixed_asset__c.project_resource__c = salesforce_krow__project_resources__c.id

The tables in play are supplied by the CrowdStrike and Salesforce plugins. Not one of the predefined Salesforce tables would have met the necessity, however that didn’t matter as a result of CMD Options have been utilizing their very own customized Salesforce objects, and since the Salesforce plugin can dynamically acquire customized objects.

You may run the question in any of the methods Steampipe queries run: with the Steampipe CLI, with psql (or any Postgres CLI), with Metabase (or any Postgres-compatible BI software), with Python (or any programming language). Or, as CMD Options have accomplished, you may wrap a question in a Steampipe management that varieties a part of a benchmark that runs on the command line with steampipe check, or as a dashboard with steampipe dashboard.

From queries to controls and benchmarks

Right here’s the management that packages the question. It’s only a skinny wrapper that names and defines a KPI.

 
management "SEC_002" {
    title = "SEC-002 - % of in-scope personnel compute gadgets with a Crowdstrike Agent Zero Belief Rating for OS of 100"
    sql = <<EOT
    -- SQL as above
    EOT
    }

The management rolls up right into a benchmark.

 
benchmark "sec" {
    title = "Safety"
    kids = [
        ...
        control.SEC_002
        ...
    ]
}

So you may run SEC_002 individually: steampipe verify management.SEC_002. Or you may run all of the controls within the benchmark: steampipe verify benchmark.sec. Outcomes can move out in a variety of formats for downstream evaluation.

However first, the place and the way to run steampipe verify in a scheduled method? From their documentation:

steampipe-scheduled-job-runner
Run scheduled Steampipe benchmark checks securely and inexpensively on AWS utilizing ECS Fargate. We use AWS Copilot to outline Step Features and AWS ECS Fargate scheduled jobs to run Steampipe checks in Docker. Steampipe benchmarks and controls are retrieved at run-time from a git respository to assist a GitOps workflow

The job runs each evening, pulls down queries from a repo, executes these in opposition to targets, and exports the outputs to Amazon S3—as Markdown, and as JSON that’s condensed by a custom template.

Checking DMARC configuration

This is one other KPI:

All organizational electronic mail domains are configured for DMARC

And right here’s the corresponding question, once more wrapped in a management.

 
management "INF_001"  ' has a quarantine coverage.  Think about making it reject.'
                WHEN N.worth IS NULL THEN 'Area ' 

The tables right here come from the CSV and Net plugins. Like Salesforce, the CSV plugin acquires tables dynamically. On this case the checklist of domains to verify lives in a file referred to as domains.csv retrieved from a website title system administration API. The domains drive a be a part of with the net_dns_record desk to determine, from MX information, which names are configured for DMARC.

Like all Steampipe controls, these report the required columns useful resource, standing, and cause. It’s purely a conference, as you may write all types of queries in opposition to plugin-provided tables, however whenever you comply with this conference your queries play in Steampipe’s benchmark and dashboard ecosystem.

Checking for inactive person accounts

It’s true that becoming a member of throughout APIs—with SQL because the widespread technique to cause over them—is Steampipe’s final superpower. However you don’t have to affix throughout APIs. Many helpful controls question one or a number of tables supplied by a single plugin.

Right here’s another KPI:

Inactive Okta accounts are reviewed throughout the group’s coverage time frames

Right here’s the corresponding management.

 
management "IAM_001" 

Controls like this categorical enterprise logic in a transparent and readable approach, and require solely modest SQL talent.

Subsequent steps

As day by day snapshots accumulate, Finnegan and Massyn are exploring methods to visualise them and establish developments and key danger indicators (KRIs). A Python script reads the custom-made steampipe verify output and builds JSON and Markdown outputs that move to S3. They’ve constructed a prototype Steampipe dashboard to visualise queries, and contemplating how a visualization software would possibly assist full the image.

Why do all this? “There are merchandise in the marketplace we might purchase,” Finnegan says, “however they don’t combine with all our providers, and don’t give us the granular mapping from enterprise aims to SQL statements. That’s the magic of Steampipe for us.”

For extra particulars, see the repos for his or her Fargate runner and their continuous controls assurance module. In case you have an identical story to inform, please get in contact. We’re all the time wanting to know the way persons are utilizing Steampipe.