[*]
Ciaran Finnegan is the cybersecurity apply lead at CMD Methods Australia and Phil Massyn is a senior stability specialist there. About a calendar year ago they started employing Steampipe and its CrowdStrike plugin to scan their customers’ AWS environments.
Now Finnegan and Massyn are setting up an inside process for what they phone “continuous controls assurance.” An additional way to say it may possibly be “KPIs as code.” Here’s an instance of a KPI (key general performance indicator):
Crucial or high severity vulnerabilities are remediated in just the organization’s plan timeframe.
How do you translate that goal into code? With Steampipe, you do it by writing SQL queries that can sign up for across the varied APIs that your program stack exposes. In this circumstance that usually means querying an endpoint management program, CrowdStrike, then joining with details from a workforce management process, Salesforce—with the comprehending that both or the two of these may well change—to create query benefits that map from a vulnerability to a unit to a particular person.
Here’s the query.
Pick out
ZTA.technique_serial_range || ' (' || salesforce_krow__job_sources__c.name || ')' as source,
Situation
WHEN ZTA.evaluation ->> 'os' = '100' THEN 'ok'
ELSE 'alarm'
Finish AS status,
ZTA.system_serial_quantity || ' (' || salesforce_krow__challenge_assets__c.title || ' has a score of ' || (ZTA.assessment ->> 'os') as reason,
jsonb_path_query_array(ZTA.assessment_objects['os_signals'], '$[*] ? (@.fulfills_conditions != "sure").criteria') #>> '' as detail
FROM
crowdstrike_zta_assessment ZTA
-- Link the serial variety to the Salesforce facts, so we can uncover the proprietor
-- Still left Be part of is essential, in scenario there is just not a url, we even now want to see the knowledge
Still left Sign up for salesforce_set_asset__c
ON ZTA.system_serial_quantity = serial_range__c
-- Right here an Interior Be a part of is vital. If the serial number exists in Krow, but no operator, that could indicate a
-- a info inconsistency in Krow, which will break the query. We want an Interior Sign up for, simply because the two entries have to exist
Inner Be a part of salesforce_krow__project_assets__c
ON salesforce_fixed_asset__c.job_source__c = salesforce_krow__challenge_means__c.id
The tables in play are delivered by the CrowdStrike and Salesforce plugins. None of the predefined Salesforce tables would have achieved the will need, but that did not make a difference mainly because CMD Solutions ended up making use of their individual custom Salesforce objects, and simply because the Salesforce plugin can dynamically obtain custom objects.
You can run the query in any of the methods Steampipe queries operate: with the Steampipe CLI, with psql (or any Postgres CLI), with Metabase (or any Postgres-suitable BI tool), with Python (or any programming language). Or, as CMD Alternatives have performed, you can wrap a query in a Steampipe control that types portion of a benchmark that runs on the command line with steampipe check out, or as a dashboard with steampipe dashboard.
From queries to controls and benchmarks
Here’s the control that offers the query. It’s just a slim wrapper that names and defines a KPI.
regulate "SEC_002"
title = "SEC-002 - % of in-scope staff compute products with a Crowdstrike Agent Zero Trust Rating for OS of 100"
sql = <
The control rolls up into a benchmark.
benchmark "sec"
title = "Security"
children = [
...
control.SEC_002
...
]
So you can run SEC_002 individually: steampipe check control.SEC_002. Or you can run all the controls in the benchmark: steampipe check benchmark.sec. Results can flow out in a variety of formats for downstream analysis.
But first, where and how to run steampipe check in a scheduled manner? From their documentation:
steampipe-scheduled-job-runner
Run scheduled Steampipe benchmark checks securely and inexpensively on AWS using ECS Fargate. We use AWS Copilot to define Step Functions 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 support a GitOps workflow
The job runs every night, pulls down queries from a repo, executes those against targets, and exports the outputs to Amazon S3—as Markdown, and as JSON that’s condensed by a custom template.
Checking DMARC configuration
Here's another KPI:
All organizational email domains are configured for DMARC
And here’s the corresponding query, again wrapped in a control.
control "INF_001"
title = "INF-001 - Organisational email domains without DMARC configured"
description = "Protect against spoofing & phishing, and help prevent messages from being marked as spam. See https://support.google.com/a/answer/2466563?hl=en for more details."
sql = <
The tables here come from the CSV and Net plugins. Like Salesforce, the CSV plugin acquires tables dynamically. In this case the list of domains to check lives in a file called domains.csv retrieved from a domain name system management API. The domain names drive a join with the net_dns_record table to figure out, from MX records, which names are configured for DMARC.
Like all Steampipe controls, these report the required columns resource, status, and reason. It’s purely a convention, as you can write all kinds of queries against plugin-provided tables, but when you follow this convention your queries play in Steampipe’s benchmark and dashboard ecosystem.
Checking for inactive user accounts
It’s true that joining across APIs—with SQL as the common way to reason over them—is Steampipe’s ultimate superpower. But you don’t have to join across APIs. Many useful controls query one or several tables provided by a single plugin.
Here’s one more KPI:
Inactive Okta accounts are reviewed within the organization’s policy time frames
Here’s the corresponding control.
control "IAM_001"
Controls like this express business logic in a clear and readable way, and require only modest SQL skill.
Next steps
As daily snapshots accumulate, Finnegan and Massyn are exploring ways to visualize them and identify trends and key risk indicators (KRIs). A Python script reads the customized steampipe check output and builds JSON and Markdown outputs that flow to S3. They’ve built a prototype Steampipe dashboard to visualize queries, and considering how a visualization tool might help complete the picture.
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 more details, see the repos for their Fargate runner and their continuous controls assurance module. If you have a similar story to tell, please get in touch. We’re always eager to know how people are using Steampipe.
Copyright © 2022 IDG Communications, Inc.
More Stories
7 downsides of open source culture
iPhone 15 And 15 Plus Price Tag Could Be Lowered To Increase Adoptability
Turn A Webpage Into A Desktop App With Gluon