Splunk Search

Join with earlier=true yields newer results

Path Finder


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-]



 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

Labels (3)
0 Karma

Ultra Champion

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.

Path Finder

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?

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...