Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community

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

Copper Contributor

Hi,

 

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

 

 

 

SQLEvent
| 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?

 

Regards,

Tony

6 Replies

@Tony555 

 

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 }}

 

sql_example.png

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!

Regards

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?

 

Regards

 

@Tony555 

 

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

 

| where count > 500

 

Alert Threshold.png

@CliveWatson 

 

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.

 

SQLEvent
| 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.

 

Regards

@Tony555 

 

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

SecurityEvent
| 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)

SecurityEvent
| 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