Splunk Search
Highlighted

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

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
Highlighted

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

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

Highlighted

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

Engager

Thanks for all your advices ! 🙂

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

Highlighted

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

Legend

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

0 Karma