Left join not working properly in Splunk Enterprise 6.6.2

[kamal_jagga]

Hi,

I have written a simple left join query which doesn't seem to work properly.

Objective: To find out host which are present in csv but are not coming in search results.

| inputlookup hosts.csv
| join type left host
[ | search index=abc sourectype=xyz
| stats latest(_time) as  _time by host ]

Either I am doing some silly mistake or Splunk is acting weird.
Kindly advise.

[cmerriman · Accepted solution]

you're missing the equals sign. try this:

 | inputlookup hosts.csv
    | join type=left host
    [  search index=abc sourectype=xyz
    | stats latest(_time) as _time by host ]

remember that join has limitations and should be avoided if possible.
you might be able to do something like this, if there is a subsearch error:

 index=abc sourectype=xyz
        | stats latest(_time) as _time by host 
        |append [| inputlookup hosts.csv]
        |stats values(_time) as _time count by host

[kamal_jagga]

Guess what, type=left was giving me syntax error in the morning and now it started working.

I feel sometimes the Splunk doesn't always behave consistently.

I am aware of the limitations of join but wanted to see, why isn't it working.

Thanks for your reply.

[lfedak_splunk]

@somesoni2, can you please repost your answer here? This was a duplicate post. Thank you!

Give this a try (since your lookup file doesn't contain sourcetype, dropping that column from base search).

index = X sourcetype="ABC" earliest=-5m@m
| stats latest(_time) as _time latest(dvc) as dvc by host
| append [| inputlookup host.csv ]
| stats values(_time) as _time values(dvc) as dvc by host sourcetype
| where isnull(dvc)