Oct 07 2021 05:43 AM - edited Oct 07 2021 05:51 AM
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
Oct 07 2021 09:27 AM
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
Oct 08 2021 12:55 AM
Oct 11 2021 01:00 AM
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
Oct 11 2021 02:02 AM
You can have a final line like this in the query, or use Alert Threshold
| where count > 500
Oct 11 2021 03:10 AM
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
Oct 11 2021 03:23 AM
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 |