2

I am trying to trigger an alert when the columns in the AzureDiagnostic Table in Log Analytics is >400 since there is a 500 column limit to the table where records will start dropping.

The issue is Alerts expects and AggregatedValue and a TimeGenerated. Since this is a schema there is not a true Time Generated. I've tried a "time" metric and renaming the column to be "TimeGenerated" but get the following error:

Search Query should contain 'AggregatedValue' and 'bin(TimeGenerated, [roundTo])' for Metric alert type

This is the alert query I have:

AzureDiagnostics
|  getschema
| summarize AggregatedValue = count(ColumnName) by bin(1d, 5m) 
|project AggregatedValue, TimeGenerated=Column1

And I get these results:
enter image description here

DreadedFrost
  • 2,602
  • 1
  • 11
  • 29

2 Answers2

1

I changed my logic to return a record or not. It will return a record only if the threshold has been met of 400 columns and then set my alert Threshold value to > 0.

AzureDiagnostics
|  getschema
| summarize count(ColumnName) 
| where count_ColumnName >400

Alert: enter image description here

DreadedFrost
  • 2,602
  • 1
  • 11
  • 29
1

I'm from the Azure Monitor Log Analytics team. We are actively working in Azure Log Analytics to avoid it all together. We are working now to have dedicated tables for most of Azure resource so it wouldn't overpopulate the AzureDiagnostics table. Some Azure resource like Azure Data Factory have options to control whether it would use the dedicated tables or AzureDiagnistcs. See #4 here: https://learn.microsoft.com/en-us/azure/data-factory/monitor-using-azure-monitor#monitor-data-factory-metrics-with-azure-monitor

MeirM
  • 70
  • 5
  • Thanks, I am aware of this after an extensive ticket with Microsoft. The issue is there is a lack of a comprehensive list of resources which support this as well any indication of a time table of when it will be fully supported. Also there isn't a user friendly work around on removing the columns once you approach the limit( have to manually delete columns through the UI, no Powershelll or CLI to do it) Also the columns get repopulated again when the resource type hits the table again. As such the next best thing is to be alerted when we are close to reaching the limit. – DreadedFrost Dec 29 '19 at 13:34