Splunk Search

How to search the count of a certain field value since a specific time in a subsearch?

gletallec
Engager

I'm almost finished with my search When I do this, search I've got what I want, but my count is not correct...
*I would like to count the status "0xc000006d" of a User on a Subject since his LastSucces. *
Here I count all results with this status since the beginning ..

Could you help me please ? 🙂

index = security EventCode = 4625
| where Status=="0xc000006d"
| eval timeE=strftime(_time, "%Y-%m-%d %H:%M:%S") 
| append [
           search index = security EventCode = 4624 
           | eval timeS=strftime(_time, "%Y-%m-%d %H:%M:%S") 
          ]
| stats first(timeE) as LastEchec first(timeS) as LastSucces by Target_User_Name SubjectUserName
| eval st = if(LastSucces!= "", "OK", "NOK")
| eval compare=strptime(LastEchec,"%Y-%m-%d %H:%M:%S") 
| where (st=="NOK" OR strptime(LastEchec,"%Y-%m-%d %H:%M:%S")>strptime(LastSucces,"%Y-%m-%d %H:%M:%S")) 
| fillnull value=NULL LastSucces
| eval diff = tostring((now()-strptime(LastEchec,"%Y-%m-%d %H:%M:%S")), "duration") 

| join Target_User_Name SubjectUserName 
         [
           search index = security EventCode = 4625 Status="0xc000006d" earliest= **LastSucces**
           | stats count by Target_User_Name SubjectUserName
         ]
| fields Target_User_Name SubjectUserName LastEchec LastSucces diff count
Tags (3)
0 Karma
1 Solution

lguinn2
Legend

I think this will work

index = security (EventCode = 4625 Status="0xc000006d") OR (EventCode = 4624)
| eval timeE=if(EventCode==4625,_time,null())
| eval timeS=if(EventCode==4624,_time,null())
| eval countit=if(EventCode==4625 AND Status=="0xc000006d",_time,null())
| stats latest(timeE) as LastEchec latest(timeS) as LastSucces list(countit) as times
        by Target_User_Name SubjectUserName
| eval st = if(LastSucces!= "", "OK", "NOK")
| where st=="NOK" OR LastEchec>LastSucces
| mvexpand times
| where times>LastSucces
| stats first(LastEchec) as LastEchec first(LastSucces) as LastSucces count
         by Target_User_Name SubjectUserName
| eval diff = tostring(now()-LastEchec, "duration")
| eval LastEchec=strftime(LastEchec,"%Y-%m-%d %H:%M:%S")
| eval LastSucces=strftime(LastSucces,"%Y-%m-%d %H:%M:%S")
| table Target_User_Name SubjectUserName LastEchec LastSucces diff count

If it doesn't work, let me know. I am sure that I can get rid all but the last subsearch. The most efficient way to do things in Splunk is to provide a base search that collects all the data in a single pass. It isn't always possible, but it is a good rule to put as much as possible in the base search.

Another good rule is to avoid subsearches and commands such as join that use them. Also, leave dates in epoch time to compare them, and only translate to a user-friendly format at the end.

The only real trick in my search was to preserve the time of each event until after LastSucces was calculated, so that the count of events could be computed.

View solution in original post

lguinn2
Legend

I think this will work

index = security (EventCode = 4625 Status="0xc000006d") OR (EventCode = 4624)
| eval timeE=if(EventCode==4625,_time,null())
| eval timeS=if(EventCode==4624,_time,null())
| eval countit=if(EventCode==4625 AND Status=="0xc000006d",_time,null())
| stats latest(timeE) as LastEchec latest(timeS) as LastSucces list(countit) as times
        by Target_User_Name SubjectUserName
| eval st = if(LastSucces!= "", "OK", "NOK")
| where st=="NOK" OR LastEchec>LastSucces
| mvexpand times
| where times>LastSucces
| stats first(LastEchec) as LastEchec first(LastSucces) as LastSucces count
         by Target_User_Name SubjectUserName
| eval diff = tostring(now()-LastEchec, "duration")
| eval LastEchec=strftime(LastEchec,"%Y-%m-%d %H:%M:%S")
| eval LastSucces=strftime(LastSucces,"%Y-%m-%d %H:%M:%S")
| table Target_User_Name SubjectUserName LastEchec LastSucces diff count

If it doesn't work, let me know. I am sure that I can get rid all but the last subsearch. The most efficient way to do things in Splunk is to provide a base search that collects all the data in a single pass. It isn't always possible, but it is a good rule to put as much as possible in the base search.

Another good rule is to avoid subsearches and commands such as join that use them. Also, leave dates in epoch time to compare them, and only translate to a user-friendly format at the end.

The only real trick in my search was to preserve the time of each event until after LastSucces was calculated, so that the count of events could be computed.

gletallec
Engager

Thanks for all your advices ! 🙂

It's work !! (just change | where times>lastSucces with | where times>LastSucces)

lguinn2
Legend

Good debugging! I fixed my answer, for the record.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...