topic Re: Token Date in a search with join in Splunk Search

Token Date in a search with join

yassy — Wed, 26 Dec 2018 18:34:39 GMT

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

Re: Token Date in a search with join

martin_mueller — Wed, 26 Dec 2018 21:32:51 GMT

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.

Re: Token Date in a search with join

mstjohn_splunk — Mon, 07 Jan 2019 21:24:45 GMT

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!