join two result with different times

[splunkdivya]

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,

[mydog8it]

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

[tiagofbmm]

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 ]

[splunkdivya]

Is there a way out to avoid join here?

[tiagofbmm]

Have you tried how faster the query above runs?

[tiagofbmm]

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

[strive]

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