Streamlining access to crucial business insights

Organizations heavily rely on data for informed decision-making in today's data-driven world. However, many companies struggle to extract data from siloed systems and make it accessible to a broader audience. Steampipe enables you to extract this data to your organization in a streamlined and efficient manner through customized queries and dashboards.

In this blog post, we will explore how Steampipe can help your organization break down data silos and make data accessible to everyone who needs it.

Extracting data from our siloed compliance system

Vanta offers security monitoring integration for audit preparation (e.g., SOC2, ISO27001), collecting evidence from multiple services. While the complete data set is designed for specific security & compliance team members, valuable insights can be shared more broadly through queries or reports. Utilizing the Steampipe Vanta plugin, administrators can extract data for a wider audience and continuously share information as needed.

Our Operations team recently received an email from Slack announcing the end of support for specific app and browser versions. We monitor operating system configurations and application inventory from a compliance perspective, but this data is in a system that our Operations team intentionally cannot access. However, by leveraging Steampipe, we can use the vanta_computer table to identify all machines that would be impacted by the Slack notice.

select
  owner_name,
  hostname,
  serial_number,
  app as application
from
  vanta_computer,
  jsonb_array_elements_text(endpoint_applications) as app
where
  app SIMILAR TO 'Slack [0-9]%'
  and string_to_array(split_part(app, ' ', 2), '.') :: int [ ] < string_to_array('4.24', '.') :: int [ ]
order by
  owner_name;

+-----------------------+---------------------+---------------+----------------+
| owner_name            | hostname            | serial_number | application    |
+-----------------------+---------------------+---------------+----------------+
| George Burdell        | gburdell            | KWKOFTCZTZ    | Slack 4.23.182 |
| John Blutarsky        | jblutarsky          | 0S0ZT5CPBU    | Slack 4.19.149 |
| Michael Scott         | mscott1             | XJT3RRPHGC    | Slack 4.14.149 |
+-----------------------+---------------------+---------------+----------------+

The one-time CSV extract was helpful for the Operations team, but they also need to track progress over time. Instead of scheduling a local cron, we can use Steampipe Cloud to schedule a snapshot of this query, ensuring the most recent data is always available to the Operations team.

CSV reports to interactive dashboards

Recurring CSV reports help the Operations team track progress, but they would benefit from a visual, interactive dashboard to share summarized results with management. To achieve this, we created a custom mod by building a dashboard around the query.

A custom mod is developed using HCL + SQL, encapsulating your SQL queries to target dashboard resources, which can be visualized as infocards, charts, tables, and more.

dashboard "unsupported_apps_using_slack" {
  title = "Employee Computers with Unsupported Apps & Browsers for Slack"
  text {
    value = "The following list are for employees with unsupported Applications & Browsers to connect with Slack starting March 1st, 2023."
  }
  container {
    width = 6
    card {
      sql   = query.vanta_count_employees_slack_apps_need_updated.sql
      width = 4
    }
      table {
      title = "List of slack applications to be updated, Slack version <= 4.23"
      sql = query.vanta_list_apps_need_updated_for_slack.sql
    }
  }
  container {
    width = 6
    card {
      sql   = query.vanta_count_employees_slack_browsers_need_updated.sql
      width = 4
    }
      table {
      title = "List of browsers to be updated, browser versions <= Edge 94, Chrome 93, Firefox 91, Safari 14"
      sql = query.vanta_list_browsers_need_updated_for_slack.sql
    }
  }
}

The final result is a more interactive and visually appealing report that can be shared with the Operations team.

Notifying your organization

Going further, we can elevate the custom mod from running locally to being shared in live dashboards with the Operations team through Steampipe Cloud.

To upload a custom mod to Steampipe Cloud, we can create a public GitHub repository with the mod.sp and dashboard file(s). Then, go to Settings -> Mods -> add the GitHub Repo URL, and click "install a custom mod".

Once available, the custom mod can leverage Steampipe Cloud's features to share live dashboards or point-in-time snapshots and schedule them periodically. By scheduling periodic dashboard snapshots, the Operations team can receive alerts on issues via automatic posts to a Slack or Microsoft Teams webhook. When non-compliance is detected, they will receive an alert like this:

Conclusion

We've shown how Steampipe can export data from a highly sensitive system and create a recurring job to keep that data current. There are numerous other examples where access should be strictly limited, but the broader organization could benefit from the data inside. For instance, Slack administration, Vercel deployment management, and Google Workspace's admin consoles are tools with restricted access that contain valuable data for others in the organization.

Do you have another example of siloed data you'd like to make available to a broader audience? If so, please let us know, we love to learn from our community!