Splunk Search

Token Date in a search with join


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
0 Karma


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.

0 Karma

Splunk Employee
Splunk Employee

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!

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!