How Steampipe allows KPIs as code

Ciaran Finnegan is the cybersecurity apply lead at CMD Options Australia and Phil Massyn is a senior safety advisor there. A couple of 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 “steady controls assurance.” One other solution to say it could be “KPIs as code.” Right here’s an instance of a KPI (key efficiency indicator):

Important 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 part of throughout the various APIs that your software program stack exposes. On this case which means querying an endpoint administration system, CrowdStrike, then becoming a member of with info from a workforce administration system, Salesforce—with the understanding that both or each of those might 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.identify || ')' 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.identify || ' 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 information, so we will discover the proprietor
-- LEFT JOIN is necessary, in case there is not a hyperlink, we nonetheless need to see the info
LEFT JOIN salesforce_fixed_asset__c
    ON ZTA.system_serial_number = serial_number__c
-- Right here an INNER JOIN is important.  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 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 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 purchase 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 device), with Python (or any programming language). Or, as CMD Options have finished, you’ll be able to wrap a question in a Steampipe management that types a part of a benchmark that runs on the command line with steampipe verify, 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 units 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’ll be able to run SEC_002 individually: steampipe verify management.SEC_002. Or you’ll be able to run all of the controls within the benchmark: steampipe verify benchmark.sec. Outcomes can circulate out in a number of codecs for downstream evaluation.

However first, the place and how you can 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 help a GitOps workflow

The job runs each night time, 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 customized template.

Checking DMARC configuration

This 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" 

The tables right here come from the CSV and Web plugins. Like Salesforce, the CSV plugin acquires tables dynamically. On this case the listing of domains to verify lives in a file known as domains.csv retrieved from a website identify 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, standing, and cause. It’s purely a conference, as you’ll be able to write all types of queries towards plugin-provided tables, however once you observe 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 solution 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 method, and require solely modest SQL talent.

Subsequent steps

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

Why do all this? “There are merchandise available on the market we might purchase,” Finnegan says, “however they don’t combine with all our providers, and don’t give us the granular mapping from enterprise goals 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 steady controls assurance module. In case you have an identical story to inform, please get in contact. We’re all the time desperate to understand how persons are utilizing Steampipe.