Join with earlier=true yields newer results

[blablabla]

Hello, 

I have the following issue. I have a Search A, that yields me the state of a device. I would like to supplement the state by the information of the command, that leaded to the State A. Therefore I am looking to get the last command of Search B with the same device ID, that is before my Event in Search A. 

In order to do this, I have used a left join.

index="IndexA" sourcetype="SourceA" .....|eval time=_time| table time ID state
|join type=left left=A right=B usetime=true earlier=true where A.ID=B.ID 
[search index="IndexA" sourcetype="SourceB" ...|eval time=_time| table time ID command| sort _time-]
|timediff='A.time'-'B.time'

 Now I have the following issues:

• Is there a direct way to access the internal field _time? (Using 'A._time' doesn't work, which is why I am saving it in an own field named time)
• Somehow, if I don't use table at the end of the search command, i cannot access the value of time in the subsearch B using 'B.time' . What is the reason for this?
• And most important: I am getting results of the subsearch B, which are newer than my Event in the Search A. Is this because I used sort inside the subsearch?

Thanks and best Regards

[PickleRick]

Don't use join if you can avoid it.

If you want last command before given state, you might try streamstats. Something like

index=whatever sourcetype=whatever
| streamstats current=f latest(command) as lastcommand by ID

You might need to resort the data before streamstats because as far as I remember, streamstats works in order of results so if you select latest row from data sorted from newest to oldest, you'll always get the latest results, not the previous ones as you'd want.

If you want the timestamp of the command, you'll have to combine the command with the timestamp first because calculating the last(command) last(_time) separately won't do.

[blablabla]

Thank you for your reply. 
the command is stored in a different index. Is it still possible to use streamstats?

what could be the reason, that using join with usetime=true and earlier=true yielded later results from the subsearch? Could it be because of sorting within the subsearch?