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:
Thanks and best Regards
Don't use join if you can avoid it.
If you want last command before given state, you might try streamstats. Something like
| 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.
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?