Ciaran Finnegan is the cybersecurity practice lead at [CMD Solutions Australia](https://www.cmdsolutions.com.au) and Phil Massyn is a senior security consultant there. Ciaran's help with testing, and his feedback, were key to the development of the [Crowdstrike plugin](https://hub.steampipe.io/plugins/turbot/crowdstrike). We circled back to find out what CMD Solutions are using the plugin for, and how Steampipe fits into their overall strategy. About a year ago they began using Steampipe to scan their customers' AWS environments. "It's amazing for that," Ciaran says, "a lot better than other tools we've used." They've done 20 such engagements, and Steampipe's adoption within CMD Solutions (and its sister Mantel Group brands) is growing: "People are using it for everything because it's like a Swiss army knife, you can do lots of different things with that SQL interface." Now Ciaran and Phil 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. ```sql 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](https://hub.steampipe.io/plugins/turbot/crowdstrike) and [Salesforce](https://hub.steampipe.io/plugins/turbot/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](https://hub.steampipe.io/plugins/turbot/salesforce#custom-objects) 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](https://steampipe.io/docs/reference/cli/check), or as a dashboard with [steampipe dashboard](https://steampipe.io/docs/reference/cli/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. ```sql control "SEC_002" { title = "SEC-002 - % of in-scope personnel compute devices with a Crowdstrike Agent Zero Trust Score for OS of 100" sql = < 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](https://about.gitlab.com/topics/gitops/) The job runs every night, pulls down queries from a repo, executes those against targets, and exports the outputs to S3 -- as Markdown, and as JSON that's condensed by a [custom template](https://steampipe.io/blog/release-0-12-0#templates-for-control-outputs). ## 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. ```sql 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 = < 'ACTIVE' THEN 'skip' WHEN date_part('day', CURRENT_TIMESTAMP - U.activated) < 30 OR date_part('day', CURRENT_TIMESTAMP - U.last_login) < 30 THEN 'ok' ELSE 'alarm' END as status, CASE WHEN U.status <> 'ACTIVE' THEN 'User ' || u.email || ' is no longer active' WHEN U.last_login is null THEN 'User ' || u.email || ' has never logged on' WHEN date_part('day', CURRENT_TIMESTAMP - U.activated) < 30 OR date_part('day', CURRENT_TIMESTAMP - U.last_login) < 30 THEN 'Last logon was on ' || U.last_login ELSE 'User ' || u.email || ' last logon on ' || U.last_login END as reason, U.email, U.last_login FROM okta_user U EOT } ``` Controls like this express business logic in a clear and readable way, and require only modest SQL skill. ## Next steps As daily snapshots accumulate, Ciaran and Phil 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," Ciaran 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." We're delighted to see CMD Solutions make such excellent use of Steampipe! For more details, see the repos for their [Fargate runner](https://github.com/ciaran-finnegan/steampipe-scheduled-job-runner) and their [continuous controls assurance module](https://github.com/ciaran-finnegan/steampipe-security-controls-module). If you have a similar story to tell, please get in touch. We're always eager to know how people are using Steampipe.

KPIs as code: How CMD Solutions built tools for continuous controls assurance