[*]Ciaran Finnegan is the cybersecurity practice lead at CMD Solutions Australia and Phil Massyn is a senior security consultant there. About a year ago they began using Steampipe and its CrowdStrike plugin to scan their customers’ AWS environments.
[*]Now Finnegan and Massyn are building an internal system for what they call “continuous controls assurance.” Another way to say it might be “KPIs as code.” Here’s an example of a KPI (key performance indicator):
[*]Critical or high severity vulnerabilities are remediated within the organization’s policy timeframe.
[*]How do you translate that objective into code? With Steampipe, you do it by writing SQL queries that can join across the diverse APIs that your software stack exposes. In this case that means querying an endpoint management system, CrowdStrike, then joining with information from a workforce management system, Salesforce—with the understanding that either or both of these may change—to produce query results that map from a vulnerability to a device to a person.
[*]Here’s the query.
SELECT ZTA.system_serial_number || ‘ (‘ || salesforce_krow__project_resources__c.name || ‘)’ as resource, CASE WHEN ZTA.assessment ->> ‘os’ = ‘100’ THEN ‘ok’ ELSE ‘alarm’ END AS status, ZTA.system_serial_number || ‘ (‘ || salesforce_krow__project_resources__c.name || ‘ has a score of ‘ || (ZTA.assessment ->> ‘os’) as reason, jsonb_path_query_array(ZTA.assessment_items[‘os_signals’], ‘$[*] ? (@.meets_criteria != “yes”).criteria’) #>> ‘{}’ as detail FROM crowdstrike_zta_assessment ZTA — Link the serial number to the Salesforce data, so we can find the owner — LEFT JOIN is important, in case there isn’t a link, we still want to see the data LEFT JOIN salesforce_fixed_asset__c ON ZTA.system_serial_number = serial_number__c — Here an INNER JOIN is necessary. If the serial number exists in Krow, but no owner, that could indicate a — a data inconsistency in Krow, which will break the query. We want an INNER JOIN, because both entries must 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 provided by the CrowdStrike and Salesforce plugins. None of the predefined Salesforce tables would have met the need, but that didn’t matter because CMD Solutions were using their own custom Salesforce objects, and because the Salesforce plugin can dynamically acquire custom objects.
[*]You can run the query in any of the ways Steampipe queries run: with the Steampipe CLI, with psql (or any Postgres CLI), with Metabase (or any Postgres-compatible BI tool), with Python (or any programming language). Or, as CMD Solutions have done, you can wrap a query in a Steampipe control that forms 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
[*]Here’s the control that packages the query. It’s just a thin wrapper that names and defines a KPI.
control “SEC_002” { title = “SEC-002 – % of in-scope personnel compute devices with a Crowdstrike Agent Zero Trust Score for OS of 100” sql = < 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 [*]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. [*]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 = < [*]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. [*]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” { title = “IAM-001 – Dormant Okta accounts are accounts that have not logged on in the last 30 days” sql = < [*]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. Originally posted on December 21, 2022 @ 4:15 pm
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 workflowChecking DMARC configuration
Checking for inactive user accounts
Next steps
