admin Ciaran Finnegan is the cybersecurity observe lead at CMD Solutions Australia and Phil Massyn is a senior safety marketing consultant there. A few yr 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 likely to 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 a 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 could 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 != "sure").standards') #>> '{}' as element
FROM
crowdstrike_zta_assessment ZTA
-- Hyperlink the serial quantity to the Salesforce knowledge, 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 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’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 instrument), with Python (or any programming language). Or, as CMD Options have executed, 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"
youngsters = [
...
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 stream out in a variety of formats for downstream evaluation.
However first, the place and methods 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 Capabilities 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 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
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"
The tables right here come from the CSV and Net plugins. Like Salesforce, the CSV plugin acquires tables dynamically. On this case the listing of domains to verify lives in a file referred to as domains.csv retrieved from a site title system administration API. The domains drive a be a part of with the net_dns_record desk to determine, from MX data, which names are configured for DMARC.
Like all Steampipe controls, these report the required columns useful resource, standing, and motive. It’s purely a conference, as you may write all types 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 consumer accounts
It’s true that becoming a member of throughout APIs—with SQL because the frequent technique to motive over them—is Steampipe’s final superpower. However you don’t have to hitch throughout APIs. Many helpful controls question one or a number of tables supplied by a single plugin.
Right here’s yet one more 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 each day snapshots accumulate, Finnegan and Massyn are exploring methods to visualise them and determine traits and key threat indicators (KRIs). A Python script reads the custom-made steampipe verify output and builds JSON and Markdown outputs that stream to S3. They’ve constructed a prototype Steampipe dashboard to visualise queries, and contemplating how a visualization instrument may 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 companies, and don’t give us the granular mapping from enterprise targets 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. You probably have an analogous story to inform, please get in contact. We’re at all times desperate to understand how individuals are utilizing Steampipe.
