How to search last event before specific time

[l0gik]

I have a search that has a join in it. I want to use the first search event timestamp to dynamically find the "last event from the join search before that timestamp"

Something like the following, which isn't accepted formatting apparently!

index=a sourcetype=messages Message{}.messageNumber=2083 "Message{}.target.description"="NON SYNCHRONIZED" ("REACTIVE_EVENT" OR "REACTIVE_WARNING")
| rename Message{}.target.div as Div, 
     Message{}.Address as Address, 
     Message{}.Type as Type, Message{}.Id as ID
**| eval eventTime=_time**
| table **eventTime** Div Address Type ID

| join outer [search index=a sourcetype=Office earliest=-30d **latest=$eventTime$** MODE_CHANGE
| rename m:CONTENT.m:DISTRICT_NAME as xDist, 
m:CONTENT.m:BOS_INSTANCE_MODE as xmode
| lookup subDivLookup.csv SubDiv_Name as xDist OUTPUT Div_ID as Div
| stats latest(xmode) as latestMode by Div
| table latestMode Div]

| table Address Div Type ID latestMode
| sort +Div

[jkat54]

You're using join incorrectly. Kindly refer to the documentation for the command.

You're not specifying any fields for the command to join on. Actually in this case you're telling join to join the events if their "outer" field matches.

 ... search with fieldToJoinX ...
 | join type=outer fieldToJoin1 fieldToJoin2 ... [ subsearch with fieldToJoinx]

[l0gik]

I must have missed that part in my original post, sorry I had to edit my real search a lot to disguise the nature of the data it is used against. I do indeed have a join element, that line should read:

| join outer Div [search.... 

The search that I have works correctly now with the exception that it runs based on the current "MODE" of the server and not the "MODE" of the server at the time of the incident.

To better clarify the issue, the outer join search needs to run with a dynamic latest event time for each event in the original search. The first search returns a lot of events that occurred, the second search shows mode changes of the server and I need to confirm the mode that the server was in at the time of each event, not just its current mode. Therefore, I need to dynamically run the second search based on the time of the event that each first search item occurred.

I'm not certain at this point but I think that it can't be done with a join and will require a map search instead.