analyzeDBR.config

[general]
namespace = "DBR"

[ri]
enableRIanalysis = true
enableRITotalUtilization = true # Set this to true to get a total RI percentage utilization value.
riPercentageThreshold = 5 # Ignore un-used RI's where percentage of under-use lower than this value
riTotalThreshold = 5 # Ignore un-used RI's where total number of RI's (per instance type) is below this.
cwNameTotal = "riTotalUtilization"
cwName = "riUnderUtilization"
cwDimension = "instance"
cwDimensionTotal = "total"
cwType = "Percent"
sql = """
  SELECT distinct
    COALESCE(
       regexp_extract(itemdescription,'per(?:\\sOn Demand)*\\s(.*?)(?:\\s\\(Amazon VPC\\))*,*\\s([a-z]\\d\\.\\d*\\w+)\\s', 1),
       regexp_extract(itemdescription,'^([a-z]\\d\\.\\d*\\w+)\\s(.*?)(?:\\s\\(Amazon VPC\\))*\\sSpot',2)
       ) AS platform,
   COALESCE(
      regexp_extract(itemdescription,'per(?:\\sOn Demand)*\\s(.*?)(?:\\s\\(Amazon VPC\\))*,*\\s([a-z]\\d\\.\\d*\\w+)\\s', 2),
      regexp_extract(itemdescription,'^([a-z]\\d\\.\\d*\\w+)\\s(.*?)(?:\\s\\(Amazon VPC\\))*\\sSpot',1)
      ) AS instance,
    substr(usagestartdate, 1, 13) AS date,
    availabilityzone AS az,
    count(*) AS hours
  FROM dbr.autodbr_**DATE**
  WHERE productname = 'Amazon Elastic Compute Cloud'
  AND operation like '%RunInstances%'
  AND usagetype like '%Usage%'
  AND reservedinstance = 'Y'
  AND split_part(usagetype, ':', 2) is not NULL
  AND length(availabilityzone) > 1
  AND length(usagestartdate) > 1
  AND try_cast(usagestartdate as timestamp) IS NOT NULL
  AND try_cast(usagestartdate as timestamp) > now() - interval '72' hour
  AND try_cast(usagestartdate as timestamp) < now()
  GROUP BY
    COALESCE(
       regexp_extract(itemdescription,'per(?:\\sOn Demand)*\\s(.*?)(?:\\s\\(Amazon VPC\\))*,*\\s([a-z]\\d\\.\\d*\\w+)\\s', 1),
       regexp_extract(itemdescription,'^([a-z]\\d\\.\\d*\\w+)\\s(.*?)(?:\\s\\(Amazon VPC\\))*\\sSpot',2)
       ),
   COALESCE(
      regexp_extract(itemdescription,'per(?:\\sOn Demand)*\\s(.*?)(?:\\s\\(Amazon VPC\\))*,*\\s([a-z]\\d\\.\\d*\\w+)\\s', 2),
      regexp_extract(itemdescription,'^([a-z]\\d\\.\\d*\\w+)\\s(.*?)(?:\\s\\(Amazon VPC\\))*\\sSpot',1)
      ),
    substr(usagestartdate, 1, 13),
    availabilityzone
"""
[ri.ignore] ## Ignore un-used RI's in this map/hash
"t2.micro" = 1
"m1.small" = 1 # This has to be ignored as RI usage in DBR file for this instance type is not accurate

[[metrics]]
## Count of Instance purchase types (RI, Spot, onDemand) per hour"
enabled = true
type = "dimension-per-row"
cwName = "InstancePurchaseType"
cwDimension = "type"
cwType = "Count"
sql = """
    SELECT distinct
      substr(split_part(usagetype, ':', 1), strpos(split_part(usagetype, ':', 1), '-') + 1, 10) AS dimension,
      substr(usagestartdate, 1, 13) AS date,
      count(*) AS value
    FROM dbr.autodbr_**DATE**
    WHERE productname = 'Amazon Elastic Compute Cloud'
    AND operation like '%RunInstances%'
    AND usagetype like '%Usage%'
    AND try_cast(usagestartdate as timestamp) IS NOT NULL
    AND try_cast(usagestartdate as timestamp) > now() - interval '24' hour
    AND try_cast(usagestartdate as timestamp) < now()
    GROUP BY
      substr(split_part(usagetype, ':', 1), strpos(split_part(usagetype, ':', 1), '-') + 1, 10),
      substr(usagestartdate, 1, 13)
    ORDER BY substr(usagestartdate, 1, 13) desc \
"""

[[metrics]]
## Summary of Overall Cost per hour
enabled = true
type = "dimension-per-row"
cwName = "TotalCost"
cwDimension = "cost"
cwType = "None"
sql = """
    SELECT
      'total' as dimension,
      substr(usagestartdate, 1, 13) AS date,
      sum(cast(**COST** as double)) AS value
    FROM dbr.autodbr_**DATE**
    WHERE length(usagestartdate) >= 19
    AND try_cast(usagestartdate as timestamp) IS NOT NULL
    AND try_cast(usagestartdate as timestamp) > now() - interval '24' hour
    AND try_cast(usagestartdate as timestamp) < now()
    GROUP BY substr(usagestartdate, 1, 13)
    ORDER BY substr(usagestartdate, 1, 13) desc \
"""


[[metrics]]
## Summary of Cost per service per hour
enabled = true
type = "dimension-per-row"
cwName = "ServiceCost"
cwDimension = "service"
cwType = "None"
sql = """
    SELECT
      productname AS dimension,
      substr(usagestartdate, 1, 13) AS date,
      sum(cast(**COST** as double)) AS value
    FROM dbr.autodbr_**DATE**
    WHERE length(usagestartdate) >= 19
    AND try_cast(usagestartdate as timestamp) IS NOT NULL
    AND try_cast(usagestartdate as timestamp) > now() - interval '24' hour
    AND try_cast(usagestartdate as timestamp) < now()
    GROUP BY
      productname,
      substr(usagestartdate, 1, 13)
    HAVING sum(cast(**COST** as double)) > 0
    ORDER BY substr(usagestartdate, 1, 13), productname desc \
"""

[[metrics]]
## Count of Instance Types per Hour
enabled = true
type = "dimension-per-row"
cwName = "InstanceType"
cwDimension = "instance"
cwType = "Count"
sql = """
  SELECT distinct
    COALESCE(
       regexp_extract(itemdescription,'per(?:\\sOn Demand)*\\s(.*?)(?:\\s\\(Amazon VPC\\))*,*\\s([a-z]\\d\\.\\d*\\w+)\\s', 2),
       regexp_extract(itemdescription,'^([a-z]\\d\\.\\d*\\w+)\\s(.*?)(?:\\s\\(Amazon VPC\\))*\\sSpot',1)
       ) AS dimension,
    substr(usagestartdate, 1, 13) AS date,
    count(*) AS value
  FROM dbr.autodbr_**DATE**
  WHERE productname = 'Amazon Elastic Compute Cloud'
  AND operation like '%RunInstances%'
  AND usagetype like '%Usage%'
  AND try_cast(usagestartdate as timestamp) IS NOT NULL
  AND try_cast(usagestartdate as timestamp) > now() - interval '24' hour
  AND try_cast(usagestartdate as timestamp) < now()
  GROUP BY
  COALESCE(
     regexp_extract(itemdescription,'per(?:\\sOn Demand)*\\s(.*?)(?:\\s\\(Amazon VPC\\))*,*\\s([a-z]\\d\\.\\d*\\w+)\\s', 2),
     regexp_extract(itemdescription,'^([a-z]\\d\\.\\d*\\w+)\\s(.*?)(?:\\s\\(Amazon VPC\\))*\\sSpot',1)
     ),
    substr(usagestartdate, 1, 13)
  ORDER BY substr(usagestartdate, 1, 13) desc
"""

[[metrics]]
## Count of Linked Account per hour
## Only enable this if you have linked accounts
enabled = false
type = "dimension-per-row"
cwName = "AccountCost"
cwDimension = "accountid"
cwType = "None"
sql = """
    SELECT distinct
      linkedaccountid AS dimension,
      substr(usagestartdate, 1, 13) AS date,
      sum(cast(blendedcost as double)) AS value
    FROM dbr.autodbr_**DATE**
    WHERE length(usagestartdate) >= 19
    AND try_cast(usagestartdate as timestamp) IS NOT NULL
    AND try_cast(usagestartdate as timestamp) > now() - interval '24' hour
    AND try_cast(usagestartdate as timestamp) < now()
    AND length(linkedaccountid) > 1
    GROUP BY
      linkedaccountid,
      substr(usagestartdate, 1, 13)
    ORDER BY
      substr(usagestartdate, 1, 13),
      sum(cast(blendedcost as double)) desc
"""

[athena]
create_database = "create database if not exists `dbr` comment \"AutoDBR Athena Database\""
create_table = """
  create external table if not exists `dbr.autodbr_**DATE**` (
    `InvoiceID` string,
    `PayerAccountId` string,
    `LinkedAccountId` string,
    `RecordType` string,
    `RecordId` string,
    `ProductName` string,
    `RateId` string,
    `SubscriptionId` string,
    `PricingPlanId` string,
    `UsageType` string,
    `Operation` string,
    `AvailabilityZone` string,
    `ReservedInstance` string,
    `ItemDescription` string,
    `UsageStartDate` string,
    `UsageEndDate` string,
    `UsageQuantity` string,
    `Rate` string,
    `Cost` string
  )
  STORED AS PARQUET
  LOCATION 's3://**BUCKET**/dbr-parquet/**ACCOUNT**-**DATE**/' \
  """
create_table_blended = """
  create external table if not exists `dbr.autodbr_**DATE**` (
    `InvoiceID` string,
    `PayerAccountId` string,
    `LinkedAccountId` string,
    `RecordType` string,
    `RecordId` string,
    `ProductName` string,
    `RateId` string,
    `SubscriptionId` string,
    `PricingPlanId` string,
    `UsageType` string,
    `Operation` string,
    `AvailabilityZone` string,
    `ReservedInstance` string,
    `ItemDescription` string,
    `UsageStartDate` string,
    `UsageEndDate` string,
    `UsageQuantity` string,
    `BlendedRate` string,
    `BlendedCost` string,
    `UnBlendedRate` string,
    `UnBlendedCost` string
    )
    STORED AS PARQUET
    LOCATION 's3://**BUCKET**/dbr-parquet/**ACCOUNT**-**DATE**/' \
    """