Append 2 tables. Gets "Missing or malformed messages.conf stanza for SEARCHFACTORY:UNKNOWN_OP__index" error

[niyaz006]

I am trying to append to search results which displays the same column headers. However I am getting the error: Missing or malformed messages.conf stanza for SEARCHFACTORY:UNKNOWN_OP__index

index="main" 
| stats sum(effort) as value by category
| append 
[ index="main" |  stats count as value by category ]

[niyaz006]

index="main"
| eval metric_name = "count:effort"
| eval metric_name = split(metric_name,":")
| mvexpand metric_name
| eval _value = case(metric_name="count", 1, metric_name="effort", effort)
| table metric_name, _value, category

[woodcock · Accepted solution]

You are missing the keyword search inside of your subsearch; try this:

index="main" | stats sum(effort) as value by category 
| append [ search index="main" | stats count as value by category ]

But there is a better way; try this:

index="main" 
| multireport
[ stats sum(effort) AS value BY category ]
[ stats count AS value BY category ]

Or best of all, this:

index="main" 
| stats sum(effort) AS effort count AS count BY category