Get last 3 events based on time in 1 index when an error happens in a different index

[Sfry1981]

I have the below search where i get an errot and then i want to pull through the last 3 events prior to that error but they sit in another index. the only matching information that joins them is the user id. I want the second search to look at the errors time and then pull the last 3 results prior to that time. Currently it is pulling through the latest results as I am not sure how to specify this. Any help would be appreciated as I cant seem to find an answer through the splunk answers for this.

index=index1 errorid =99999999 usersid=111 | head 1 | append [search index=index2 sid=* clientid=* usersid=111| head 3 ] | table usersid errorid message nav1 _time

My results show like this
usersid a_errid message nav1 _time
96494 454545 error 2017-10-25T09:35:35.000+0100
96494 nav1area 2017-11-09T11:49:51.000+0000
96494 nav1area 2017-11-09T11:49:50.000+0000
96494 nav1area 2017-11-09T11:48:50.000+0000

As you can see the times are for now but the error was back on the 25th oct so i need the events to show the last 3 events before that time?

Thanks

[somesoni2 · Accepted solution]

Try like this

index=index1 errorid =99999999 usersid=111 | head 1 | table _time userid errorid 
| eval earliest=_time-86400  | eval latest=_time
| map search="search index=index2 sid=* clientid=* usersid=$userid$ latest=$latest$ earliest=$earliest$ | head 3 | table _time userid message nav1 | eval errorid=\"$errorid$\""

[Sfry1981]

Thanks somesoni2 and its nearly perfect, Its pulling through the 3 events for that date into the statistics but then it shows the error log in the events tab but i need it to show in the statistics tab with the other events. Any ideas?

[Sfry1981]

Sorry someoni2 please ignore me and it is there as I was not looking at it properly. You are a star 🙂