How to create an alert with multiple searches?

[Niro]

Hello,

I have an alert that sends an email when there are x authentication failures , this works fine and returns user,count - but I'd like to also include a table that contains the below fields when the alert trips, how can we go about doing that?

user,src_ip,count

current alert:

index=main action=fail* OR action=block* (source="*WinEventLog:Security" OR sourcetype=linux_secure OR tag=authentication) user!="" |  stats count by user src _time | stats sum(count) as count by user | where count>200
| sort - count

[Niro]

Sorry for the late response!

Thanks all for the help and the, you pointed me in the right direction and I got exactly what I needed. This is what I ended up with:

index=main action=fail* OR action=block* (source="*WinEventLog:Security" OR sourcetype=linux_secure OR tag=authentication) user!="" 
| lookup dnslookup clientip as src OUTPUT clienthost as dns_src
| stats count by user dns_src  
| eventstats sum(count) as total_auth_failures by user | where total_auth_failures>100
| sort - count user dns_src

[JohnEGones · Accepted solution]

Ok, so looking at the reference documentation, we are given:

The differences between these commands are described in the following table:

stats command eventstats command

Events are transformed into a table of aggregated search results	Aggregations are placed into a new field that is added to each of the events in your output
You can only use the fields in your aggregated results in subsequent commands in the search	You can use the fields in your events in subsequent commands in your search, because the events have...

REF: https://docs.splunk.com/Documentation/SplunkCloud/9.0.2305/SearchReference/Eventstats

So, basically, the eventstats command doesn't change any of the events themselves, it just adds the field to the events pulled in from the query, where as stats is  direct calculation from the events. So all of the subsequent actions from stats will be based on the transformations done whereas the eventstats just added a new field with aggregate values?

I'm finding that engaging with alot of this requires a new way of thinking, a so called Splunk and data analyst/engineering mindset that is constrained and molded by what is possible with SPL and Splunk Arch, especially when there are performance or resource constraints that might push an analyst or engineer to prefer or require a certain type of query over another. 

[ITWhisperer]

Correct - SPL is not SQL, SPL processes a pipeline of events (much like *nix commands do when piped together, i.e. the output of one command becomes the input for the next command - hence the use of the pipe symbol (|) to link the commands).

There are certain commands (called non-streaming commands) which wait for all the events to be processed before outputting any events for onward processing. consider sort which needs to have processed all the events before it can determine the correct order to pass the events on. stats commands are similar, in that all the events have to be processed to determine the aggregated result.

[JohnEGones]

@ITWhisperer can you, or perhaps someone else elaborate on this?

[ITWhisperer]

I am assuming that the first part of your search returns the data you want in the alert

index=main action=fail* OR action=block* (source="*WinEventLog:Security" OR sourcetype=linux_secure OR tag=authentication) user!="" |  stats count by user src _time 

By using eventstats, the count for each user is added to all the stats events returned thus far

| eventstats sum(count) as count by user

The where command and sort are taken from your search to filter the results (I included user in the sort in case more than one user had the same count.

[ITWhisperer]

Try using eventstats to preserve the stats events from the first stats command

index=main action=fail* OR action=block* (source="*WinEventLog:Security" OR sourcetype=linux_secure OR tag=authentication) user!="" |  stats count by user src _time 
| eventstats sum(count) as count by user | where count>200
| sort - count user