Splunk Search
Highlighted

Search Query For Time Difference

Path Finder

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 ADTime showing correct, but CATime 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 ADTime, 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 CATime, duser AS Account, act AS Action] | eval Timediff=ADTime-CATime | eval Timediff=strftime(time,"%H:%M:%S") | table ADTime Account CA_Time Action Timediff

Thanks in advance!

0 Karma
Highlighted

Re: Search Query For Time Difference

SplunkTrust
SplunkTrust

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

Re: Search Query For Time Difference

Path Finder

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.

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

Thanks in advance!

0 Karma
Highlighted

Re: Search Query For Time Difference

SplunkTrust
SplunkTrust

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

Re: Search Query For Time Difference

Path Finder

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 ADTime Account CATime Action Timediff

0 Karma
Highlighted

Re: Search Query For Time Difference

SplunkTrust
SplunkTrust

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

Re: Search Query For Time Difference

Path Finder

This is the output,
ADTime CATime 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

0 Karma
Highlighted

Re: Search Query For Time Difference

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

Re: Search Query For Time Difference

Path Finder

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 ADTime=strftime(time, "%Y/%m/%d %H:%M:%S")|eval Date1=time|search NOT SourceNetworkAddress="-" |rename user AS Account, SourceNetworkAddress AS SourceIP| join Account outer [search index=xyz sourcetype="xyz" act="Action" (duser=userID)| eval CATime=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 ADTime | where Timediff > 1 |table ADTime Account SourceIP Timediff

0 Karma
Highlighted

Re: Search Query For Time Difference

Motivator

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