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.
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
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
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.
@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)