CloudHealth

Level 200 FlexReports

Author: Ivan Tzonev

CloudHealth Reporting

CloudHealth has multiple capabilities that support the “Understanding Cloud Usage and Cost” FinOps domain. CloudHealth’s standard reporting features help you easily understand your cloud cost and usage. However, you can also use FlexReports to enhance and extend your data reporting capabilities.

Image 1: CloudHealth's standard Cost History report

What are FlexReports?

FlexReports use the same raw billing data from the Cloud Service Providers (CSPs) as the other reporting options in CloudHealth. The FlexReport user interface gives you an easy visual editor as well as the option to craft complex SQL statements directly.  Both methods let you create reports that fit your requirements. 

In the standard FlexReport editor UI, you can easily create a report by choosing specific Measures, Dimensions and Filters. The FlexReport UI automatically creates a SQL snippet, which then can be further edited to create more complex reports if needed. 

Example FlexReport: Azure Virtual Machines Cost and Usage 

The following configuration would create a simple Azure Virtual Machines report that contains cost and usage information as well as some specifics about the virtual machines. It is only created by using the UI editor.

Measures: 

• ActualCostReportingCurrency
• Quantity

Dimensions:

• TimeInterval
• MeterCategory – To be able to filter the Service – Virtual Machines
• MeterSubCategory – To show Virtual Machines Sizes
• MeterRegion – To show Virtual Machines Regions
• ResourceGroup

Filter:

• MeterCategory = "Virtual Machines”

Image 2: Azure Virtual Machines Cost and Usage FlexReport Editor

SQL Query

{
"sqlStatement" : 
 " SELECT
  timeInterval_Month AS Month,
  SUM(ActualCostInReportingCurrency) AS SUM_ActualCostInReportingCurrency,
  SUM(AmortizedCostInReportingCurrency) AS SUM_AmortizedCostInReportingCurrency,
  SUM(Quantity) AS SUM_Quantity,
  MeterCategory AS MeterCategory,
  MeterSubCategory AS MeterSubCategory,
  AvailabilityZone AS AvailabilityZone,
  MeterName AS MeterName,
  MeterRegion AS MeterRegion
FROM
  AZURE_COST_USAGE_MONTHLY
WHERE
  (MeterCategory IN ('Virtual Machines'))
GROUP BY
  timeInterval_Month,
  MeterCategory,
  MeterSubCategory,
  AvailabilityZone,
  MeterName,
  MeterRegion
ORDER BY
  timeInterval_Month DESC ",
"needBackLinkingForTags" : false ,
"timeRange" : {
  "last": 3
} ,
"limit" : -1
}

Using the FlexReport editor UI, you can easily configure your report with just a few mouse clicks. 

This is also a good learning opportunity if you are not familiar with SQL or the underlying data structure of the CSP billing files.  You do not need that knowledge yet, but as you progress with more complex FlexReports, that knowledge is essential.

Example FlexReport: AWS EC2 Instance Report

Similar to the Azure Virtual Machines report, we are able to create a report but for AWS EC2 Instance Cost and Usage simply using the UI.

It is important to remember that in FlexReports each CSP has its own dataset.
Each CSP provides its data in its own format and CloudHealth stores and references the data separately.

Measures:

• lineItem_UnblendedCost
• lineItem_UsageAmount

Dimensions:

• TimeInterval
• product_instanceFamily - To be able to filter the Product – Compute Instance
• product_productFamily – To show EC2 Instance type
• lineItem_UsageType – To show EC2 Instance size
• product_region – To show provisioned region

Filter:

• productFamily = ‘Compute Instance’

Image 3: AWS EC2 Instance Cost and Usage FlexReport Editor

SQL Query

{
"sqlStatement" : 
 " SELECT
  timeInterval_Month AS Month,
  SUM(lineItem_UnblendedCost) AS SUM_lineItem_UnblendedCost,
  SUM(lineItem_UsageAmount) AS SUM_lineItem_UsageAmount,
  lineItem_UsageType AS LineItem_UsageType,
  product_instanceFamily AS Product_instanceFamily,
  product_productFamily AS Product_productFamily,
  product_region AS Product_region
FROM
  AWS_CUR
WHERE
  (
    lineItem_LineItemType NOT IN ('SavingsPlanNegation')
  )
  AND (lineItem_Operation LIKE '%RunInstance%')
  AND (
    product_ProductName IN ('Amazon Elastic Compute Cloud')
  )
GROUP BY
  timeInterval_Month,
  lineItem_UsageType,
  product_instanceFamily,
  product_productFamily,
  product_region ",

"needBackLinkingForTags" : true ,
"dataGranularity" : "MONTHLY" ,
"timeRange" : {
  "last": 3
} ,
"limit" : -1
}

More Advanced (Level 200) Example FlexReports

The FlexReport SQL engine can be used to create even more complex reports. There are a number of available functions which can be configured using the SQL snippet. Using this capability, we are able to obtain more granular data, create specific calculations or combine different datasets from the different CSPs. In the section below, we are going to explore the following use cases:

• Simple calculations
• Create SQL temporary tables
• Combine datasets

A list and example of the supported SQL functions can be found in the product documentation.  Please see below for some example FlexReports using some of these functions.

Example FlexReport: Simple Calculations for Azure Virtual Machines Hourly Rates per VM Size 

Image 4: Azure Virtual Machines hourly rates per VM Size FlexReport Editor

SQL Query

{
"sqlStatement" : 
 " SELECT
  timeInterval_Month AS Month,
  SUM(ActualCostInReportingCurrency) AS SUM_ActualCostInReportingCurrency,
  SUM(AmortizedCostInReportingCurrency) AS SUM_AmortizedCostInReportingCurrency,
  SUM(Quantity) AS SUM_Quantity,
  CAST(
    (SUM(ActualCostInBillingCurrency) / SUM(Quantity)) AS DECIMAL(4, 2)
  ) AS Actual_Hourly_Cost,
  MeterCategory AS MeterCategory,
  MeterSubCategory AS MeterSubCategory,
  ChargeType AS ChargeType,
  AvailabilityZone AS AvailabilityZone,
  MeterName AS MeterName,
  MeterRegion AS MeterRegion
FROM
  AZURE_COST_USAGE_MONTHLY
WHERE
  (MeterCategory IN ('Virtual Machines'))
GROUP BY
  timeInterval_Month,
  MeterCategory,
  MeterSubCategory,
  ChargeType,
  AvailabilityZone,
  MeterName,
  MeterRegion ",

"needBackLinkingForTags" : false ,
"timeRange" : {
  "last": 3
} ,
"limit" : -1
}

Example FlexReport: Creating SQL Temporary Tables 

This FlexReport shows Azure Virtual Machines and their associated Disks. Temporary tables allow you to synthesize your own datasets or merge different data to create your report. The report shows Virtual Machine and associated Disk Cost and Usage and provides additional data about the assets.

Image 5: Azure Virtual Machines and associated Disks FlexReport Editor

SQL Query

{
"sqlStatement" : 
 " WITH
  \"cxtemp_azure_disks\" AS (
    SELECT
      timeInterval_Month AS Month,
      SUM(CostInReportingCurrency) AS DiskCost,
      SUM(Quantity) AS DiskUsage,
      split_part (split_part (ResourceId, '/', 9), '_', 1) AS VMNameExtractedFromDiskName,
      split_part (ResourceId, '/', 9) AS DiskName
    FROM
      AZURE_COST_USAGE
    WHERE
      (
        ConsumedService IN ('Microsoft.Compute')
        AND split_part (ResourceId, '/', 8) IN ('disks')
      )
    GROUP BY
      timeInterval_Month,
      split_part (ResourceId, '/', 9),
      split_part (split_part (ResourceId, '/', 9), '_', 1)
  ),
  \"cxtemp_azure_vms\" AS (
    SELECT
      timeInterval_Month AS Month,
      SUM(CostInReportingCurrency) AS VMCost,
      SUM(Quantity) AS VMUsage,
      split_part (ResourceId, '/', 9) AS VMName
    FROM
      AZURE_COST_USAGE
    WHERE
      (
        ConsumedService IN ('Microsoft.Compute')
        AND split_part (ResourceId, '/', 8) IN ('virtualMachines')
        AND (MeterCategory IN ('Virtual Machines'))
      )
    GROUP BY
      timeInterval_Month,
      split_part (ResourceId, '/', 9)
  )
SELECT
  cxtemp_azure_disks.Month AS Month,
  CAST(cxtemp_azure_disks.DiskCost AS DECIMAL(20, 2)) AS Disk_Cost,
  CAST(cxtemp_azure_disks.DiskUsage AS DECIMAL(20, 2)) AS Disk_Usage,
  CAST(cxtemp_azure_vms.VMCost AS DECIMAL(20, 2)) AS VM_Cost,
  cxtemp_azure_disks.DiskName AS Disk_Name,
  cxtemp_azure_vms.VMName AS VM_Name
FROM
  cxtemp_azure_disks
  FULL JOIN cxtemp_azure_vms ON cxtemp_azure_vms.VMName = cxtemp_azure_disks.VMNameExtractedFromDiskName
WHERE
  cxtemp_azure_vms.VMName IS NOT NULL
  AND cxtemp_azure_vms.Month = cxtemp_azure_disks.Month ",

"needBackLinkingForTags" : false ,
"dataGranularity" : "DAILY" ,
"timeRange" : {
  "last": 90
} ,
"limit" : -1
}

Example FlexReport: Combining Datasets - Multi-Cloud Cost History by Service

SQL Query

{
"sqlStatement" : 
 " WITH
  cxtemp_azure AS (
    SELECT
      timeInterval_Month AS Month,
      SUM(ActualCostInReportingCurrency) AS Cost,
      MeterCategory AS Product,
      'Azure' AS Provider
    FROM
      AZURE_COST_USAGE_MONTHLY
    GROUP BY
      timeInterval_Month,
      MeterCategory
  ),
  cxtemp_aws AS (
    SELECT
      timeInterval_Month AS Month,
      SUM(lineItem_UnblendedCost) AS Cost,
      product_productName AS Product,
      'AWS' AS Provider
    FROM
      AWS_CUR
    GROUP BY
      timeInterval_Month,
      product_productName
  )
SELECT
  Month as Month,
  Provider,
  Product,
  Cost
FROM
  cxtemp_azure
UNION ALL
SELECT
  Month AS Month,
  Provider,
  Product,
  Cost
FROM
  cxtemp_aws
ORDER BY
  Month ",

"dataGranularity" : "MONTHLY" ,
"timeRange" : {
  "last": 3
} ,
"limit" : -1
}

How can CloudHealth help with FlexReports? 

CloudHealth has a number of standard reporting capabilities to make it easy to see you cloud cost and usage, but you can also use FlexReports for simple-but-customized reports or create complex aggregations of data that is transformed to meet the needs of your business.  This helps you to get the right data, to the right people, so they can continue to make the right decisions.