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
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
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.
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
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
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.
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.
@ITWhisperer can you, or perhaps someone else elaborate on this?
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.
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