Splunk Search

Add count of subsearch results

LittleColin
Engager

I need to return all rows from my top search but add a count of rows from a map or subquery/subsearch.

In my system I have a number of batches which may have a number of errors that exist in a different index and I want to display a count of those errors (even if zero) alongside the batch.

Something like this pseudo query:

search index=A | fields batch_id, batch_name | count = COUNT("search index=B batch_id=$batch_id$ level=error")

This is to display on a dashboard in a stats table:

 Id     Name     Errors
 1234 | BatchA | 0
 4567 | BatchB | 6
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi LittleColin,
if you have batch_name in both the indexes, you can try something like this:

index=B [ search index=A | dedup batch_id| fields batch_id ]
| stats values(batch_name) AS batch_name count AS Errors by batch_id

if instead you have batch_name only in index=A, you can try something like this:

index=B
| stats count AS Errors by batch_id
| join type=left batch_id [ search index=B | fields batch_id batch_name]
| table batch_id batch_name Errors

The second one is less performant than the first.

Bye.
Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi LittleColin,
if you have batch_name in both the indexes, you can try something like this:

index=B [ search index=A | dedup batch_id| fields batch_id ]
| stats values(batch_name) AS batch_name count AS Errors by batch_id

if instead you have batch_name only in index=A, you can try something like this:

index=B
| stats count AS Errors by batch_id
| join type=left batch_id [ search index=B | fields batch_id batch_name]
| table batch_id batch_name Errors

The second one is less performant than the first.

Bye.
Giuseppe

LittleColin
Engager

Many thanks for the swift reply - for me your second option is working well

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