result of two searches in one variable

[Bettynet]

Hi,

I would like to have a dashboard panel with just a number, which should be the substraction of two values obtained from two searches. Both searches work individually, but I can't find the way to put them together in one search. 

Search 1:

index="opex_mobile_index" | eventstats max(source) as last_source   | where match(source,last_source)

| table "CECO", "Descripcion_Partida_Total", "Partida", "ImporteSolicitado_EUR_Partida_Total"

| dedup "CECO", "Descripcion_Partida_Total", "Partida", "ImporteSolicitado_EUR_Partida_Total"

| stats sum("ImporteSolicitado_EUR_Partida_Total") as total_budget 

Search 2:

index="opex_mobile_index" | eventstats max(source) as last_source   | where match(source,last_source)

search "Descripcion_Partida_Detalle"="Entregado QFC2"

| table "CECO", "Descripcion_Partida_Total", "Partida", "ImporteSolicitado_EUR_Partida_Detalle"

| dedup "CECO", "Descripcion_Partida_Total", "Partida", "ImporteSolicitado_EUR_Partida_Detalle"

| stats sum("ImporteSolicitado_EUR_Partida_Detalle") as QFC2_budget

Combination:

index="opex_mobile_index" | eventstats max(source) as last_source   | where match(source,last_source)

| table "CECO", "Descripcion_Partida_Total", "Partida", "ImporteSolicitado_EUR_Partida_Total"

| dedup "CECO", "Descripcion_Partida_Total", "Partida", "ImporteSolicitado_EUR_Partida_Total"

| stats sum("ImporteSolicitado_EUR_Partida_Total") as total_budget 

| append [search "Descripcion_Partida_Detalle"="Entregado QFC2"

| table "CECO", "Descripcion_Partida_Total", "Partida", "ImporteSolicitado_EUR_Partida_Detalle"

| dedup "CECO", "Descripcion_Partida_Total", "Partida", "ImporteSolicitado_EUR_Partida_Detalle"

| stats sum("ImporteSolicitado_EUR_Partida_Detalle") as QFC2_budget]

| eval diferencia = total_budget - QFC2_budget

This does not work. Nothing is output from the append [ ]. I used append, appendpipe, appendcols, multisearch... I don't know what I am doing wrong. 

Any help would be highly appreciated. 

Thanks! 

[richgalloway · Accepted solution]

The append command creates two events, but to perform a subtraction both fields must be in the same event.  Either combine the two events on a common field (which you don't have) or use appendcols.

You say you tried that, but I suspect no result was returned because the second search is invalid (at least in the question).  The second search is missing the index name, etc.

[Bettynet]

Dear @richgalloway, 

Thanks for your quick response. As you said, yes I tried with appendcols with no results. I also tried including the index name, but got the error: "Unknown search command 'index'". 

Could you please explain how should it be??

index="opex_mobile_index" | eventstats max(source) as last_source | where match(source,last_source)
| table "CECO", "Descripcion_Partida_Total", "Partida", "ImporteSolicitado_EUR_Partida_Total"
| dedup "CECO", "Descripcion_Partida_Total", "Partida", "ImporteSolicitado_EUR_Partida_Total"
| stats sum("ImporteSolicitado_EUR_Partida_Total") as total_budget
| appendcols
[index="opex_mobile_index" | eventstats max(source) as last_source | where match(source,last_source)
| search "Descripcion_Partida_Detalle"="Entregado QFC2"
| table "CECO", "Descripcion_Partida_Total", "Partida", "ImporteSolicitado_EUR_Partida_Detalle"
| dedup "CECO", "Descripcion_Partida_Total", "Partida", "ImporteSolicitado_EUR_Partida_Detalle"
| stats sum("ImporteSolicitado_EUR_Partida_Detalle") as QFC2_budget]
| eval diferencia = total_budget - QFC2_budget

Thanks again!

[kamlesh_vaghela]

@Bettynet 

Try again by adding search in subserch.

| appendcols
[ search index="opex_mobile_index"

KV

[Bettynet]

Yes! that did work!!!

Thank you both very much!!! @kamlesh_vaghela @richgalloway 

Really grateful ^^

[kamlesh_vaghela]

Glad to help you.

Happy Splunking