How Steampipe permits KPIs as code

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

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

Important or excessive severity vulnerabilities are remediated inside 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 part of throughout the various 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 might change—to provide 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 motive,
    jsonb_path_query_array(ZTA.assessment_items['os_signals'], '$[*] ? (@.meets_criteria != "yes").standards') #>> '{}' as element
FROM   
    crowdstrike_zta_assessment ZTA
-- Hyperlink the serial quantity to the Salesforce information, so we will discover the proprietor
-- LEFT JOIN is essential, 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 would point out a
-- a knowledge inconsistency in Krow, which can break the question.  We wish 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 offered 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 had been utilizing their very own customized Salesforce objects, and since the Salesforce plugin can dynamically acquire customized objects.

You’ll be able to 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 completed, you’ll be able to wrap a question in a Steampipe management that kinds a part of a benchmark that runs on the command line with steampipe checkor 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 devices with a Crowdstrike Agent Zero Trust Score for OS of 100"
    sql = <<EOT
    -- SQL as above
    EOT
    }

The management rolls up right into a benchmark.

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

So you’ll be able to run SEC_002 individually: steampipe test management.SEC_002. Or you’ll be able to run all of the controls within the benchmark: steampipe test benchmark.sec. Outcomes can circulation out in a variety of formats for downstream evaluation.

However first, the place and how you can run steampipe test 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 towards targets, and exports the outputs to Amazon S3—as Markdown, and as JSON that’s condensed by a custom template.

Checking DMARC configuration

Here is one other KPI:

All organizational e-mail domains are configured for DMARC

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

 
management "INF_001"  ' has a reject coverage.'
                WHEN N.worth LIKE '%p=quarantine;%' 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 record of domains to test lives in a file known as domains.csv retrieved from a website title system administration API. The domains drive a be 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, standingand motive. It’s purely a conference, as you’ll be able to write every kind of queries towards 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 frequent strategy to motive 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 offered by a single plugin.

Right here’s yet another KPI:

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

Right here’s the corresponding management.

 
management "IAM_001"  ' final logon on ' 

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

Subsequent steps

As every day snapshots accumulate, Finnegan and Massyn are exploring methods to visualise them and establish traits and key danger indicators (KRIs). A Python script reads the personalized steampipe test output and builds JSON and Markdown outputs that circulation 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 products on the market we could buy,” Finnegan says, “but they don’t integrate with all our services, and don’t give us the granular mapping from business objectives 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. When you’ve got the same story to inform, please get in contact. We’re all the time desperate to understand how persons are utilizing Steampipe.