topic Re: Join consecutive events in same index in Splunk Search

Join consecutive events in same index

intelli2019 — Sat, 27 Jul 2019 19:03:55 GMT

Hi,
I thought this would be easy but no!
I'm doing the query below on the Sample data below but the FileTime_END value I'm getting is 15:29:00 for every line (don't have enough points to attach images or post links sorry).
What am I doing wrong? Also is there a better way to do this without a JOIN?

Cheers guys.

index="file-times" State=Start | table Service FileTime State | rename Service as Service_Start FileTime as Filetime_Start, State as State_Start
| join Service type=outer [search index="file-times" State=END | table FileTime State | rename FileTime as Filetime_End, State as State_End]

Sample Log/Index Data:
23/07/2019 09:34:00, Service=RR, FileTime=9:33:45, State=START
23/07/2019 10:31:00, Service=RR, FileTime=10:30:45, State=END
23/07/2019 11:01:00, Service=HHR, FileTime=11:00:32, State=START
23/07/2019 11:31:00, Service=HHR, FileTime=11:30:32, State=END
23/07/2019 12:01:00, Service=LPE, FileTime=12:00:32, State=START
23/07/2019 12:55:00, Service=LPE, FileTime=12:54:32, State=END
23/07/2019 12:01:00, Service=SMURF, FileTime=12:00:32, State=START
23/07/2019 13:01:00, Service=SMURF, FileTime=13:00:00, State=END
23/07/2019 14:00:00, Service=TEST, FileTime=14:05:00, State=START
23/07/2019 14:30:00, Service=TEST, FileTime=14:29:00, State=END
23/07/2019 15:00:00, Service=TEST1, FileTime=15:05:00, State=START
23/07/2019 15:30:00, Service=TEST1, FileTime=15:29:00, State=END

Re: Join consecutive events in same index

HiroshiSatoh — Sun, 28 Jul 2019 03:16:44 GMT

Try this!

index="file-times"
|stats max(FILETIME) as Filetime_End,min(FILETIME) as Filetime_Start by Service

You can also use the transaction command.

Re: Join consecutive events in same index

intelli2019 — Sun, 28 Jul 2019 05:24:23 GMT

Thanks! I will try this. The production index will have multiple days data in it. To get min and max filetime by service for each day I just add a date field to the end of the query? I.e “by Service, Date” instead of just “by Service” ?

Re: Join consecutive events in same index

intelli2019 — Sun, 28 Jul 2019 05:25:26 GMT

Re: Join consecutive events in same index

HiroshiSatoh — Wed, 30 Sep 2020 01:28:06 GMT

index="file-times"
|eval date=substr(FILETIME,1,10)
|stats max(FILETIME) as Filetime_End,min(FILETIME) as Filetime_Start by Service,date

Re: Join consecutive events in same index

woodcock — Tue, 30 Jul 2019 05:08:41 GMT

Like this:

| makeresults 
| eval raw="time=23/07/2019T09:34:00,Service=RR,FileTime=9:33:45,State=START time=23/07/2019T10:31:00,Service=RR,FileTime=10:30:45,State=END time=23/07/2019T11:01:00,Service=HHR,FileTime=11:00:32,State=START time=23/07/2019T11:31:00,Service=HHR,FileTime=11:30:32,State=END time=23/07/2019T12:01:00,Service=LPE,FileTime=12:00:32,State=START time=23/07/2019T12:55:00,Service=LPE,FileTime=12:54:32,State=END time=23/07/2019T12:01:00,Service=SMURF,FileTime=12:00:32,State=START time=23/07/2019T13:01:00,Service=SMURF,FileTime=13:00:00,State=END time=23/07/2019T14:00:00,Service=TEST,FileTime=14:05:00,State=START time=23/07/2019T14:30:00,Service=TEST,FileTime=14:29:00,State=END time=23/07/2019T15:00:00,Service=TEST1,FileTime=15:05:00,State=START time=23/07/2019T15:30:00,Service=TEST1,FileTime=15:29:00,State=END"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| kv
| eval _time = strptime(time, "%d/%m/%YT%H:%M:%S")
| sort 0 - _time

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| reverse
| stats list(*) AS * BY Service

Re: Join consecutive events in same index

nathc100 — Mon, 05 Aug 2019 23:57:18 GMT

Hi woodcock,
Sorry mate I'm pretty new at this. Could you explain what your query does a little please?
It looks like you're creating some raw data to query but this would be coming from a daily log file in my case.
Cheers
Nathan

Re: Join consecutive events in same index

woodcock — Tue, 06 Aug 2019 00:00:39 GMT

See line 10? It explains it. You replace everything through line 10 with your main search and then use lines 12-13 to do what you were asking.