Splunk Search

Left join not working properly in Splunk Enterprise 6.6.2

kamal_jagga
Contributor

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.

0 Karma
1 Solution

cmerriman
Super Champion

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

View solution in original post

0 Karma

cmerriman
Super Champion

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
0 Karma

kamal_jagga
Contributor

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
Splunk Employee
Splunk Employee

@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)
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...