How to configure Splunk custom alert?

[devdattajogleka]

Hello,

I am configuring a custom splunk alert. My search query is as follows

| dbxquery connection="FFconed_feTenant" query="select count(file_name) as file_count, DATE_FORMAT(created_at,\"%m/%d/%y %W\") as date from ida_files_inventory
where created_at > Date_sub(Curdate(), INTERVAL 30 Day) and created_at < Curdate() group by DATE_FORMAT(created_at,\"%m/%d/%y %W\")" | fields file_count,date |where file_count<100 | chart
avg(file_count) as avg_count

I want to send an alert when the file_count is less than 0.95*avg_count or greater than 1.5*avg_count
So can I configure a custom alert with condition "search file_count < (0.95*avg_count) OR file_count > (1.5*avg_count)"

[devdattajogleka]

Actually I want to take average of all 'file_count's which are less than 100. Thats why I have applied where condition first.

[ITWhisperer]

I am not suggesting you remove that where command, I am suggesting you replace the chart with an eventstats and another where command

[ITWhisperer · Accepted solution]

Use eventstats rather than chart to get the average in each event and then use a where command to find the events which are outside the bounds

[devdattajogleka]

Yeah got that. Alert is working. Thank you so much.

I want to add one more condition to it. The alert should the include the file_count's of last week only. I used following query for that.

| dbxquery connection="FFconed_feTenant" query="select count(file_name) as file_count, DATE_FORMAT(created_at,\"%m/%d/%y %W\") as date from ida_files_inventory
where created_at > Date_sub(Curdate(), INTERVAL 30 Day) and created_at < Curdate() group by DATE_FORMAT(created_at,\"%m/%d/%y %W\")" | fields file_count,date | where file_count<100 | eventstats  avg(file_count) as avg_count | where file_count < (avg_count*0.875) or file_count > (avg_count*1.125) | fields file_count,date | where date > relative_time(now(), "-7d@d")

Used "-w" instead of  "-7d@d" Also tried using strftime by specifying date format, using 'earliest' time modifier but the query does not yeild any result whereas it should. Please suggest a solution.

[ITWhisperer]

I suspect date is coming back as a string from dbxquery, therefore you need to parse that into an epoch time so you can compare it with the value returned by relative_time() - try something like this

| where strptime(date,"%m/%d/%y %W") > relative_time(now(), "-7d@d")