No data receive for SQL Azure database using Azure by HTTP template

[c_hasle]

Hello,

In my organisation we recently decided to install Azure by HTTP templates in our Zabbix server to automatically discover and monitor our Azure VMs and databases.
We followed this documentation :
Microsoft Azure monitoring and integration with Zabbix

After setting up an Azure application, linking the templates to the host, and setting all the macros in the templates, this works perfectly well with the VMs but we get an error with the API requests for the databases. The error we get is returned in the data error field as shown in the attachment and is the following :

Failed to receive data:
cpu_percent,physical_data_read_percent,log_write_p ercent,storage,connection_successful,connection_fa iled,blocked_by_firewall,deadlock,storage_percent, xtp_storage_percent,workers_percent,sessions_perce nt,sessions_count,cpu_limit,cpu_used,sqlserver_pro cess_core_percent,sqlserver_process_memory_percent ,tempdb_data_size,tempdb_log_size,tempdb_log_used_ percent : Request failed with status code 400: {"additionalInfo":[{"type":"string","info":"TraceId={dac6475c-6d48-46f6-be16-e3303aed4ae5}"},{"type":"string","info":"Exception Type=Microsoft.Online.Metrics.MetricsMP.Utilities. RPRequestFormatException"}],"code":"BadRequest","message":"Failed to find metric configuration for provider: Microsoft.Sql, resource Type: servers/databases, metric: cpu_limit, Valid metrics: cpu_percent,physical_data_read_percent,log_write_p ercent,dtu_consumption_percent,storage,connection_ successful,connection_failed,connection_failed_use r_error,blocked_by_firewall,deadlock,storage_perce nt,xtp_storage_percent,workers_percent,sessions_pe rcent,sessions_count,dtu_limit,dtu_used,sqlserver_ process_core_percent,sqlserver_process_memory_perc ent,tempdb_data_size,tempdb_log_size,tempdb_log_us ed_percent,allocated_data_storage,ledger_digest_up load_success,ledger_digest_upload_failed"}
full_backup_size_bytes,diff_backup_size_bytes,log_ backup_size_bytes : Request failed with status code 400: {"additionalInfo":[{"type":"string","info":"TraceId={aaad348d-de23-449e-bb4c-aa3b212d6509}"},{"type":"string","info":"Exception Type=Microsoft.Online.Metrics.MetricsMP.Utilities. RPRequestFormatException"}],"code":"BadRequest","message":"Failed to find metric configuration for provider: Microsoft.Sql, resource Type: servers/databases, metric: full_backup_size_bytes, Valid metrics: cpu_percent,physical_data_read_percent,log_write_p ercent,dtu_consumption_percent,storage,connection_ successful,connection_failed,connection_failed_use r_error,blocked_by_firewall,deadlock,storage_perce nt,xtp_storage_percent,workers_percent,sessions_pe rcent,sessions_count,dtu_limit,dtu_used,sqlserver_ process_core_percent,sqlserver_process_memory_perc ent,tempdb_data_size,tempdb_log_size,tempdb_log_us ed_percent,allocated_data_storage,ledger_digest_up load_success,ledger_digest_upload_failed"}

The thing is, it manages to get basic information like the space allocated and the database status, but that's almost all.
It looks like there is setup to do in the Azure portal so the metrics are available for pickup by the API, but there is nothing about this in the documentation and it works for the VMs without any additional setup.
In the metrics section in Azure, the metrics are readable and the Azure application has the read right on the resource group containing the databases.
Could it be a version problem ? The bad request error makes me feel like the metrics are not supported by the Azure data warehouse and maybe the template is not up to date.

Has anyone already encountered this ?
I would like some help with the debugging process I can follow, all suggestions are welcome.
Also, is there anywhere the API requests are logged, so I can check and test them myself ?

Have a nice day!

[DM93]

I can +1 this issue, I used "Azure by HTTP template" to add a host with specified macros for a certain Azure Sub, that sub does contain SQL servers, those were discovered and added as hosts into Zabbix but they show error almost same as in the OP. I see an exclamation mark for most items and this error shows:
"1. Failed: cannot extract value from json by path "$.metrics.blocked_by_firewall.total": no data matches the specified path"

[hkalchev]

Same error from my end also

[hkalchev]

Hello, I found the solution, it seems that the metrics are breaking the query, start with only one, and then check which is breaking the query, in my case the temp metrics were the problem, so with this query, I was able to get what I wanted:






var metrics = [
'storage',
'cpu_percent',
'physical_data_read_percent',
'cpu_limit',
'cpu_used',
'allocated_data_storage',
'storage_percent',
'xtp_storage_percent',
'sessions_percent',
'sessions_count',
'blocked_by_firewall',
'connection_failed',
'connection_successful',
'workers_percent',
'log_write_percent',
'deadlock',
],
day_metrics = 'full_backup_size_bytes,diff_backup_size_bytes,log _backup_size_bytes',
prepared_metrics = [],
data = {};
data['errors'] = {};
data['metrics'] = {};[/HTML]

[galoxucro]

'day_metrics' were a problem to me.

Isn't that a template bug? There shouldn't be a way of discovering the valid metrics from another API call, or at least a macro to control which metrics shouldn't be queried in API calls, i.e. {$AZMETRICS.NOT_MATCHES}?

[smit]

Hi guys!

I have the same issue

How did you managed to fix it?
If I delete one metric, its an error appears again, related to another and etc, one- by-one

{"errors":"Failed to receive data:\ncpu_percent,physical_data_read_percent,log_ write_percent,storage,connection_successful,connec tion_failed,blocked_by_firewall,deadlock,storage_p ercent,xtp_storage_percent,workers_percent,session s_percent,sessions_count,cpu_limit,cpu_used,sqlser ver_process_core_percent,sqlserver_process_memory_ percent,tempdb_data_size,tempdb_log_size,tempdb_lo g_used_percent : Request failed with status code 400: {"error":{"additionalInfo":[{"type":"string","info":"TraceId={060933e6-067b-44ef-8d18-f57a329c7191}"},{"type":"string","info":"Exception Type=Microsoft.Online.Metrics.MetricsMP.Utilities. RPRequestFormatException"}],"code":"BadRequest","message":"Failed to find metric configuration for provider: Microsoft.Sql, resource Type: servers/databases, metric: cpu_limit, Valid metrics: cpu_percent,physical_data_read_percent,log_write_p ercent,dtu_consumption_percent,storage,connection_ successful,connection_failed,connection_failed_use r_error,blocked_by_firewall,deadlock,storage_perce nt,xtp_storage_percent,workers_percent,sessions_pe rcent,sessions_count,dtu_limit,dtu_used,sqlserver_ process_core_percent,sqlserver_process_memory_perc ent,sql_instance_cpu_percent,sql_instance_memory_p ercent,tempdb_data_size,tempdb_log_size,tempdb_log _used_percent,allocated_data_storage,ledger_digest _upload_success,ledger_digest_upload_failed"}}\nfu ll_backup_size_bytes,diff_backup_size_bytes,log_ba ckup_size_bytes : Request failed with status code 400: {"error":{"additionalInfo":[{"type":"string","info":"TraceId={a4d44fb2-b52a-4e9c-a555-9605bc6c4b2a}"},{"type":"string","info":"Exception Type=Microsoft.Online.Metrics.MetricsMP.Utilities. RPRequestFormatException"}],"code":"BadRequest","message":"Failed to find metric configuration for provider: Microsoft.Sql, resource Type: servers/databases, metric: full_backup_size_bytes, Valid metrics: cpu_percent,physical_data_read_percent,log_write_p ercent,dtu_consumption_percent,storage,connection_ successful,connection_failed,connection_failed_use r_error,blocked_by_firewall,deadlock,storage_perce nt,xtp_storage_percent,workers_percent,sessions_pe rcent,sessions_count,dtu_limit,dtu_used,sqlserver_ process_core_percent,sqlserver_process_memory_perc ent,sql_instance_cpu_percent,sql_instance_memory_p ercent,tempdb_data_size,tempdb_log_size,tempdb_log _used_percent,allocated_data_storage,ledger_digest _upload_success,ledger_digest_upload_failed"}}","m etrics":{"allocated_data_storage":{"timeStamp":"20 23-09-11T11:14:00Z","average":50331648}},"health":{"avai labilityState":"Available","title":"Available","su mmary":"There aren't any known Azure platform problems affecting this SQL database or SQL data warehouse.","reasonType":"","category":"Not Applicable","context":"Not Applicable","occuredTime":"2023-08-12T00:00:00Z","reasonChronicity":"Persistent","rep ortedTime":"2023-09-11T11:15:02.6321282Z"}}

[smit]

Hey guys!

Ive created a zabbix bug report about fixing this templates

pls vote

https://support.zabbix.com/browse/ZBX-23394

[Edwinmng83]

Hi Anyone,

We're using Azure SQL managed instances, but when we tried to use Azure Microsoft SQL Serverless Database by HTTP, doesn't work can't get all the data. If anyone found a way to fix, please send us some image your Azure setup and Zabbix setup step by step to guide us, thank you.

But I can get data in Zabbix for Azure Postgresql and MySQL no issue at all.

[anslu]

I know where the issue is. Zabbix is not being able to differentiate one Azure MSSQL database from another. Simply there are few types of MS SQL available, and each is accepting different API calls, rarely supporting the same API calls globally.

So, from the Monitoring > Latest Data > Pick appropriate AZ SQL/DB host > near "Get Error", users can click on the "Failed to receive data" info and expand it.
Orange indicates the problem
Yellow shows unsupported API call
Green shows supported options

Template is automatically assigned based on the resource type, Label H


E.g. /subscriptions/123/resourceGroups/ABC/providers/Microsoft.Sql/servers/SQLHostName/databases/DBName123
But it's not able to tell whether it's e.g. Azure SQL Database Hyperscale or Dedicated SQL pool

What's the difference?
One type won't support some calls, the other will


What can be done?
- Ideally, find a way to differentiate one AZ MSSQL from another and assigning different templates for each DB type (API calls must be adjusted)
- The workaround is re-check what API calls are supported for your workloads or remove the ones non-supported globally, leaving the checks a bit crippled

Go to Template > Items > Hit on Get Data and edit the script. Then either uncomment or replace the API with supported one, each time the output of the "Get errors" will be different now. All supported calls can be found in MS dosc or at Azure from resource level by going to Metrics.