Reports Yaml Documentation with Examples

Each Report can be defined as code. There are three main sections in each report: the Report properties, Widgets, and optional Filters.

Report Properties

A report starts with the report definition section having the following properties:

title: The report title displayed in the main heading and in the list of reports.

shortDescription: a brief description of the report displayed in the report as a subtitle

longDescription: a Markdown-formatted string that fits a long document displayed in the Tips for using this report popup.

logo: specify one of the built-in images to be used on the report cards in various places of the CloudQuery Platform.

tags: an array of labels that make it easier to find or filter reports in the main Reports view.

widgets: an array of visualizations displayed in the report. See below for details.

report:
  title: <<Your report title>>
  shortDescription: <<Your report short description>>
  longDescription: |
    ## What does this report show?
    <<Your report long description in markdown>>
  logo: <<tags | aws | gcp | azure | github| limits | users | ip | identity | cloud | end_of_life | cost_saving | k8s>>
  tags:
    - tag1
    - tag2
  filters:
    - type: generic
    ...
  widgets:
    - title: "first widget"
    ...

Widgets

A widget has the following properties:

title: The title of the widget

display: The display options based on the type of the widget. See Widget Display Types below.

query: SQL query that will be executed to populate the widget.

- title: <<sample widget title>>
  display:
    type: <<pie_chart | table | label | bar_chart | xy_chart>>
  query: |
    select if(length(tags) = 0, 'Untagged Resources', 'Tagged Resources') AS tags_status, count(*)
    from cloud_assets
    where resource_category != '' and supports_tags = true
    group by tags_status
    order by tags_status asc

Label

Label is the simplest visualization to show a text or a single number.

- title: label widget
  display:
    type: label
  query: |
    select 1

Table

Table widget visualizes the query result as is.

- title: table widget
  display:
    type: table
  query: |
    select 'EU' as region, 2 as count, toDate(now()) as date union all
    select 'US' as region, 3 as count, toDate(now()) as date

Pie chart

Pie chart visualizes the first two columns of the query result table.

The first column must be a string (text), the second column must be a number.

Optionally, you can display a "total" representing the sum of all values in the center of the pie chart and set a label for it.

- title: pie chart widget
  display:
    type: pie_chart
    total:
      show: true
      label: pie chart inner label
  query: |
    select 'slice 1' as slice, 10 as value union all
    select 'slice 2' as slice, 20 as value union all
    select 'slice 3' as slice, 5 as value

Bar chart

Bar chart visualizes the first two columns of the query result table.

The first column must be a string (text), the second column must be a number.

Use the entryLabel property to customize the tooltip over the bar.

- title: bar chart widget
  display:
    type: bar_chart
    entryLabel: average
  query: |
    select 'row 1' as x, 2 as y union all
    select 'row 2' as x, 3 as y

Bar chart with multiple series

Use the map column type to show a bar chart with multiple series.

- title: bar chart with multiple series
  display:
    type: bar_chart
  width: 100%
  query: |
    WITH
      toDate('2024-01-01') AS start_date,
      toDate('2024-01-05') AS end_date
    SELECT
      date,
      map(
      'series 1', randUniform(10, 50),
      'series 2', randUniform(0, 100),
      'series 3', randUniform(10, 30)
      ),
    FROM
      (
        SELECT addDays(start_date, number) AS date
        FROM numbers(dateDiff('day', start_date, end_date) + 1)
      )
    ORDER BY 
        date;

X-Y Chart

X-Y chart is a line chart designed to display multiple data series over the same X axis. It can combine multiple queries into a single visualization. Unlike the other widgets, this widget accepts an array of queries instead of just a single query.

Each query is specified with its title (series name) and the actual SQL query.

- title: XY-chart
  display:
    type: xy_chart
    width: 100%
  queries:
    - title: Random series 1
      query: |
        WITH
          toDate('2024-01-01') AS start_date,
          toDate('2024-01-05') AS end_date
        SELECT
          date,
          rand() / 1000000000 as random_normal
        FROM
          (
            SELECT addDays(start_date, number) AS date
            FROM numbers(dateDiff('day', start_date, end_date) + 1)
          )
        ORDER BY 
            date;
    - title: Random series 2
      query: |
        WITH
          toDate('2024-01-01') AS start_date,
          toDate('2024-01-05') AS end_date
        SELECT
          date,
          rand() / 1000000000 AS random_normal
        FROM
          (
            SELECT addDays(start_date, number) AS date
            FROM numbers(dateDiff('day', start_date, end_date) + 1)
          )
        ORDER BY 
            date;

Filters

You can add top level filters to each report and decide what widgets will be affected by the filters.

A filter is defined by its type, label, expression, and optionally the definition of the available values.

The type defines the value type of the filter and how it is used. The available types are:

generic: a filter with a pre-defined set of options. The options are defined either statically in the code, or loaded via a SQL query
date: a filter with a date selector
search: a free-form user input with an optional default value

The label defines the label displayed to the user.

The filter expression (named filterExpression in the report code) is the placeholder expression used in the SQL queries of widgets. CloudQuery will automatically replace the expression when the filter is used by the user. By using the filterExpression in the SQL query of a widget, you tell CloudQuery to apply the filter to the specific widget.

Each filter expression needs to be unique within the report. There cannot be two filters with the same filter expression, such as account_id = '?' . If you need to have two filters filtering on the same column name, consider using the as keyword in the SQL queries to rename the columns to match the specific filter expression.

For example, you can define the filter expression as account = '?' . Then in the SQL query, define the where clause using this exact expression:

...

query: |
  select account, name from cloud_assets
  where 1=1 and account = '?'
...

We recommend using the 1=1 expression in connection with the actual filter expression so when the filter value is not set, the widget shows the results of the query without any filter applied.

Following are examples of the different filter types with widgets using them.

Generic filter with statically defined options

This filter is a dropdown with a static list of options defined in the code. Each option has a label displayed to the user and value used in the SQL query.

- type: generic
  filterLabel: Option
  filterExpression: account_id = '?'
  options:
    - label: account 1
      value: 1
    - label: account 2
      value: 2
    - label: account 3
      value: 3

An example of a table widget using this filter:

- title: Widget with a generic option filter
  display:
    type: table
  query: |
    select account_id as account_id_filtered, account_name from (
        select 1 as account_id, 'account 1' as account_name union all
        select 2 as account_id, 'account 2' as account_name
    )
    where 1=1 and account_id = '?'

Generic filter with options from a SQL query

This filter will look the same as the one above, but the options will be dynamic based on the SQL query definition. You can use any table in CloudQuery to get the values from.

- type: generic
  filterLabel: Account
  filterExpression: account_id = '?'
  query: | 
    select value, label from (
        select 1 as value, 'account 1' as label union all
        select 2 as value, 'account 2' as label union all
        select 3 as value, 'account 3' as label
    ) order by value

Date selector

The default date selector provides a calendar day dropdown with buttons pointing to intervals in the past (7 days ago, 14 days ago, ...)

  - type: date
    filterLabel: Event Date
    filterExpression: event_time < '?'
    defaultValue: now() - interval 30 DAY

An example widget using the filter above:

- title: widget filtered by Event Date
  display:
    type: table
  query: |
    select event_time from (
        select now() as event_time union all
        select now() - interval 10 day as event_time 
    )
    where 1=1 and event_time < '?'

Date selector with a date in the future

Add mode:future to the date filter to change the buttons to point to a future date:

- type: date
  filterLabel: Expiration
  filterExpression: expiration_date < '?'
  defaultValue: now() + interval 30 DAY
  mode: future

Free-form search filter

This free-form search input lets users type in any value.

- type: search
  filterExpression: ip_address LIKE '%?%'
  filterLabel: IP Address

An example widget using this filter:

- title: widget using search without default value
  display:
    type: table
  query: |
    select ip_address from (
        select '192.168.1.1' as ip_address union all
        select '192.168.2.1' as ip_address union all
        select '192.168.3.1' as ip_address 
    )
    where 1=1 and ip_address LIKE '%?%'

Free-form search filter with a default value

Add a defaultValue property to the above to always load a fresh report with this filter set automatically.

- type: search
  filterExpression: average_load >= '?'
  filterLabel: minimum average load (%)
  defaultValue: 50

An example widget using this filter:

- title: widget using search with default value
  display:
    type: table
  query: |
    select ip_address, average_load from (
        select '192.168.1.1' as ip_address, 30 as average_load union all
        select '192.168.1.2' as ip_address, 40 as average_load union all
        select '192.168.2.1' as ip_address, 50 as average_load union all
        select '192.168.3.1' as ip_address, 60 as average_load 
    )
    where 1=1 and average_load >= '?'

Advanced Options

You can use the width property on the display properties to set the desired widget width in the percentage of the view port. This property is available on all widgets.

- title: label widget
  display:
    type: label
    width: 50%
  query: |
    select 1

To add a search box in a table widget, add a search_string column in the SQL query. The column will not be visualized. Instead, a search box will be displayed in the table header and you'll be able to use it to find a value from the search_string column. You can use concat() function, for example, to make more columns searchable or to include columns in the search even if they are not visualized.

- title: table widget with search
  display:
    type: table
  query: |
    select 'EU' as region, 2 as count, toDate(now()) as date, concat('eu','abc') as search_string union all
    select 'US' as region, 3 as count, toDate(now()) as date, concat('us','def') as search_string

Groups

You can wrap widgets in collapsible groups to make the reports more concise. A group has the following properties:

title: The title of the group

description: The subtitle displayed below the title.

widgets: An array of widgets.

groups:
    - title: first group
      description: first group with widgets
      widgets:
        - title: label widget
          display:
            type: label
          query: |
            select 1

PreviousBuilt-in Report Templates NextFull Report Example

Last updated 1 month ago

Was this helpful?

Reports Yaml Documentation with Examples

Report Properties

Widgets

Label

Table

Pie chart

Bar chart

Bar chart with multiple series

X-Y Chart

Filters

Generic filter with statically defined options

Generic filter with options from a SQL query

Date selector

Date selector with a date in the future

Free-form search filter

Free-form search filter with a default value

Advanced Options

Setting widget width

Adding search to a table widget

Groups