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
  10. Optionally, you may associate an Excel template as well. Select
    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
filter is asking for
from the billing period of 202011 (November 2020 in YYYYMM notation).
filter is asking for
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:
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:
, and
About grouped data:
When grouping, the grouped data will be output as an object within the field grouped_data. The
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
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
would throw an error, so aliasing to
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
field increments if
. Environment value is
DEV or Dev
. The
field increments if the allTags. Environment value is
. The
field increments if the
value is
{ costLineItemsWithDRGs( let: [ { name: "assetSubType" search: "providerAssetId" default: "unknown" cases:[ { if: { regex:"i-" } value: "instance" } { if: { regex:"vol-" } value: "volume" } ] } ] ) { providerName assetSubType }
filter allows you to define new fields conditionally.
In the case above, a new field named
is based on
is assigned a value of "instance" if
contains the string i-, or a value of "volume" if
contains the string vol-. If neither of these match,
will be set to default value of "unknown".
{ 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
This example query uses steps to first group data by
. It derives a new field assetSubType from
and it groups again, eliminating the
field, and grouping on providerName and
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}
is the primary node in the query results. Where
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
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)[], to learn more about advanced transformation template capabilities.
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.
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

and the
display the GraphQL schema for Cost & Asset Management. The schema defines the collections, filters and project fields available to the GraphQL query.
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.
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

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

Special Filter Fields

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 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 is the backend working name of Dynamic Tag. When you filter on vattribute, you are filtering on the key/value of a Dynamic Tag.
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]
filter fields are deprecated.)

Special Project fields

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]
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.
(get: A project function that pulls data specifically from the grouped_data field into a new field. Example:
InstanceCount: group(get: "instanceQuantity")
A project function that concatenates one or more fields into a new field. Example:
providerLocation: concat(values: ["providerName", "location"], sep: "-")
A project function that populates a new field with a substring of another field. Example:
providerCountry: substr(field: "location", start: 0, end: 1)
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