Splunk Search

how to use format in a subsearch to make a tstats command

klim
Path Finder

I know you can use a search with format to return the results of the subsearch to the main query. Like for example I can do this:

index=unified_tlx [search index=i | top limit=1 acct_id | fields acct_id | format] | stats count by acct_id

Where it finds the top acct_id and formats it so that the main query is index=i ( ( acct_id="top_acct_id" ) ) | stats count by acct_id.

How can I use format to do that for a tstats command instead so that it could look something like | tstats count where index=i AND TERM(random_acct_id) ? I want to use the subsearch to find the top_acct_id and then format it so it will look like the above

Labels (2)
0 Karma

tscroggins
Influencer

@klim 

EDIT: Updated.

Using top returns both the field value and the count, so it's not necessary to run additional searches:

index=i
| top limit=1 acct_id

acct_idcountpercent
112398.6

 

If you're running Splunk 8+ and your events contain field names and values with only minor breakers, e.g. acct_id=xxx, you can produce similar results with tstats:

| tstats count where index=i TERM(acct_id=*) by PREFIX(acct_id=)
| eventstats sum(count) as total
| eval percent=100*count/total
| sort 1 - count
| fields - total
| rename acct_id= as acct_id

On my test system, using tstats with TERM and PREFIX followed by subsequent commands is about 390% faster.

You can nest subsearches in tstats just as you can with other searches. Instead of using format (either implicitly or explicitly), you can return a field value directly using $field:

| tstats count where index=unified_tlx 
[| tstats count where index=i TERM(acct_id=*) by PREFIX(acct_id=)
| sort 1 - count
| rename acct_id= as acct_id
| eval acct_id="TERM(acct_id=".acct_id.")"
| return $acct_id ] by PREFIX(acct_id=)

If you're running an earlier version of Splunk or if your raw data does not contain field names, you can fall back to nesting top, but your final output will not contain the acct_id value:

| tstats count where index=unified_tlx
[ index=i
| top limit=1 acct_id
| eval acct_id="TERM(".acct_id.")"
| return $acct_id ]

If acct_id is an indexed field, you can reference it directly in tstats:

| tstats count where index=unified_tlx
[ | tstats count where index=i by acct_id
| sort 1 - count
| table acct_id ] by acct_id

0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...