Cloud Services

Cost & Asset Management

Custom Reports using Graph QL
Published On May 23, 2024 - 11:14 AM

Custom Reports using Graph QL

Create custom reports to analyze your cost and assets.

Creating a Custom Report

Cost & Asset Management (CAM) supports Custom Reports.
To learn more about navigating to the different services from each tenant, refer to Landing page navigation or Kyndryl Bridge Landing page navigation.
To create a custom report, click the
main menu
 from the Kyndryl environment and then click
Admin > Reports
.
  1. In the Report management page, click
    Create New Template
     on the right side.
  2. Give the template a name and select a data source.
  3. Click
    Next: Template Builder
    .The interface shows five elements labeled:
  4. The query Explorer, located on the left of the screen on the Template Builder.
  5. The query editor, located on the right of the query Explorer.
  6. The query results, located on the right of the query editor.
  7. the Documentation Explorer, located to the right of the query results.
  8. The Transformation Template editor, located at the bottom of the Template Builder.
  9. Once the required information has been entered, click
    Save
  10. Optionally, you may associate an Excel template as well. Select
    Yes
     from the 'With Template' dropdown and choose the file that you wish to associate.
  11. Click
    Create Template
     to finish.
Once these elements are in place, there are three stages that occur in sequence when a custom report is generated:
  1. The data is requested using the GraphQL query.
  2. The data is received and transformed with a transformation template.
  3. The data is output to an Excel template, or optionally to a CSV or JSON file.
Report Administrator role is required to create custom report templates.
For more information on GraphQL go to GraphQL query language website.

The GraphQL Query: An Overview

A simple GraphQL query:
{
  costLineItems (
    period: { eq: 202011 }
    ccCustomerTotalCostConverted: { gt: 10 }
  ) {
    providerName
    assetType
    category
    ccCustomerTotalCostConverted
    quantity
    serviceStartDate
  }
}
A GraphQL Query Has Three Parts:
Part 1: The name of the data collection to query.
{
  costLineItems
Part 2: The filters.
{
  costLineItems(
    period: { eq: 202011 }
    ccCustomerTotalCostConverted: { gt: 10 }
  )
Filters are arguments within parentheses after the name of the collection to query.
Above, the
period
 filter is asking for
cosLineItems
 from the billing period of 202011 (November 2020 in YYYYMM notation).
The
ccCustomerTotalCostConverted
 filter is asking for
costLineItems
 with a numeric cost value greater than 10.
Part 3: The Project fields.
{
  costLineItems(
    period: { eq: 202011 }
    ccCustomerTotalCostConverted: { gt: 10 }
  ) {
    assetType
    category
    ccCustomerTotalCostConverted
    providerName
    quantity
    serviceStartDate
  }
}
The items in the curly braces after the filters represent the fields you would like to project in the response. In GraphQL, you specifically list each field you want projected in the response, and only those will be returned.
For example, the above query returns results like this:
{
  "data": {
    "costLineItems": [
      {
        "assetType": "Amazon Elastic Compute Cloud",
        "category": "Compute",
        "ccCustomerTotalCostConverted": 47.205000461,
        "providerName": "aws",
        "quantity": 9442,
        "serviceStartDate": "2020-11-01T00:00:00.000Z"
      },
      {
        "more": "costLineItems..."
      }
    ]
  }
}

The GraphQL Query: In Depth.

The Cost & Asset Management GraphQL schema has been extended beyond basic GraphQL capabilities to allow for several powerful filters. These are:
Group:
 Example of using group within the filter portion of the GraphQL query:
{
  costLineItemsWithDRGs(
    group: [
      { field: "allTags.DeliveryPlatform" }
      { field: "allTags.Toolname" }
      { field: "allTags.RFS-Invoiceable" as: "Invoiceable" }
    ]
  ) {
    grouped_data
  }
}
In the above example, the group filter is grouping values from the tag keys:
allTags.DeliveryPlatform
,
allTags.Toolname
, and
allTags.DeliverRFS-InvoiceableyPlatform
.
About grouped data:
When grouping, the grouped data will be output as an object within the field grouped_data. The
grouped_data
 output field is of JSONObject type and can handle any custom fields you define even if they are not included in the existing schema.
The above-grouped query results in output like this:
{
  "data": {
    "costLineItemsWithDRGs": [
      {
        "grouped_data": {
          "DeliveryPlatform": "Prod",
          "Invoiceable": "Yes",
          "Toolname": "MyToolName"
        }
      },
      {
        "grouped_data": {
          "DeliveryPlatform": "Dev",
          "Invoiceable": "No",
          "Toolname": "MyToolName"
        }
      },
      {
        "more": "costLineItemsWithDRGs..."
      }
    ]
  }
}
Note how the the field
RFS-Invoiceable
 was renamed or aliased to Invoiceable by using as:, for example:
{ field: "allTags.RFS-Invoiceable" as: "Invoiceable" }
. Aliasing can be helpful in a number of use cases: customizing column names to fit your report, avoiding characters in column names that tend to throw errors. In this case the dash (-) in
RFS-Invoiceable
would throw an error, so aliasing to
Invoiceable
 avoids the error.
Accumulators: sum, count, min, max
{
  costLineItemsWithDRGs(
    group: [
      { field: "providerName" }
    ]
    sum: [
      { field: "allTags.CPU-Max" as: "Cores" }
      { field: "allTags.Memory-Max" as: "Memory" }
      { field: "ccCustomerTotalCostConverted" as: "Cost" }
      { field: "quantity" as: "instanceQuantity" }
    ]
    count: [
      { field: "providerAssetId" as: "assetCount" }
    ]
    min:[
      { field:"invoiceStartDate" }
    ]
    max:[
      { field:"invoiceEndDate" }
    ]
  ) {
    grouped_data
  }
Conditional accumulators
{
  costLineItemsWithDRGs(
    group: [
      { field: "providerName" }
    ]
    count: [
      {
          field: "allTags.Environment"
          as: "DevServers"
          if: { field: "allTags.Environment" in: ["DEV", "Dev"] }
      }
      {
          field: "allTags.Environment"
          as: "ProdServers"
          if: { field: "allTags.Environment" in: ["PRD"] }
      }
      {
          field: "allTags.Environment"
          as: "BareMetalServers"
          if: { field: "allTags.Environment" in: ["BMTL"] }
      }
    ]
  ) {
    grouped_data
  }
In the example above, the count fields are incremented conditionally. The
DevServers
 field increments if
allTags
. Environment value is
DEV or Dev
. The
ProdServers
 field increments if the allTags. Environment value is
PRD
. The
BareMetalServers
 field increments if the
allTags.Environment
 value is
BMTL
.
Let:
  {
  costLineItemsWithDRGs(
    let: [
      {
        name: "assetSubType"
        search: "providerAssetId"
        default: "unknown"
        cases:[
          {
            if: { regex:"i-" }
            value: "instance"
          }
          {
            if: { regex:"vol-" }
            value: "volume"
          }
        ]
      }
    ]
  ) {
    providerName
    assetSubType
  }
The
let
 filter allows you to define new fields conditionally.
In the case above, a new field named
assetSubType
 is based on
providerAssetId
.
assetSubType
 is assigned a value of "instance" if
providerAssetId
 contains the string i-, or a value of "volume" if
providerAssetId
 contains the string vol-. If neither of these match,
assetSubType
 will be set to default value of "unknown".
Steps:
{
  costLineItemsWithDRGs(
    steps: [
      {
        group: [
          { field: "providerName" }
          { field: "providerAssetId" }
        ]
        sum: [
          { field: "ccCustomerTotalCostConverted" as: "Cost" }
        ]
      }
      {
        let:[
          {
            name: "assetSubType"
            search: "providerAssetId"
            default: "unknown"
            cases:[
              {
                if: { regex:"i-" }
                value: "instance"
              }
              {
                if: { regex:"vol-" }
                value: "volume"
              }
            ]
          }

        ]
      }
      {
        group: [
          { field: "providerName" }
          { field: "assetSubType" }
        ]
        sum: [
          { field: "Cost" }
        ]
      }
    ]
    ) {
      grouped_data
    }
If you have a complex query where it would be useful to group data, then create new columns with let, then group again, you can use
steps
.
This example query uses steps to first group data by
providerName
 and
providerAssetId
. It derives a new field assetSubType from
providerAssetId
 and it groups again, eliminating the
providerAssetId
 field, and grouping on providerName and
assetSubType
.
It is more efficient to group data as early in the query as possible in order to reduce the number of fields and rows that need to be processed in each succeeding step.

The Transformation template

A transformation template transforms the results from a GraphQL query into a format compatible with CSV or Excel output.
In Custom Reporting, there are three output formats: JSON, CSV or Excel. A transformation template is required for CSV or Excel output, and not required for JSON output.
Some GraphQL query results fields can be in the form of objects or arrays of objects. Fields that are not strings or numbers must be flattened into simple string or numeric fields in order to be compatible with CSV or Excel output.
A transformation template uses JMESPath, a query language for JSON, to accomplish this transformation.
Beyond making data compatible with CSV or Excel output, transformation templates can provide very powerful parsing and reshaping of custom report data. Useful when preparing data for complex Excel spreadsheets.
Minimal Transformation template example:
Where the query results look like this:
{
  "data": {
    "costLineItems": [
      {
        "assetType": "Amazon Elastic Compute Cloud",
        "category": "Compute",
        "ccCustomerTotalCostConverted": 47.205000461,
        "providerName": "aws",
        "quantity": 9442,
        "serviceStartDate": "2020-11-01T00:00:00.000Z"
      },
      {
        "more": "costLineItems..."
      }
    ]
  }
}
A minimal transformation template looks like this:
data.{MyReportName: costLineItems}
Where
data
 is the primary node in the query results. Where
MyReportName
 is a name you choose, and will become the name of the CSV file or the label of the tab on the Excel spreadsheet. And costLineItems is the name of the array of data objects as shown in the query results above.
A transformation template is required for CSV or Excel output. It is not required for JSON output.
Advanced transformation template example:
```data.{MyReportName: costLineItems[?category != "ALL"].{ AssetId: providerAssetId, Assetname: assetName, CapacityText: capacityText, DataCenter: allTags[?key == 'DataCenter'].value | [0], ServerCategory: allTags[?key == 'ServerCategory'].value | [0], Environment: allTags[?key == 'Environment'].value | [0] }}
The advanced transformation template shown above filters out any data where category equals ```All```.

It also pulls specific values from the ```allTags``` array into named columns. For example, the ```DataCenter``` column in the transformation template is populated by a value from the allTags array where the tag key ```== 'DataCenter'.
Grouped_data transformation template:
data.{MyReportName: costLineItemsWithDRGs.{
  DeliveryPlatform: grouped_data.DeliveryPlatform,
  Invoiceable: grouped_data.Invoiceable,
  Toolname: grouped_data.Toolname
}}
Transformation template for multiple queries:
data.{
  sourceDataCosts: myQueryA[].{
    category: grouped_data.category,
    cost: grouped_data.ccCustomerTotalCostConverted
  },
  sourceDataAssets: myQueryB[].{
    category: grouped_data.category,
    cost: grouped_data.ccCustomerTotalCostConverted
  }
}
In this case
sourceDataCosts
 and
sourceDataAssets
 will be the labels of the Source Data tabs in the Excel spreadsheet, if you choose Excel as the output format.
Please review the documentation at (JMESPath)[https://jmespath.org/], to learn more about advanced transformation template capabilities.
Note:
 If your transformation template contains a syntax error, then the query results will not be transformed. JMESPath transformation templates require commas between each expression, but no trailing commas, as shown in the above examples.

The Excel Template

A Custom Report using an Excel template will contain at least two tabs once the report is generated:
  1. Report tab
  2. Source Data tab
An Excel template can be as complex as required, and may include more than one Report tab.
The
Report tabs
 should be formatted as needed for the final report with cell formulas/macros, stylized tables and charts.
The data to be used by the Report tab(s) will come from the Source Data tab(s). One or more
Source Data tabs
 will be added by Custom Reporting when the report is generated.
The label of each
Source Data tab
 is defined in the transformation template. The columns of data in each Source Data tab will appear in alphabetical order based on column name. The names of the columns are defined in the transformation template.
Steps to verify your Excel template:
  1. Upload the Excel template when you save your custom report.
  2. On the reports list page, select
    [Generate Report]
     from the 3-dot overflow menu to the right of the report you just created.
  3. Check the bell icon at top of page for a notification that your report is ready.
  4. Download and open your report -- the Excel document should have one or more new tabs that contain source data.
  5. The formulas/macros should correctly populate the Report tab(s) from the Source Data tab(s).

The Explorer and Docs

The
Explorer
 and the
Docs
 display the GraphQL schema for Cost & Asset Management. The schema defines the collections, filters and project fields available to the GraphQL query.
The
Explorer
 will help you construct a GraphQL query. You can expand sections, check boxes, enter values and a query will be created in the query editor area immediately to the right of the Explorer. Using the Explorer to build your query is optional. You can build your queries by hand if you are more comfortable working that way.
The
Docs
 allow you to inspect the schema without affecting the query in the query editor area.
In both the Explorer and the Docs, collections are shown in blue. Within collections, filter fields are shown in purple, and project fields are shown in blue.
In the Docs section, field definitions are shown in gold.

Deciphering Cost fields

Cost:
 may or may not be populated with a value
PcProviderTotalCost:
 the
pc
 prefix means Provider Currency. This field contains the total cost from the provider in the provider currency.
CcProviderTotalCost:
 the
cc
 prefix means Customer Currency. This field contains the total cost from the provider converted to Customer Currency.
CcCustomerTotalCost:
 This is the cost field that should be used for reports in most cases.
ccCustomerTotalCost
 is what is shown in Cost & Asset Management dashboards.

Special Filter Fields

limit:
 A filter field that limits the number of records returned. If you want no limit to the number of records returned, set limit to a very high value. For example: limit: 1000000000.
vtag:
 vtag is the backend working name of Dynamic Resource Group. When you filter on vtag, you are filtering on the name of a Dynamic Resource Group.
vattribute:
 vattribute is the backend working name of Dynamic Tag. When you filter on vattribute, you are filtering on the key/value of a Dynamic Tag.
persist:
 A filter field that tells GraphQL to write the output of your query to a collection. Useful if you have a very long running report. [Beta feature]
(
append
 and
override
 filter fields are deprecated.)

Special Project fields

persist_result:
 If you set persist: true in your query, the persist_result project field will display the name of the collection that contains the output of your query. [Beta feature]
grouped_data:
 Contains the fields formed by a grouped query. See About Grouped Data.
For the following project functions it is possible to accomplish the same result using a transformation template.
group
 (get: A project function that pulls data specifically from the grouped_data field into a new field. Example:
InstanceCount: group(get: "instanceQuantity")
concat:
 A project function that concatenates one or more fields into a new field. Example:
providerLocation: concat(values: ["providerName", "location"], sep: "-")
substr:
 A project function that populates a new field with a substring of another field. Example:
providerCountry: substr(field: "location", start: 0, end: 1)
jmespath:
 A project function that uses jmespath syntax to pull data from an object or array of objects into a new field. Example:
serverName: jmespath(search: "allTags.Name")
Do you have two minutes for a quick survey?
Take Survey