Splunk Enterprise Security

How to list values using tstats in Splunk ES



I am using below search query which list's out the sequence of login using standard querying. What the below query does is it gives me the authentication actions as list. I am looking for those security events which gets succeed after multiple failures. The below query does the job but i want to use it using tstats command as the below conventional query is quite slow.

sourcetype="WinEventLog:Security" EventCode=4624 OR EventCode=4625 | stats count sparkline as trend values(user) as Users max(_time) as maxtime min(_time) as mintime values(difference) as difference list(action) as list values(src_bunit) as src_bunit values(dest_bunit) as dest_bunit values(dvc_bunit) as dvc_bunit values(user_email) as user_email,values(Failure_Reason) as Failure_Reason, values(signature) as signature,values(Error_Code) as Error_Code by user | eval list = mvjoin(list, " ") | eval alert = if(match(list, "(?:failure\s?){4,}(?:success)"), "True", "False") | where alert = "True" | eval diff = round((maxtime - mintime)/60,0) | eval timediff = mvindex(split(diff, "-"),1) | eval maxtime=strftime(maxtime,"%H:%M:%S") | eval mintime=strftime(mintime,"%H:%M:%S") | where count>100 | fields - diff | where timediff<=60

I tried constructing the below query but list() function doesn't seems to be working with tstats command and hence i am unable to get the exact sequence. In the below query list(Authentication.action) as list doesn't work and hence the issue. If i use values() it will only give me success & failure but not the sequence and hence i am not able to find success after four failures.

| tstats summariesonly=t earliest(_time) as maxtime, latest(_time) as mintime, values(Authentication.dest_nt_domain) as dest_nt_domain,values(Authentication.signature) as signature, list(Authentication.action) as list,values(Authentication.user_email) as user_email,values(Authentication.dest) as dest ,values(Authentication.body) as body,values(Authentication.signature_id) as signature_id values(Authentication.Error_Code) as Error_Code, count as EventCount from datamodel=Authentication where sourcetype="WinEventLog:Security" (Authentication.signature_id="4624" OR Authentication.signature_id="4625") groupby Authentication.user | eval list = mvjoin(list, " ") | eval alert = if(match(list, "(?:failure\s?){4,}(?:success)"), "True", "False") | where alert = "True" | eval diff = round((maxtime - mintime)/60,0) | eval timediff = mvindex(split(diff, "-"),1) | eval maxtime=strftime(maxtime,"%H:%M:%S %Z") | eval mintime=strftime(mintime,"%H:%M:%S %Z") | where EventCount>=50 | fields - diff | where timediff<=60

Why I am using "list" cmd?

To get failure followed by a success action sequence like below example

After list command, | eval list = mvjoin(list, " ") | eval alert = if(match(list, "(?:failure\s?){4,}(?:success)"), "True", "False") | where alert = "True"

Example: failure failure failure failure success failure success

How can i address the above scenario using datamodel/tsats?

0 Karma


PLZ upvote if you use this!

Copy out all field names from your DataModel. (move to notepad++/sublime/or text editor of your choice).
1. add "values" command and the inherited/calculated/extracted DataModel pretext field to each fields in the tstats query
(in the following example I'm using "values(authentication.YourDataModelField)
*note add host, source, sourcetype without the authentication.fieldname - as they are already in tstats so is time but I use this to groupby)
2. add "from" clause to choose your DataModel (in the following example I'm using authentication DataModel)
3. add "where" clause to specify field values (in the following example I'm using action=failure and limiting the signature
id to the 3 windows failures I care about in this usecase) also can specify nodename or child datamodel object/etc - note you cannot wildcard this field)
4. add "by" clause to help narrow the dataset (in the following example I'm using user, src, signature_id, and _time)
5. table all (note this will give you a table view of all the data in that datamodel - I like to use this as it makes more sense to me starting with everything and removing what I do not need)

| tstats count as count values(Authentication.user)
, values(Authentication.tag)
, values(Authentication.dest_bunit)
, values(Authentication.dest_category)
, values(Authentication.dest_nt_domain)
, values(Authentication.dest_priority)
, values(Authentication.duration)
, values(Authentication.response_time)
, values(Authentication.signature)
, values(Authentication.signature_id)
, values(Authentication.src_bunit)
, values(Authentication.src_category)
, values(Authentication.src_nt_domain)
, values(Authentication.src_priority)
, values(Authentication.src_user_bunit)
, values(Authentication.src_user_category)
, values(Authentication.src_user_priority)
, values(Authentication.user_bunit)
, values(Authentication.user_category)
, values(Authentication.user_priority)
, values(Authentication.action) as action
, values(Authentication.app) 
, values(Authentication.src)
, values(Authentication.src_user) 
, values(Authentication.dest)
, values(host)
, values(source)
, values(sourcetype)
from datamodel="Authentication"."Authentication" 
where Authentication.action=failure
(Authentication.signature_id=4625 OR Authentication.signature_id=4772 OR Authentication.signature_id=4771)
by Authentication.user, Authentication.src, Authentication.signature_id, _time
| table *

Lastly, specify the fields you want - replace those in the tstats and table commands, add post processing stats/rex/lookups/ etc.
(note this part I did not show in example)

and as Christopher Walken would say "BAM!"

Esteemed Legend

It cannot be done with tstats because it does not support list (which I think is silly). Your only option is similar to what @lakshman239 suggested: use something like this:

|tstats count
FROM datamodel=Authentication 
WHERE index=* AND (Authentication.action="failure" OR Authentication.action="success")
BY Authentication.action _time span=5m

And build out from there.


How about you use values(Authentication.action) instead of list and then use span=5m _time group by class, so you get a time series data and you can count the success and failure counts to match your alert? will that work in your scenario?

0 Karma


I have tried similar to what you have mentioned but i dont see that working!

0 Karma