Splunk Search

Optimizing subquery with inputlookup

krbalaji77
Explorer

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

Labels (3)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

gcusello
SplunkTrust
SplunkTrust

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
Explorer

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  ]

 

Tags (2)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...