I’m trying to execute two JSON queries in order to join them into one table.
First query will return the resource name and the databytes processed by it.
Second query will return the resource name and the cost. So the table should look have these columns: Name, Databytes, Cost
Problem is the first query returns the resource name like this:
/subscriptions/XXX/resourceGroups/PAW/providers/Microsoft.Network/azureFirewalls/AzureFirewall_VWAN-HUB/providers/Microsoft.Insights/metrics/DataProcessed
And the second query returns a different resource name structure:
/subscriptions/XXX/resourcegroups/paw/providers/microsoft.network/azurefirewalls/azurefirewall_vwan-hub
As far as I understand the outer join will only work if the names are identical. So I would need to use regex to extract the resource name without all the /XXX/ before and after the name (marked in red).
/subscriptions/XXX/resourceGroups/PAW/providers/Microsoft.Network/azureFirewalls/AzureFirewall_VWAN-HUB/providers/Microsoft.Insights/metrics/DataProcessed
And the second query returns this resource name (marked in red):
/subscriptions/XXX/resourcegroups/paw/providers/microsoft.network/azurefirewalls/azurefirewall_v
How can I produce the same results for the join to work properly?
What target visualization do you want to plot this on
Also please post some sample data?
Hi
This should be presented in a table like this:
Resource Name / Data processed / Cost
AAA 365353453 $22
Please post sample data returned by api query. The json
Cost API results (replaced subscription ID with XXX):
{
"id": "subscriptions/XXXXX/providers/Microsoft.CostManagement/query/ff4cc502-7c90-4c35-bc67-b3b060fe3a8c",
"name": "ff4cc502-7c90-4c35-bc67-b3b060fe3a8c",
"type": "Microsoft.CostManagement/query",
"location": null,
"sku": null,
"eTag": null,
"properties": {
"nextLink": null,
"columns": [
{
"name": "PreTaxCost",
"type": "Number"
},
{
"name": "ResourceId",
"type": "String"
},
{
"name": "Currency",
"type": "String"
}
],
"rows": [
[
609.0549451402087,
"/subscriptions/XXX/resourcegroups/paw/providers/microsoft.network/azurefirewalls/azurefirewall_vwan-hub",
"USD"
]
]
}
}
FW metric API results:
{
"cost": 239,
"timespan": "2023-02-23T01:00:00Z/2023-02-23T05:00:00Z",
"interval": "P1D",
"value": [
{
"id": "/subscriptions/XXXX/resourceGroups/DMZ-Network-RG/providers/Microsoft.Network/azureFirewalls/DMZ-Firewall/providers/Microsoft.Insights/metrics/DataProcessed",
"type": "Microsoft.Insights/metrics",
"name": {
"value": "DataProcessed",
"localizedValue": "Data processed"
},
"displayDescription": "Total amount of data processed by this firewall",
"unit": "Bytes",
"timeseries": [
{
"metadatavalues": [],
"data": [
{
"timeStamp": "2023-02-23T01:00:00Z",
"total": 19610734178
}
]
}
],
"errorCode": "Success"
}
],
"namespace": "Microsoft.Network/azureFirewalls",
"resourceregion": "westeurope"
}
1 Like
simonarbel:
PAW
is the above the resource group you will be joining the 2 queries on? You mentioned stuff highlited in red but we dont see any colors.
Also where is Data processed coming from it’s value?
Another issue with your data
Why are these different on the same data point?
/subscriptions/XXX/resourceGroups/PAW/providers/Microsoft.Network/azureFirewalls/AzureFirewall_VWAN-HUB /providers/Microsoft.Insights/metrics/DataProcessed
/subscriptions/XXX/resourcegroups/paw/providers/microsoft.network/azurefirewalls/azurefirewall_v wan-hub
I marked the required strings in bold. Just need the correct regex and a way to join the 2 queries
1 Like
How about this above data. Not seeing it in the json you posted?
I have two data sources using the infinity plugin where each one calls an API with $fwid and $subscription as dashboard variables
sorry but that still does not answer the question I asked. Where is “Data processed” column data coming from?
Is it a derived data or what?
“Data processed” is a metric returned by the second API
Is this where the data is located?
Yes this is the header and down the output there is the actual data.
BTW I found the right regex to extract the FW name:)(?=/providers)|([^/] )$
However when using this in the “rename by regex” transformation it doesn’t work.
You are a squirmy one. Unable to get you to answer exactly the question asked to help us help you.
Post and point exactly where that data is in the json from the api.
So post the part of the json in the json format pointing to that data. This could have been answered a while back
sorry for the misunderstanding
The actual “data proceeded” is in the “total:” object
{
Anyone can help me please?
