# select * from shell;

> Use SQL to execute commands on local or remote hosts!

By Steampipe Team
Published: 2023-10-02


Steampipe blurs the boundaries between code and data, to powerful effect. The [exec plugin](https://hub.steampipe.io/plugins/turbot/exec) continues that theme. Use it to write SQL queries that run commands on local or remote hosts, returning results as one or more rows. We'll look at a few basic examples, then show how we use the plugin to enable the new [Docker Compliance](https://hub.steampipe.io/mods/turbot/docker_compliance) mod.

## From Steampipe to your local shell and back again

If you install the plugin and do nothing else, this becomes possible.

```sql
select line from exec_command_line where command = 'uname -a'
+-------------------------------------------------------------------------------------------------------------------+
| line                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------+
| Linux Indigo 5.15.90.1-microsoft-standard-WSL2 #1 SMP Fri Jan 27 02:56:13 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux |
+-------------------------------------------------------------------------------------------------------------------+
```

Multiple lines produce multiple rows.

```sql
select line from exec_command_line where command = 'ls -lr --time-style=+"%Y-%m"'
+-----------------------------------------------------------------------------------------+
| line                                                                                    |
+-----------------------------------------------------------------------------------------+
| drwxr-xr-x  6 jon jon     4096 2023-01 steampipe-mod-azure-insights                     |
| drwxr-xr-x  7 jon jon     4096 2023-07 steampipe-plugin-salesforce                      |
| drwxr-xr-x 15 jon jon     4096 2023-05 steampipe-plugin-sdk                             |
```

You can also use the `exec_command` table to bring an entire file into a column.

```sql
select stdout_output from exec_command where command = 'cat /etc/resolv.conf'
```

```
+-------------------------------------------------------------------------------------+
| output                                                                              |
+-------------------------------------------------------------------------------------+
| # This is /run/systemd/resolve/stub-resolv.conf managed by man:systemd-resolved(8). |
| # Do not edit.                                                                      |
| ...                                                                                 |

| nameserver 127.0.0.53                                                               |
| options edns0 trust-ad                                                              |
| search .                                                                            |
+-------------------------------------------------------------------------------------+
```

## Connect to remote Linux or Windows hosts, traversing bastions and proxies

  The first example uses the default [exec_local](https://hub.steampipe.io/plugins/turbot/exec#configuration) connection. Now let's edit `exec.spc` to add a connection to a remote host.

``` 
  connection "exec_local" {
  plugin = "exec"
}

connection "exec_remote_linux" {
  plugin = "exec"
  protocol = "ssh"
  host = "my.host.net"
  user = "jon"
  private_key = "~/.ssh/my.host.net.id_rsa"
}
```

Now we can query the local and remote connections individually.

```
select line from exec_local.exec_command_line where command = 'uname -a'
+-------------------------------------------------------------------------------------------------------------------+
| line                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------+
| Linux Indigo 5.15.90.1-microsoft-standard-WSL2 #1 SMP Fri Jan 27 02:56:13 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux |
+-------------------------------------------------------------------------------------------------------------------+
```

```
 select line from exec_remote_linux.exec_command_line where command = 'uname -a'
+-------------------------------------------------------------------------------------------------------+
| line                                                                                                  |
+-------------------------------------------------------------------------------------------------------+
| Linux hn 5.15.0-50-generic #56-Ubuntu SMP Tue Sep 20 13:23:26 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux |
+-------------------------------------------------------------------------------------------------------+
```

## Query multiple hosts with aggregators

You could use a SQL JOIN to combine those queries. But there's a much easier way! First create an aggregator.

```hcl
connection "exec_all" {
  plugin = "exec"
  type = "aggregator"
  connections = ["exec_local", "exec_remote_linux"]
}
```

Then query it.

```
select
  _ctx->>'connection_name' as connection, 
  line 
from
  exec_all.exec_command_line
where
  command = 'uname -a'
```

```
+-------------------+-------------------------------------------------------------------------------------------------------------------+
| connection        | line                                                                                                              |
+-------------------+-------------------------------------------------------------------------------------------------------------------+
| exec_remote_linux | Linux hn 5.15.0-50-generic #56-Ubuntu SMP Tue Sep 20 13:23:26 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux             |
| exec_local        | Linux Indigo 5.15.90.1-microsoft-standard-WSL2 #1 SMP Fri Jan 27 02:56:13 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux |
+-------------------+-------------------------------------------------------------------------------------------------------------------+
```

Here are the supported [connection modes](https://hub.steampipe.io/plugins/turbot/exec#configuration):

- Remote Linux via SSH

- Remote Windows via WinRM

- Remote Windows connection through WinRM ignoring certificate validation

- Remote Linux connection through SSH with in-file private key

- Remote Linux connection through SSH with password

- Remote Linux connection through SSH via bastion host

- Remote Linux connection through SSH via bastion host over proxy


## Analyze results with SQL

Of course SQL is also a great way to aggregate data. Let's ask and answer the question: "How many packages installed per day?"

```sql
select 
  _ctx->>'connection_name' as connection, 
  line as package_install_date, 
  count(*)
from
  exec_all.exec_command_line 
where 
  command = 'cat /var/log/dpkg.log | cut -d'' '' -f1'
group by 
  connection, line
order by
  connection, package_install_date
```

```
+-------------------+----------------------+-------+
| connection        | package_install_date | count |
+-------------------+----------------------+-------+
| exec_local        | 2023-09-05           | 11    |
| exec_local        | 2023-09-11           | 87    |
| exec_local        | 2023-09-13           | 67    |
| exec_remote_linux | 2023-09-05           | 107   |
| exec_remote_linux | 2023-09-08           | 35    |
| exec_remote_linux | 2023-09-13           | 52    |
+-------------------+----------------------+-------+
```

## Bringing it all together for Docker CIS scanning

Here's the CIS 1.6 benchmark running as a [dashboard](https://hub.steampipe.io/mods/turbot/docker_compliance/dashboards/benchmark.cis_v160). 

![CIS 1.6 dashboard](/images/blog/2023-10-exec-plugin/docker-compliance-dashboard.png)

The benchmark uses the [Docker](https://hub.steampipe.io/plugins/turbot/docker/) plugin for queries against the Docker layer (using tables like [docker_container](https://hub.steampipe.io/plugins/turbot/docker/tables/docker_container)), and also uses the `exec` plugin to query the OS layer. These queries support 33 controls that use the Docker plugin, and 56 that use the `exec` plugin.

Let's explore one of the `exec`-based controls. CIS Docker 1.6 advises that that the Docker sock file permissions should be set to 660 or more restrictively. Here's the definition of a [control](https://hub.steampipe.io/mods/turbot/docker_compliance/controls/control.cis_v160_3_16) that checks for that condition.

```
control "cis_v160_3_16" {
  title         = "3.16 Ensure that the Docker sock file permissions are set to 660 or more restrictively"
  description   = "You should verify that the Docker socket file has permissions of 660 or are configured more restrictively."
  query         = query.docker_sock_file_restrictive_permission
```

And here is the referenced query.

```sql
query "docker_sock_file_restrictive_permission" {
  sql = <<-EOQ
    ${local.os_hostname_sql}
     linux_output as (
      select
        stdout_output,
        _ctx ->> 'connection_name' as conn
      from
        exec_command,
        os_output
      where
        os_conn = _ctx ->> 'connection_name'
        and command = 'stat -c %a /var/run/docker.sock'
    ),
    darwin_output as (
      select
        stdout_output,
        _ctx ->> 'connection_name' as conn
      from
        exec_command,
        os_output
      where
        os_conn = _ctx ->> 'connection_name'
        and os_output.os = 'Darwin'
        and command = 'stat -f %Op /var/run/docker.sock'
    ),
    command_output as (
      select * from darwin_output
      union all
      select * from linux_output
    )
    select
      host as resource,
      case
        when o.stdout_output like '%No such file or directory%' then 'skip'
        when o.stdout_output like '%660%' then 'ok'
        else 'alarm'
      end as status,
      case
        when o.stdout_output like host || '%No such file or directory%' then ' recommendation is not applicable as the file is unavailable.'
        else host || ' docker.socket file permission set to ' || (btrim(o.stdout_output, E' \n\r\t')) || '.'
      end as reason
      ${replace(local.common_dimensions_qualifier_sql, "__QUALIFIER__", "h.")}
    from
      hostname as h,
      os_output as os,
      command_output as o
    where
      os.os_conn = h.host_conn
      and h.host_conn = o.conn;
  EOQ
}
```

Once you figure out  which shell commands to use, it's just the standard Steampipe pattern for a control that returns the [required columns](https://steampipe.io/docs/reference/mod-resources/control#required-control-columns) `resource`, `status`, and `reason`.

Like all other [mods](https://hub.steampipe.io/mods), `Docker Compliance` is available to [reuse and remix](https://steampipe.io/blog/remixing-dashboards). Are there other conditions you'd like to check? If there's a shell command that returns the necessary data, you can easily wrap it in a Steampipe control.

## See it in action

<div className="flex justify-center">
<iframe 
    class="youtube-video" 
    src="https://www.youtube-nocookie.com/embed/fDCHJuWmH4k" 
    frameBorder="0" 
    allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share"
    allowFullScreen
    title="select * from shell; Use SQL to execute commands on local or remote hosts!"
>
</iframe>
</div>

## What is an API anyway?

Steampipe nominally maps APIs to database tables. When you `select * from aws_sns_topic`, Steampipe winds up calling the AWS [ListTopics](https://docs.aws.amazon.com/sns/latest/api/API_ListTopics.html) API. Many Steampipe plugins work like that: a table corresponds to a specific API call. But some plugins work in a more general way. The [CSV plugin](https://hub.steampipe.io/plugins/turbot/csv) opens the door to a universe of dynamic data. The [net_http_request](https://hub.steampipe.io/plugins/turbot/net/tables/net_http_request) table of the [net plugin](https://hub.steampipe.io/plugins/turbot/net/) turns Steampipe into [an HTTP client](https://steampipe.io/blog/steampipe-is-an-http-client). The [Terraform](https://hub.steampipe.io/plugins/turbot/terraform) and [AWS CloudFormation](https://hub.steampipe.io/plugins/turbot/awscfn) plugins create a SQL facade over IaC config files. 

In that tradition, the [exec plugin](https://hub.steampipe.io/plugins/turbot/exec) creates a SQL facade over the universe of shell commands. It's a wide-open capability, we know you'll find creative uses for it, and we look forward to [hearing about them](/community/join).