Hi @Nadhiya_Dubai,
Try this:
| spath path=properties.rows{}{} output=values | nomv values | eval values=split(values, "USD") | mvexpand values | rex field=values "^\s*(?<PreTaxCost>[\d\.]+)\s*(?<UsageDate>[\d]+)$" | where isnotnull(PreTaxCost) and isnotnull(UsageDate) | spath | table id, name, eTag, name, type, PreTaxCost, UsageDate, property.nextLink
Sample query:
| makeresults | eval _raw="{\"sku\": null, \"name\": \"b0b36773-618e-4490-9c11-57eaf5cb0f49\", \"properties\": {\"rows\": [[37.39104138816985, 20200301, \"USD\"], [37.45212432304218, 20200302, \"USD\"], [37.19654530403741, 20200303, \"USD\"], [37.34428721156659, 20200304, \"USD\"], [38.220217017864336, 20200305, \"USD\"], [37.70977490679157, 20200306, \"USD\"], [37.53643148801985, 20200307, \"USD\"], [37.47332013352408, 20200308, \"USD\"], [27.71226225182299, 20200309, \"USD\"]], \"columns\": [{\"type\": \"Number\", \"name\": \"PreTaxCost\"}, {\"type\": \"Number\", \"name\": \"UsageDate\"}, {\"type\": \"String\", \"name\": \"Currency\"}], \"nextLink\": null}, \"eTag\": null, \"location\": null, \"type\": \"Microsoft.CostManagement/query\", \"id\": \"subscriptions/2808ca16-1c43-4d1c-8088-223b7c378d46/providers/Microsoft.CostManagement/query/b0b36773-618e-4490-9c11-57eaf5cb0f49\"}" | spath path=properties.rows{}{} output=values | nomv values | eval values=split(values, "USD") | mvexpand values | rex field=values "^\s*(?<PreTaxCost>[\d\.]+)\s*(?<UsageDate>[\d]+)$" | where isnotnull(PreTaxCost) and isnotnull(UsageDate) | spath | table id, name, eTag, name, type, PreTaxCost, UsageDate, property.nextLink
... View more