Azure Cost Management data in R

Question

I would be interested in getting the Azure Cost Management data (especially the Amortized Cost). At first glance, the AzureR package set seems a good starting point.

Would it be possible to somehow use AzureAuth to authorize me and then to try to use the REST API? As for now, I have not even managed to use AzureAuth properly as I do not understand the parameters.

Does any of you work on these data in R? How do you obtain it? Saving CSV file in Azure Portal is an awful experience because it takes a lot of time to set the right filters and Microsoft changes the export schema from time to time. For example today few columns disappeared (i.a. Resource Type) when grouping by a tag.

Any help would be very appreciated : )

Edit: Thanks to the help of Hong Ooi (https://github.com/Azure/AzureR/issues/6) I now managed to connect to the Cost Management API with AzureRMR:

    library(AzureRMR)
    az <- create_azure_login()
    sub1 <- az$get_subscription(my_sub_guid)
    d1 <- sub1$do_operation("providers/Microsoft.CostManagement/query", api_version = "2019-11-01", http_verb = "POST", encode = "json",
                            body = list(
                              timeframe = "Custom",
                              timePeriod = list(
                                from = "2020-04-01",
                                to = "2020-04-01"
                              ),
                              type = "AmortizedCost",
                              dataset = list(
                                granularity = "daily"
                              )
                            ))

What I still don't know is:

How to send the body as JSON and not a list of lists?
How to receive the response as JSON?
How to receive more columns in the response? I especially need Resource Name and Resource Type.

Edit 2: Response example:

response_example <-
  list(properties = list(
    nextLink = NULL,
    columns = list(
      list(name = "UsageDate",
           type = "Number"),
      list(name = "Currency",
           type = "String")
    ),
    rows = list(
      list(as.integer(20200401),
           "EUR"),
      list(as.integer(20200402),
           "EUR")
    )
  ))

What I want to do is to get the data frame out of the response. I found the working solution, but it looks awful:

d1_ <- do.call(what = "rbind", args = lapply(d1$properties$rows, as_tibble, .name_repair = "unique"))
colnames(d1_) <- do.call(what = "rbind", args = lapply(d1$properties$columns, as_tibble, .name_repair = "unique")) %>% 
  select(name) %>% pull()

Edit 3: If anybody needs that I found a way to ask for specific columns. The body is:

{
   "type":"AmortizedCost",
   "dataSet":{
      "granularity":"Daily",
      "aggregation":{
         "PreTaxCost":{
            "name":"PreTaxCost",
            "function":"Sum"
         }
      },
      "sorting":[
         {
            "direction":"ascending",
            "name":"UsageDate"
         }
      ],
      "grouping":[
         {
            "type":"TagKey",
            "name":"myTag"
         },
         {
            "type":"Dimension",
            "name":"ResourceType"
         },
         {
            "type":"Dimension",
            "name":"ResourceGroupName"
         }
      ]
   },
   "timeframe":"Custom",
   "timePeriod":{
      "from":"2020-04-01T00:00:00+00:00",
      "to":"2020-04-30T23:59:59+00:00"
   }
}

The valid parameters (column names) are:

AccountName, BillingAccountId, BillingAccountName, BillingMonth, BillingPeriod, BillingProfileId, BillingProfileName, ChargeType, ConsumedService, CostAllocationRuleId, CostAllocationRuleName, CustomerName, CustomerTenantDomainName, CustomerTenantId, DepartmentName, EnrollmentAccountName, Frequency, InvoiceId, InvoiceNumber, InvoiceSection, InvoiceSectionId, InvoiceSectionName, MarkupRuleId, MarkupRuleName, Meter, MeterCategory, MeterId, MeterSubcategory, PartNumber, PartnerEarnedCreditApplied, PartnerName, PricingModel, Product, ProductOrderId, ProductOrderName, PublisherType, ResellerMPNId, ReservationId, ReservationName, ResourceGroup, ResourceGroupName, ResourceGuid, ResourceId, ResourceLocation, ResourceType, ServiceFamily, ServiceName, ServiceTier, SubscriptionId, SubscriptionName, UnitOfMeasure

Also, this is how I deal with the response: Turning Azure Cost Management API's response into data frame

Hong Ooi · Accepted Answer · 2020-04-23T04:01:10.657

AzureRMR automatically converts the list-of-lists into json. Normally this is the most convenient choice, but you can send raw json text by setting encode="raw":

sub1$do_operation("providers/Microsoft.CostManagement/query", api_version = "2019-11-01",
    http_verb = "POST",
    encode = "raw",
    body = '{"timeframe":"MonthToDate","type":"actualcost","dataset":{"granularity":"daily"}}')

If you want the raw output rather than the parsed output, set http_status_handler="pass". This returns a httr response object; see ?httr::response for more details. Note you'll have to handle errors yourself if you do this.

sub1$do_operation("providers/Microsoft.CostManagement/query", api_version = "2019-11-01",
    http_status_handler = "pass",
    ...)

I'm not familiar with the cost management API, so I can't help you on how to get more columns. I'd suggest contacting tech support if you don't get a response here.

To turn the result into a data frame:

res <- sub1$do_operation(...)
rows <- do.call(rbind, lapply(res$properties$rows, function(r)
{
    names(r) <- sapply(res$properties$columns, `[[`, 1)
    data.frame(r, stringsAsFactors=FALSE)
}))

Thank you, @Hong, one more time. I asked about getting the response as JSON because at that time I couldn't find how to handle the response in the form of the list of lists (cf. Edit 2 for my current solution). — Piotr K, Apr 22 '20 at 20:30

Azure Cost Management data in R

1 Answers1