Data Flexibility calculated properties

This page provides a list and samples (work in progress) of how you can build the calculated properties to display in Data Flexibility tables

Introduction

You can defined custom calculated properties in your schemas to:

  1. Display fields defined in the Form Json Schema.

  2. Display new fields calculated on the fly when the page loads. This is defined in the Data Model Json section.

The key info to navigate through the schemas relation is the relation name. You can check those relation names by going to Settings -> Schemas and looking at the relations name under the schema you want to add the calculated properties.

Check in this quick video how you can see what information(Properties and Relations) are available in system schemas:

You can also use calculated properties and functions to work with the Custom Data Scorecard check. All the concepts explained in this page applies there as well.

Besides the custom calculation for the columns, you can also customize the formatting of the information displayed at the tables with rich visual information. Check full docs for that here.

Fields and structure

Data Model Json

This is the structure of the calculated properties in the Data Model Json, inside a schema:

  {
    "columnName": "id",
    "hidden": true,
    "name": "id",
    "calc": "id"
  },
  • columnName: The name that will be displayed on the table.

  • name: Property name in the entity Form JSON Schema.

  • hidden: if you want hide this field in the tables. For instance if you just want to use it on the calculations but not show in the table. This field default is false if you don't declare it.

  • calc: from where you want to take the value. Complex operations can be performed here with the types and functions that we will discuss below.

Custom Data checks

This is the structure of a calculated Custom Data Check inside a Scorecard:

{
   "type": "FUNCTION",
   "aggregation": "COUNT",
   "path": "resources.snykIssues.id",
   "filter": []
}

Note that as it don't need columns configuration it's just defining the "calc" part as it would in the Data Model Json. So we omitted that "calc" definition and you can just start by the calculations directly.

Below we will explore the full set of calculations that can be performed . You can also see some direct samples in the scorecard check documentation here.

Types

Whenever you want to do a new calculation for displaying in your Data Flexibility schemas table columns you can use the following "calc" types:

  • STRING - Putting a string as a value.

  • CONST - Putting a const as a value.

  • NUMBER - Putting a number as a value.

  • MATH - Mathematical equation

  • PATH - Path to an entity or its relation value

  • FUNCTION - Aggregation function

FUNCTION

  • ANY_VALUE - Returns any from the sub-sequence

  • FIRST_VALUE - Return the first value from sub-sequence

  • SUM - the sum of the values works only with numbers

  • AVG - average value of sub-sequence. Works only with numbers

  • COUNT - compute the amount of the records in the sub-sequence

  • ARRAY_AGG - collect data as an array

  • MIN - returns the minimum value for the sub-sequence. works only with numbers.

  • MAX - returns the maximum value for the sub-sequence. works only with numbers.

  • MEDIAN - Determines the median of a set of values.

  • MOD

  • PERCENTILE_CONT

  • PERCENTILE_DISC REGULAR_FUNCTION - Regular functions 1.OBJECT_CONSTRUCT - Creates a new object

  • DATEDIFF - returns difference between 2 dates.

  • DATEADD - adding something to the date

  • CURRENT_DATE - current date

  • CURRENT_TIME - current time

  • CURRENT_TIMESTAMP - creates a current timestamp

  • TO_TIMESTAMP - Converts string/number to timestamp

  • TO_DATE - Converts string/number to date

  • YEAR

  • MONTH

  • DAY

  • HOUR

  • MINUTE

  • SECOND

  • ARRAY_CAT - Returns a concatenation of two arrays.

  • NVL - If expr1 is NULL, returns expr2, otherwise returns expr1.

  • C8_SCORECARD_METRIC - C8 System function to calculate percentage bar for the scorecards

  • ARRAY_SIZE - Returns the size of the input array.

  • OBJECT_KEYS - Returns an array containing the list of keys in the top-most level of the input object.

  • MAX_BY and MIN_BY - Get the required property by the latest entry using a Date (type createDateTime) or Timestamp(type number) field. Check samples 6 and 7 for Scorecard scope here.

Examples

Dora metrics Change Failure Rate

Sample column calculation in services Data Model Json for Dora Metric Change Failure Rate based on Deploys and Incidents schemas relation to services
  {
    "hidden": false,
    "columnName": "DORA CFR%",
    "name": "DORACFR",
    "calc": {
      "type": "MATH",
      "condition": "*",
      "values": [
        {
          "type": "MATH",
          "condition": "/",
          "values": [
            {
              "type": "FUNCTION",
              "aggregation": "COUNT",
              "path": "IncidentsServices.id"
            },
            {
              "type": "FUNCTION",
              "aggregation": "COUNT",
              "path": "DeploysServices.id"
            }
          ]
        },
        {
          "type": "NUMBER",
          "value": 100
        }
      ]
    }
  }
Resulting table from the above JSON when adding that column to the service table schema

Vulnerabilities schema

Sample complex calculations for vulnerabilities schema:
[
  {
    "hidden": false,
    "name": "lowIssue",
    "columnName": "Low Issue",
    "calc": {
      "type": "MATH",
      "condition": "*",
      "values": [
        {
          "type": "MATH",
          "condition": "/",
          "values": [
            {
              "type": "NUMBER",
              "value": 100
            },
            {
              "type": "FUNCTION",
              "aggregation": "COUNT",
              "path": "resources.snykIssues.id"
            }
          ]
        },
        {
          "type": "FUNCTION",
          "aggregation": "COUNT",
          "path": "resources.snykIssues.id",
          "filter": [
            {
              "type": "compare",
              "field": "resources.snykIssues.details.severity",
              "value": "low",
              "compare": "eq"
            }
          ]
        }
      ]
    }
  },
  {
    "hidden": false,
    "name": "mediumIssue",
    "columnName": "Snyk Medium %",
    "calc": {
      "type": "MATH",
      "condition": "*",
      "values": [
        {
          "type": "MATH",
          "condition": "/",
          "values": [
            {
              "type": "NUMBER",
              "value": 100
            },
            {
              "type": "FUNCTION",
              "aggregation": "COUNT",
              "path": "resources.snykIssues.id"
            }
          ]
        },
        {
          "type": "FUNCTION",
          "aggregation": "COUNT",
          "path": "resources.snykIssues.id",
          "filter": [
            {
              "type": "compare",
              "field": "resources.snykIssues.details.severity",
              "value": "medium",
              "compare": "eq"
            }
          ]
        }
      ]
    }
  },
  {
    "hidden": false,
    "name": "issueHighPercent",
    "columnName": "Snyk High %",
    "calc": {
      "type": "MATH",
      "condition": "*",
      "values": [
        {
          "type": "MATH",
          "condition": "/",
          "values": [
            {
              "type": "NUMBER",
              "value": 100
            },
            {
              "type": "FUNCTION",
              "aggregation": "COUNT",
              "path": "resources.snykIssues.id"
            }
          ]
        },
        {
          "type": "FUNCTION",
          "aggregation": "COUNT",
          "path": "resources.snykIssues.id",
          "filter": [
            {
              "type": "compare",
              "field": "resources.snykIssues.details.severity",
              "value": "high",
              "compare": "eq"
            }
          ]
        }
      ]
    }
  },
  {
    "hidden": false,
    "name": "issuep1Percent",
    "columnName": "Snyk p1 %",
    "calc": {
      "type": "MATH",
      "condition": "*",
      "values": [
        {
          "type": "MATH",
          "condition": "/",
          "values": [
            {
              "type": "NUMBER",
              "value": 100
            },
            {
              "type": "FUNCTION",
              "aggregation": "COUNT",
              "path": "resources.snykIssues.id"
            }
          ]
        },
        {
          "type": "FUNCTION",
          "aggregation": "COUNT",
          "path": "resources.snykIssues.id",
          "filter": [
            {
              "type": "compare",
              "field": "resources.snykIssues.details.severity",
              "value": "p1",
              "compare": "eq"
            }
          ]
        }
      ]
    }
  },
  {
    "hidden": false,
    "name": "issuep2Percent",
    "columnName": "Snyk p2 %",
    "calc": {
      "type": "MATH",
      "condition": "*",
      "values": [
        {
          "type": "MATH",
          "condition": "/",
          "values": [
            {
              "type": "NUMBER",
              "value": 100
            },
            {
              "type": "FUNCTION",
              "aggregation": "COUNT",
              "path": "resources.snykIssues.id"
            }
          ]
        },
        {
          "type": "FUNCTION",
          "aggregation": "COUNT",
          "path": "resources.snykIssues.id",
          "filter": [
            {
              "type": "compare",
              "field": "resources.snykIssues.details.severity",
              "value": "p2",
              "compare": "eq"
            }
          ]
        }
      ]
    }
  },
  {
    "hidden": false,
    "name": "issuep3Percent",
    "columnName": "Snyk p3 %",
    "calc": {
      "type": "MATH",
      "condition": "*",
      "values": [
        {
          "type": "MATH",
          "condition": "/",
          "values": [
            {
              "type": "NUMBER",
              "value": 100
            },
            {
              "type": "FUNCTION",
              "aggregation": "COUNT",
              "path": "resources.snykIssues.id"
            }
          ]
        },
        {
          "type": "FUNCTION",
          "aggregation": "COUNT",
          "path": "resources.snykIssues.id",
          "filter": [
            {
              "type": "compare",
              "field": "resources.snykIssues.details.severity",
              "value": "p3",
              "compare": "eq"
            }
          ]
        }
      ]
    }
  },
  {
    "hidden": false,
    "name": "issuep4Percent",
    "columnName": "Snyk p4 %",
    "calc": {
      "type": "MATH",
      "condition": "*",
      "values": [
        {
          "type": "MATH",
          "condition": "/",
          "values": [
            {
              "type": "NUMBER",
              "value": 100
            },
            {
              "type": "FUNCTION",
              "aggregation": "COUNT",
              "path": "resources.snykIssues.id"
            }
          ]
        },
        {
          "type": "FUNCTION",
          "aggregation": "COUNT",
          "path": "resources.snykIssues.id",
          "filter": [
            {
              "type": "compare",
              "field": "resources.snykIssues.details.severity",
              "value": "p4",
              "compare": "eq"
            }
          ]
        }
      ]
    }
  },
  {
    "hidden": false,
    "name": "snykIssuesCount",
    "columnName": "Snyk Issues",
    "calc": {
      "type": "FUNCTION",
      "aggregation": "COUNT",
      "path": "resources.snykIssues.id",
      "filter": []
    }
  },
  {
    "hidden": false,
    "name": "EC2Count",
    "columnName": "EC2Count",
    "calc": {
      "type": "FUNCTION",
      "aggregation": "COUNT",
      "path": "resources.id",
      "filter": [
        {
          "type": "compare",
          "field": "resources.providerResourceType",
          "value": "AWS:EC2:Instance",
          "compare": "eq"
        }
      ]
    }
  }
]
Resulting table from the above JSON

Last updated

Copyright © 2023 configure8, Inc. All rights reserved.