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
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.
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.
Thanks for all your advices ! 🙂
It's work !! (just change | where times>lastSucces
with | where times>LastSucces
)
Good debugging! I fixed my answer, for the record.