Splunk Search

result of two searches in one variable

Bettynet
Engager

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! 

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

Bettynet
Engager

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!

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Bettynet 

Try again by adding search in subserch.

| appendcols
[ search index="opex_mobile_index"

 

KV

Bettynet
Engager

Yes! that did work!!!

 

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

 

Really grateful ^^

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Glad to help you.

Happy Splunking

 

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...