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

[klim]

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

[tscroggins]

@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_id	count	percent
1	123	98.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