topic Re: Search Query For Time Difference in Splunk Search

Search Query For Time Difference

kpavan — Tue, 29 Sep 2020 08:45:02 GMT

Hi All,

My use case to find out 1st search user logon time in AD and same user logon time in 2nd search with his action, now need to find if user logon is complete but if he didn't do any action in 2nd search with duration not more than 30mins then I need to highlight that user with logon time,
In the below query AD_Time showing correct, but CA_Time showing same date&time for all rows, not sure where am I missing and not getting what expected.

index="wineventlog" source="wineventlog:security" EventCode="4624" (user=userID) |eval Date=strftime(_time, "%Y/%m/%d %H:%M:%S")|rename Date AS AD_Time, user AS Account| join outer [search index=xyz sourcetype="xyz" act="Action" (duser=userID) |eval Date=strftime(_time, "%Y/%m/%d %H:%M:%S")| rename Date AS CA_Time, duser AS Account, act AS Action] | eval Timediff=AD_Time-CA_Time | eval Timediff=strftime(_time,"%H:%M:%S") | table AD_Time Account CA_Time Action Timediff

Thanks in advance!

Re: Search Query For Time Difference

renjith_nair — Tue, 16 Feb 2016 03:36:14 GMT

Try this

index="wineventlog" source="wineventlog:security" EventCode="4624" (user=userID) 
| eval AD_Time=strftime(_time, "%Y/%m/%d %H:%M:%S")|eval Date1=_time|rename user AS Account
| join Account outer [search index=xyz sourcetype="xyz" act="Action" (duser=userID) 
| eval CA_Time=strftime(_time, "%Y/%m/%d %H:%M:%S")|eval Date2=_time| rename duser AS Account, act AS Action] 
| eval Timediff=Date1-Date2 | eval Timediff=strftime(Timediff,"%H:%M:%S") | table AD_Time Account CA_Time Action Timediff

Re: Search Query For Time Difference

kpavan — Tue, 29 Sep 2020 08:45:30 GMT

Hi Renjith,

Thanks for your response,

I tried the query, but Timediff showing wrong value, as showing below result the value of Timediff should be 04:14:22 but getting as 23:14:22 which is not correct. Could you please help on the same.

AD_Time Account CA_Time Action Timediff
2016/02/05 08:28:44 userID 2016/02/05 04:14:22 Retrieve 23:14:22

Thanks in advance!

Re: Search Query For Time Difference

renjith_nair — Tue, 16 Feb 2016 09:23:48 GMT

Sorry I didn't notice your conversion of difference field.

Try eval Timediff=tostring(Timediff, "duration") instead of eval Timediff=strftime(Timediff,"%H:%M:%S")

Final search

 index="wineventlog" source="wineventlog:security" EventCode="4624" (user=userID) 
 | eval AD_Time=strftime(_time, "%Y/%m/%d %H:%M:%S")|eval Date1=_time|rename user AS Account
 | join Account outer [search index=xyz sourcetype="xyz" act="Action" (duser=userID) 
 | eval CA_Time=strftime(_time, "%Y/%m/%d %H:%M:%S")|eval Date2=_time| rename duser AS Account, act AS Action] 
 | eval timediff=Date1-Date2 | eval Timediff=tostring(timediff, "duration") | table AD_Time Account CA_Time Action Timediff

Re: Search Query For Time Difference

fdi01 — Tue, 16 Feb 2016 10:49:39 GMT

try like this :

index="wineventlog" source="wineventlog:security" EventCode="4624" (user=userID) |eval AD_Time=strftime(_time, "%Y/%m/%d %H:%M:%S")|rename  user AS Account| appendcols [search index=xyz sourcetype="xyz" act="Action" (duser=userID) |eval CA_Time=strftime(_time, "%Y/%m/%d %H:%M:%S")| rename  duser AS Account, act AS Action] | eval Timediff=AD_Time-CA_Time | eval Timediff=strftime(_time,"%H:%M:%S") | table AD_Time Account CA_Time Action Timediff

Re: Search Query For Time Difference

kpavan — Tue, 29 Sep 2020 08:45:33 GMT

Hi Renjith,

sorry to bother you again, tried your final query but Streamed search execute failed because: Invalid number and just edited little bit, now Timediff field not giving any value. something is missing.

|eval timediff=strptime(Date1, "%Y/%m/%d %H:%M:%S") - strptime(Date2, "%Y/%m/%d %H:%M:%S") | eval Timediff=tostring(timediff, "duration")| table AD_Time Account CA_Time Action Timediff

Re: Search Query For Time Difference

renjith_nair — Tue, 16 Feb 2016 13:23:20 GMT

Try subtracting _time from bth search before formatting. Can you try this and share the output?

 index="wineventlog" source="wineventlog:security" EventCode="4624" (user=userID) 
  | eval AD_Time=strftime(_time, "%Y/%m/%d %H:%M:%S")|eval Date1=_time|rename user AS Account
  | join Account outer [search index=xyz sourcetype="xyz" act="Action" (duser=userID) 
  | eval CA_Time=strftime(_time, "%Y/%m/%d %H:%M:%S")|eval Date2=_time| rename duser AS Account, act AS Action] 
  | eval timediff=Date1-Date2|table AD_Time,CA_Time,Account,Action,Date1,Date2,timediff

Re: Search Query For Time Difference

kpavan — Tue, 29 Sep 2020 08:45:48 GMT

This is the output,
AD_Time CA_Time Account Action Date1 Date2 timediff
2016/02/16 02:01:07 2016/02/07 22:58:44 userID1 Retrieve 1455606067 1454903924 702143
2016/02/15 09:54:02 2016/02/10 01:16:18 userID2 Retrieve 1455548042 1455084978 463064

Re: Search Query For Time Difference

renjith_nair — Wed, 17 Feb 2016 03:10:29 GMT

This looks correct . Just add |eval Timediff=tostring(timediff, "duration") to see the difference in hours,minutes and seconds.

Result of my search gives difference as 8+03:02:23 which is 8 days 3 hours,2 minutes,23 secs which is correct for my timezone

Re: Search Query For Time Difference

kpavan — Tue, 29 Sep 2020 08:49:29 GMT

Hi Renjith,

Actually tostring is giving me error as i said earlier like Invalid number, not sure why. So i have changed bit, i think got what expected.

Thanks for your support!

index="wineventlog" sourcetype="wineventlog:security" EventCode="4624" OR EventCode="4625"(user=userID)| eval AD_Time=strftime(_time, "%Y/%m/%d %H:%M:%S")|eval Date1=_time|search NOT Source_Network_Address="-" |rename user AS Account, Source_Network_Address AS SourceIP| join Account outer [search index=xyz sourcetype="xyz" act="Action" (duser=userID)| eval CA_Time=strftime(_time, "%Y/%m/%d %H:%M:%S")|eval Date2=_time| rename duser AS Account, act AS Action]| eval Timediff=round(('Date1'-'Date2')/3600) | dedup AD_Time | where Timediff > 1 |table AD_Time Account SourceIP Timediff