Token Date in a search with join

[yassy]

I have this search. My problem is that the result only results in seven days. If I do only the first part, before the Union, the result is correct, but when I execute the two searches, the results are not correct.

earliest=-15d@d latest=now index=wineventlog (EventCode="4624") 
|regex Account_Name!=".*\$"                     
|eval Day=strftime(_time,"%d/%m/%Y")   
|eval User=mvindex(Account_Name,1)     
|eval Time=strftime(_time,"%d/%m/%Y %H.%M.%S")
|stats earliest(Time) as PrimerLogin by User,host,Day  
|eval desde=strptime(PrimerLogin,"%d/%m/%Y %H.%M.%S") 
|join  User,host,Day
[search earliest=-15d@d latest=now index=wineventlog (EventCode="4634")  
|regex Account_Name!=".*\$"
|eval Day=strftime(_time,"%d/%m/%Y")
|eval User=Account_Name
|eval Time=strftime(_time,"%d/%m/%Y %H.%M.%S")
|stats latest(Time) as UltimoLogoff by User,host,Day 
|eval hasta= strptime(UltimoLogoff,"%d/%m/%Y %H.%M.%S")
]
|eval segundos_usados=hasta-desde 
|eval diff=if(0>segundos_usados,"Usuario No Deslogeado",tostring(floor((segundos_usados)/(3600))) + "hs" +  tostring(floor((segundos_usados)/(60))%60) + "min" )
|stats values(PrimerLogin) as Login,values(UltimoLogoff) as Logoff,values(diff) as Duration by User,host,Day

[martin_mueller]

Don't use join like that, use stats and OR instead:

 earliest=-15d@d latest=now index=wineventlog (EventCode="4624" OR EventCode="4634") 
| regex Account_Name!=".*\$"                     
| eval Day=strftime(_time,"%d/%m/%Y")   
| eval User=if(mvcount(Account_Name) > 1, mvindex(Account_Name,1), Account_Name)
| eval login_time = case(EventCode="4624", _time),  logoff_time = case(EventCode="4634", _time)
| stats earliest(login_time) as desde, latest(logoff_time) as hasta by User, host, Day
| eval segundos_usados=hasta-desde 
...

That will go through both event codes together, and stats-by the two together for each user-host-day triple.

[mstjohn_splunk]

Hi @yassy,

Did the answer below solve your problem? If so, please resolve this post by approving it! If your problem is still not solved, keep us updated so that someone else can help ya.

Thanks for posting!