Optimizing subquery with inputlookup

[krbalaji77]

I have this query to find hosts from a lookup that have zero events. There are about a 100 hosts and I can see that the query performance is slow with the use of subquery this way. 

Any ideas to improve this?

| inputlookup lookup.csv
| join type=outer [search index=os sourcetype=ps "abc.pid" OR "abc.bin"
| stats count as heartbeat by host ]
| fillnull heartbeat value=0
| where heartbeat=0
| stats values(host) as failed_hosts

[gcusello · Accepted solution]

Hi @krbalaji77,

the error is to use the join command that must be used only when there isn't any other solution.

Splunk isn't a database!

In addition you used the search as subquery and you have the limit of 50,000 results.

You should use a different approach using your lookup:

index=os sourcetype=ps "abc.pid" OR "abc.bin"
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup lookup.csv | eval host=lower(host), count=0 | fields host count ]
| join type=outer [search  ]
| stats sum(count) AS total BY host
| where total=0

This is a more performant search.

Ciao.

Giuseppe

[krbalaji77]

Thank you for the quick response @gcusello 

so, we do still need to do a join in this case right meaning the append itself wouldnt do it?

also - what does this do ?

| join type=outer [search  ]

[gcusello]

Hi @krbalaji77,

sorry! it was a typo:

index=os sourcetype=ps "abc.pid" OR "abc.bin"
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup lookup.csv | eval host=lower(host), count=0 | fields host count ]
| stats sum(count) AS total BY host
| where total=0

Ciao.

Giuseppe