Subsearch to store value

[jam90]

Hello, 

I am running two separate queries to extract values:

First query

index=abc status=error | stats count AS FailCount

Second query

index=abc status=planning | stats count AS TotalPlanned

Both queries are working well and giving expected results. 

When I combine them using sub search, I am getting error:

index=abc status=error
| stats count AS FailCount
[ search index=abc status=planning
| stats count AS TotalPlanned
| table TotalPlanned ]
| eval percentageFailed=(FailCount/TotalPlanned)*100 

Error message:

Error in 'stats' command: The argument '(( TotalPlanned=761 )) is invalid'

Note: The count 761 is a valid count for TotalPlanned, so it did perform that calculation. 

[ITWhisperer]

| stats count(eval(status="error")) AS FailCount count(eval(status="planning")) AS TotalPlanned
| eval percentageFailed=(FailCount/TotalPlanned)*10

[richgalloway · Accepted solution]

It may help to think of a subsearch like a macro.  Just as the contents of a macro replace the macro name in a query, so, too, do the results of a subsearch replace the subsearch text in the query.  Therefore, it's important that the results of the subsearch make sense, semantically.

In the example query, once the subsearch completes, Splunk tries to execute this

index=abc status=error
| stats count AS FailCount
(( TotalPlanned=761 ))
| eval percentageFailed=(FailCount/TotalPlanned)*100 

which is not a valid query.

One fix is to use the appendcols command with the subsearch

index=abc status=error
| stats count AS FailCount
| appendcols [ search index=abc status=planning
  | stats count AS TotalPlanned
  | table TotalPlanned ]
| eval percentageFailed=(FailCount/TotalPlanned)*100