Splunk Enterprise Security

How to list values using tstats in Splunk ES

ashish9433
Communicator

Hi,

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

dakeesling
Engager

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!"

woodcock
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.

lakshman239
SplunkTrust
SplunkTrust

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

ashish9433
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...