Passing Parameters to Queries & Controls

A query may optionally define parameters. When executing the query, you can specify values for the parameters to be used for the query execution.

Using Parameters in Queries

Variable usage and interpolation in Powerpipe is based on and conforms to Terraform. Special consideration must be made for passing variables into queries, however, as both the HCL parser AND the SQL parser must account for the variables.

When defining a query, you may use positional parameters ($1, $2, $3, ...) in the query definition. For each of these positional parameters, you should define a param block that names and describes the parameter. Note that Powerpipe will assign the parameters in the order that the param blocks are defined - the first param block describes $1, the second describes $2, etc:

query "instances_in_state" {
  sql = "select instance_id, instance_state from aws_ec2_instance where instance_state = $1;" 

  param "state" {
    default = "stopped"
  } 
}

You can also pass list values as parameters, and they will converted to PostgreSQL arrays in the query:

query "instances_in_states" {
  sql = "select instance_id, instance_state from aws_ec2_instance where instance_state = any($1);" 

  param "states" {
    default = ["stopped", "running"]
  } 
}

Passing Arguments

You can run a query or control by name from the command line. If the query provides defaults for all the parameters, you can run it without arguments in the same way you would run a query or control that takes no parameters, and it will run with the default values:

powerpipe query run instances_in_state

If the query does not provide a default, or you wish to run the query with a different value, you can pass an argument to the query with one or more --arg arguments.

You can pass them by name:

powerpipe query run instances_in_state --arg state='running'

Or by position. If no argument name is provided, the arguments will be passed to the query in the order they are passed to the command (the first --arg as $1, the second as $2, etc.):

powerpipe query run instances_in_state --arg 'running'

Likewise, when specifying arguments in HCL, you can pass them by name (as a map):

control "running_instances" {
  title       = "EC2 instances that are running"
  query       = query.instances_in_state
  args        = {
    "state"   = "running"
  }
}

Or by position (as a list):

control "running_instances_list" {
  title       = "EC2 instances that are running"
  query       = query.instances_in_state
  args        = ["running"]
}

Using Parameters in Controls, Charts, and other resources

Controls, charts, cards, and many other resources allow you to refer to a parameterized query with the query argument, and you can pass arguments to the query in the args argument:

query "instances_invalid_state" {
  sql = <<-EOT
    select 
      arn as resource,
      case
        when instance_state = any($1) then 'alarm'
        else 'ok'
      end as status,
      instance_id || ' is ' || instance_state as reason,
      region,
      account_id
    from
      aws_ec2_instance
  EOT
  param "invalid_states" {
    default = ["running"]
  } 
}


control "stopped_instances" {
    title       = "EC2 instances that are stopped"
    query       = query.instances_invalid_state
    args        = {
      "invalid_states"   = ["stopped", "stopping"]
    }
}

Alternatively, you may specify inline sql, and define param blocks as you would for a query:

control "stopped_instances_inline" {
  title = "Stopped EC2 instances"
  sql   = <<-EOT
    select 
      arn as resource,
      case
        when instance_state = any($1) then 'alarm'
        else 'ok'
      end as status,
      instance_id || ' is ' || instance_state as reason,
      region,
      account_id
    from
      aws_ec2_instance
  EOT
  param "invalid_states" {
    default = ["stopped", "stopping"]
  } 
}

Note that you may either reference a query object with the query argument or use inline SQL with the sql argument from your control, but not both, and the behavior is subtly different, as can be seen in the examples above:

• The query argument is a reference to a query resource. You cannot define parameters (param blocks) for the control, but you can pass them as arguments (args) to the query if the query has parameters defined.
• The sql argument is a string. When the control specifies a SQL string, it essentially behaves like a query, and thus you can define the parameters that it accepts (in param blocks) in the same manner as a query resource.

Using Parameters with Variables

It is common for arguments and parameter defaults to refer to input variables, so that users of the mod can change the values without modifying the code:

variable "bad_states" {
  type    = list(string)
  default = ["stopped", "stopping"]
}


control "instances_in_bad_states" {
    title       = "EC2 instances that are stopped"
    query       = query.instances_invalid_state
    args        = {
      "invalid_states"   = var.bad_states
    }
}

Using Parameters with Inputs

It is common for arguments to refer to dashboard input elements, allowing you to create rich, dynamic, interactive reports:

dashboard "inputs_param_example_dashboard" {
  title = "Inputs/Params Example Dashboard"

  input "region" {
  sql = <<-EOQ
    select
      distinct region as label,
      region as value
    from
      aws_region
    order by
      region;
  EOQ    
  width = 3
  }
  
  table {
    sql = <<-EOQ
      select
        name,
        versioning_enabled
      from
        aws_s3_bucket
      where
        region = $1
    EOQ
    args =  [self.input.region.value]
  }
}