How to show amount of query results as entity on incident created in Azure Sentinel

New Contributor



I need to do a simple monitoring on the amount of event logs from our SQL databases to our log analytics space.


The query looks like this




| where TimeGenerated > ago (15min)




Then I'm configuring Alert Threshold to trigger an incident if the amount of query result is fewer than 500.


I would like to use Entity mapping to map the query number result so I quickly can see the number without the need of running the query manually.


Is this possible to create?




6 Replies



I'd actually do this another way (but you can choose):


1. First if you just need a count, only return that data, like in this example SQLEvent | count , the rule can also define the lookback and schedule time to 15mins for both 


2. You can use the Alert Details feature, to display the count from step 1 into the Alert name - see the Alert Name format example below.  In my example the returned column was "Count" so I put that in double curly braces {{  Count }}



Now I get a Alert title that includes the number/count from {{ Clount }}, so I see this without needing to go into an entity or even open the Incident   

SQL example 2.png

Thank you!

This was a better suggestion how to handle this!


Hi @CliveWatson 


I just realized that by this rule logic I will have incidents created every time the analytics rule being executed since the " | count" will always be equal to 1.


My intention is that I want incident to be created if the count is lower than a specific number (500 or something).


Is there any way to still keep "{{ count }}" in the incident name by doing some magic on the rule itself?






You can have a final line like this in the query, or use Alert Threshold


| where count > 500


Alert Threshold.png



Alert Threshold based on query result only works if I list all raw events from SQL server without processing the result with "count" since the outcome will always be 1 in number of query result.


I was hoping that there is a workaround to extract "count" to Alert name but it might not be possible at the same time only generate Alert if the amount is below 500 events.


Also i'm trying to see how it would work with "| where count < 500" but I can't get it to work.


| count 
| where count < 500

Query could not be parsed at '<' on line [3,14] Token: < Line: 3 Position: 14


I want to trigger alert if the amount of events is below 500 for a 15 minute period.





Sorry, I was doing it from my memory, the second count should be Count (with an uppercase "C"). e.g. 

| where TimeGenerated > startofday(ago(7d))
| count 
| extend status_ = iif(Count < 500,0,1)

You can build test logic, in this case, if the value is below 500, status == 0, above 500 then status == 1  (you can use true or false, rather than 0 and 1)

You can display the value as well or even a custom message - in a column I've called "status_" (feel free to change)

| where TimeGenerated > startofday(ago(7d))
| count 
| extend status_ = iif(Count < 500,strcat('Good, under 500: ',Count),strcat('Bad, over 500: ', Count))

Go to Log Analytics and run query

Count status_
3233512 Bad, over 500: 3233512