search

MicrosoftDocs / azure-docs

Open source documentation of Microsoft Azure
https://docs.microsoft.com/azure
Creative Commons Attribution 4.0 International
10.25k stars 21.43k forks source link

Azure SQL Analytic Query is not working ... #67399

Closed lee-alderdice-mgh closed 3 years ago

lee-alderdice-mgh commented 3 years ago

Hi. The query below receives the error at the bottom.

let cpu_percentage_threshold = 95; let time_threshold = ago(1h); AzureDiagnostics | where Category == "ResourceUsageStats" and TimeGenerated > time_threshold | summarize avg_cpu = max(todouble(avg_cpu_percent_s)) by ResourceId | where avg_cpu > cpu_percentage_threshold

'summarize' operator: Failed to resolve scalar expression named 'avg_cpu_percent_s'

Thanks.

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

SwathiDhanwada-MSFT commented 3 years ago

@lee-alderdice-mgh Thanks for your comment. We will review the issue and get back to you shortly.

SwathiDhanwada-MSFT commented 3 years ago

@lee-alderdice-mgh I have reproduced the issue from my end, and the query provided in the document is indeed correct. For your reference, providing screenshot of the query executed in my environment.

image

Note : To verify the query , I changed the last line of query to check threshold below 95 for validation purposes.

As there are no documentation changes, we will close the issue for now. If there are further questions , please revert and we will be glad to assist you.

danimir commented 3 years ago

Correct. If your CPU has not hit 95% at any time, there will be no long records, therefore error will appear. It might seem like the query is not working - but it is just not finding any records that your CPU went over 95%. Decreasing this threshold down to the percentage your CPU was utilized for the given time period will return a result. Another important thing - your diagnostic settings to send the telemetry to Log Analytics workspace must be enabled as a pre-requirement. Hope this helps.

lee-alderdice-mgh commented 3 years ago

Ah I see. I did not grasp that at all then, wow. Apologies for wasting your time there.

From: Danimir Ljepava notifications@github.com Sent: 11 December 2020 08:11 To: MicrosoftDocs/azure-docs azure-docs@noreply.github.com Cc: Lee Alderdice Lee.Alderdice@medigold-health.com; Mention mention@noreply.github.com Subject: Re: [MicrosoftDocs/azure-docs] Azure SQL Analytic Query is not working ... (#67399)

CAUTION: This email originated from outside the organisation. Do not click links or open attachments unless you recognise the sender and know the content is safe.

Correct. If your CPU has not hit 95% at any time, there will be no long records, therefore error will appear. It might seem like the query is not working - but it is just not finding any records that your CPU went over 95%. Decreasing this threshold down to the percentage your CPU was utilized for the given time period will return a result. Hope this helps.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/67399#issuecomment-743043293, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AR7OR4BY2J35AAWGIRAOARDSUHH3ZANCNFSM4UTQ67RQ.

lee-alderdice-mgh commented 3 years ago

Hi,

Please see the screenshot. It's not about data returned but more that I am getting an error in the query. In AzureDiagnostics I don't have the column avg_cpu_percent_s ... do you think I have set something up wrong to cause this?

image

danimir commented 3 years ago

In Azure Diagnostics you need to enable "ResourceUsageStats" This is because ResourceUsageStats contains vCores count, average CPU percentage, IO requests, bytes read/written, reserved storage space, and used storage space, as per the documentation page.