topic join two result with different times in Splunk Search

join two result with different times

splunkdivya — Tue, 29 Sep 2020 18:32:40 GMT

I have a use case, where in I need data from different dates compared to previous days. so, I have a time picker and a previous days dropdown with 1,2,3,4,5,6,7,8,9,10 days option.

e.g. there may be a case where in I need to compare today with last 5 days. So, I will select today from time range and previous 5 days from dropdown.

case 2. : I can select date range from time picker and then compare with any number of days.

I used join: index=abc earliest=$time_tok.earliest$- $days_selected$ latest=$time_tok.earliest$|stats count(host) by source|join type=left source[index=abc earliest=$time_tok.earliest$ latest=$time_tok.latest$|stats count(host) by source].

tried using daysago as well, but it is very very slow.

Please provide some pointers.
Best,

Re: join two result with different times

strive — Tue, 20 Mar 2018 06:45:06 GMT

How much data you have?
What is your HW configuration?
Dont you have other filter conditions available?

Re: join two result with different times

tiagofbmm — Tue, 20 Mar 2018 07:59:17 GMT

If your query is accurate, then you don't need to use stats, you can just use tstats, which is 1000x faster:

| tstats count(host) where index=abc AND earliest=$time_tok.earliest$- $days_selected$ AND latest=$time_tok.earliest$ by source
| join type=left source 
    [| tstats count(host) as C2 where index=_internal AND earliest=$time_tok.earliest$ AND latest=$time_tok.latest$ by source ]

Re: join two result with different times

splunkdivya — Tue, 20 Mar 2018 08:28:16 GMT

Is there a way out to avoid join here?

Re: join two result with different times

tiagofbmm — Tue, 20 Mar 2018 08:31:57 GMT

Have you tried how faster the query above runs?

Re: join two result with different times

tiagofbmm — Wed, 21 Mar 2018 17:28:12 GMT

Please let me know if the answer was useful for you. If it was, accept it and upvote. If not, give us more input so we can help you with that

Re: join two result with different times

mydog8it — Tue, 29 Sep 2020 20:07:44 GMT

I do something similar in the following search. It grabs the past 30 minutes and the prior three weeks same 30 minute window and averages the historical data to establish "normal" and then displays "now" in the same timechart. I thought this would give you something to work from. Not the prettiest search, but it works.

index=main earliest=-30m@m latest=-0m@m host="xxxxxxx"
| timechart span=30s count as TOTAL
| eval ReportKey="Last30"
| append [search index=main host="xxxxxxx" earliest=-10110m@m latest=-10080m@m
| timechart span=30s count as TOT
| eval ReportKey="1WkAgo"
| eval _time=_time+604800]
| append [search index=main host="xxxxxxx" earliest=-20190m@m latest=-20160m@m
| timechart span=30s count as TOT
| eval ReportKey="2WksAgo"
| eval _time=_time+1209600]
| append [search index=main host="xxxxxxx" earliest=-30270m@m latest=-30240m@m
| timechart span=30s count as TOT
| eval ReportKey="3WksAgo"
| eval _time=_time+1814400 ]
| timechart avg(TOT) as Three_week_average values(TOTAL) as The_previous_30_minutes