Splunk Search

Token Date in a search with join

yassy
Explorer

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

martin_mueller
SplunkTrust
SplunkTrust

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

mstjohn_splunk
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
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...