SOLVED

Alert from an HDInsight Query

%3CLINGO-SUB%20id%3D%22lingo-sub-968495%22%20slang%3D%22en-US%22%3EAlert%20from%20an%20HDInsight%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-968495%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirstly%2C%20apologies%20for%20what%20I'm%20sure%20is%20a%20really%20simple%20question!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20alert%20on%20CRITICAL%20events%20on%20our%20Production%20HDInsight%20instance.%20I've%20created%20the%20Kusto%20query%20below%2C%20that%20polls%20for%20critical%20events%20in%20non-maintenance%20mode%2C%20and%20then%20summarises%20on%20original%20timestamp%2C%20so%20I%20collect%20all%20events%20associated%20with%20the%20same%20'href'%20together.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20that's%20all%20good%2C%20but%20I%20want%20to%20alert%20on%20this.%20As%20I%20see%20a%20new%20'href'%2C%20I%20want%20to%20be%20able%20to%20trigger%20an%20alert%20on%20distinct%20Alert_text%2C%20just%20once.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20try%20to%20create%20a%20new%20alert%20from%20the%20query%20text%20in%20Log%2C%20it%20says%20I%20need%20an%20aggregated%20value%20-%20I'm%20assuming%20I%20would%20trigger%20if%20the%20aggregated%20value%20was%20greater%20than%20'0'%20on%20the%20Original_timestamp%2C%20but%20I'm%20not%20sure%20how%20to%20structure%20the%20query%20to%20do%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emetrics_cluster_alerts_CL%3CBR%20%2F%3E%7C%20where%20Alert_cluster_name_s%20%3D%3D%20%22enthdipd%22%20and%20Alert_state_s%20%3D%3D%20%22CRITICAL%22%20and%20Alert_maintenance_state_s%20%3D%3D%20%22OFF%22%3CBR%20%2F%3E%7C%20summarize%20arg_max(Alert_original_timestamp_d%2C%20*)%20by%20Alert_component_name_s%2C%20href_s%2C%20Alert_text_s%3CBR%20%2F%3E%7C%20sort%20by%20Alert_component_name_s%20asc%20%2C%20Alert_original_timestamp_d%20desc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-968495%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECustom%20Logs%20and%20Custom%20Fields%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-970414%22%20slang%3D%22en-US%22%3ERe%3A%20Alert%20from%20an%20HDInsight%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-970414%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F430883%22%20target%3D%22_blank%22%3E%40CampbellT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20a%20series%20of%20blog%20posts%20that%20focus%20on%20Azure%20Monitor%20Alerts.%20The%20blog%20post%20about%20Log%20Analytics%20alerts%20is%20here%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fcloudadministrator.net%2F2019%2F10%2F07%2Fazure-monitor-alert-series-part-7%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcloudadministrator.net%2F2019%2F10%2F07%2Fazure-monitor-alert-series-part-7%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EYou%20can%20check%20it%20out%20as%20there%20I%20am%20explaining%20some%20concepts%20including%20using%20metric%20measurement%20alerts%20sub-type%20for%20event%20based%20log%20which%20seems%20to%20be%20your%20case.%3C%2FP%3E%0A%3CP%3EThe%20query%20for%20your%20alert%20will%20probably%20look%20like%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Emetrics_cluster_alerts_CL%0A%7C%20where%20Alert_cluster_name_s%20%3D%3D%20%22enthdipd%22%20and%20Alert_state_s%20%3D%3D%20%22CRITICAL%22%20and%20Alert_maintenance_state_s%20%3D%3D%20%22OFF%22%0A%7C%20extend%20AggregatedValue%20%3D%201%20%0A%7C%20summarize%20arg_max(Alert_original_timestamp_d%2C%20*)%20by%20Alert_component_name_s%2C%20href_s%2C%20Alert_text_s%2C%20bin(TimeGenerated%2C%205m)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ENote%20that%20I%20have%20put%20AggregatedValue%20that%20is%20equal%20to%201%20so%20your%20threshold%20will%20be%20greater%20than%200%20in%20your%20alert%20configuration.%20Besides%20aggregated%20value%20bin()%20usage%20is%20also%20required%20for%20metric%20measurement%20alerts.%20Usually%20you%20will%20set%20the%20bin%20interval%20(in%20the%20above%20case%205m)%20to%20the%20time%20window%20you%20configure%20the%20alert.%20You%20will%20also%20have%20to%20metric%20column%20or%20columns%20when%20you%20create%20the%20alert.%20Those%20will%20be%20the%20unique%20values%20that%20compromise%20a%20single%20instance%20for%20alert.%3C%2FP%3E%0A%3CP%3ELet%20me%20know%20if%20you%20have%20figured%20all%20the%20details%20and%20managed%20to%20configure%20the%20desired%20alert.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-971461%22%20slang%3D%22en-US%22%3ERe%3A%20Alert%20from%20an%20HDInsight%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-971461%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20is%20perfect%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9172%22%20target%3D%22_blank%22%3E%40Stanislav%20Zhelyazkov%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20newbie%20to%20Kusto%2C%20I%20really%20need%20to%20wrap%20my%20head%20around%20the%20structure%20of%20the%20query%20language.%20I'm%20getting%20there%20-%20once%20I%20am%2C%20I%20see%20that%20this%20can%20be%20extremely%20powerful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20response.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, 

 

Firstly, apologies for what I'm sure is a really simple question!!

 

I'm trying to alert on CRITICAL events on our Production HDInsight instance. I've created the Kusto query below, that polls for critical events in non-maintenance mode, and then summarises on original timestamp, so I collect all events associated with the same 'href' together. 

 

Now, that's all good, but I want to alert on this. As I see a new 'href', I want to be able to trigger an alert on distinct Alert_text, just once. 

 

When I try to create a new alert from the query text in Log, it says I need an aggregated value - I'm assuming I would trigger if the aggregated value was greater than '0' on the Original_timestamp, but I'm not sure how to structure the query to do this. 

 

Thanks in advance, 

 

metrics_cluster_alerts_CL
| where Alert_cluster_name_s == "enthdipd" and Alert_state_s == "CRITICAL" and Alert_maintenance_state_s == "OFF"
| summarize arg_max(Alert_original_timestamp_d, *) by Alert_component_name_s, href_s, Alert_text_s
| sort by Alert_component_name_s asc , Alert_original_timestamp_d desc

 

2 Replies
Best Response confirmed by CampbellT (New Contributor)
Solution

Hi@CampbellT 

I have a series of blog posts that focus on Azure Monitor Alerts. The blog post about Log Analytics alerts is here:

https://cloudadministrator.net/2019/10/07/azure-monitor-alert-series-part-7/

You can check it out as there I am explaining some concepts including using metric measurement alerts sub-type for event based log which seems to be your case.

The query for your alert will probably look like this:

metrics_cluster_alerts_CL
| where Alert_cluster_name_s == "enthdipd" and Alert_state_s == "CRITICAL" and Alert_maintenance_state_s == "OFF"
| extend AggregatedValue = 1 
| summarize arg_max(Alert_original_timestamp_d, *) by Alert_component_name_s, href_s, Alert_text_s, bin(TimeGenerated, 5m)

Note that I have put AggregatedValue that is equal to 1 so your threshold will be greater than 0 in your alert configuration. Besides aggregated value bin() usage is also required for metric measurement alerts. Usually you will set the bin interval (in the above case 5m) to the time window you configure the alert. You will also have to metric column or columns when you create the alert. Those will be the unique values that compromise a single instance for alert.

Let me know if you have figured all the details and managed to configure the desired alert.

That is perfect, @Stanislav Zhelyazkov 

 

As a newbie to Kusto, I really need to wrap my head around the structure of the query language. I'm getting there - once I am, I see that this can be extremely powerful.

 

Thanks for the response.