Splunk Search

how to add field values of a table to another field values in a different table in a dashboard belonging to same Index

preacher_15
Explorer

hello all,
I want to add field values of a table with field values of another table in a dashboard both belonging to same index.
example:
i have indexed 2 csv files to have 2 different table.
table 1 data is as follows:
Lead Actuals Forecast Budget
A. 10 20 200
B. 15 15 300
25 35 500 Total
table2 data is as follows:
hghfa NotActuals NotForecast NotBudget
other costs: 30 30 400
remaining: 25 30 500

what i want is now to add the fields in table1 and get the total(columnwise) and from the total subtract other costs from table2.
and display the TOTAL at the bottom of either table1 or table2.
I just want to subtract the Other costs field values from the Total from table 1.
how can i do this?

Thanks in advance.

Tags (1)
0 Karma

renjith_nair
Legend

@preacher_15 ,

Try

index="your index" source="first" OR source="second"
|fields Lead,Actuals,Budget,Forecast,NotActuals, NotForecast, NotBudget
|addcoltotals labelfield="Lead" label="Total"
|eventstats first(NotActuals) as NotActuals,first(NotForecast) as NotForecast,first(NotBudget) as NotBudget
|eval Actuals  = if(Lead=="Total", Actuals-NotActuals,Actuals) ,
      Forecast = if(Lead=="Total", Forecast-NotForecast,Forecast) ,
      Budget   = if(Lead=="Total", Budget-NotBudget,Budget)
|fields Lead,Actuals,Budget,Forecast|where isnotnull(Lead)

With dummy data ,

|makeresults |eval Lead="A B"|makemv Lead|mvexpand Lead|appendcols [|makeresults|eval Actuals="10 15"|makemv Actuals|mvexpand Actuals]
             |appendcols [|makeresults |eval Forecast="20 15"|makemv Forecast|mvexpand Forecast]
             |appendcols [|makeresults |eval Budget="200 300"|makemv Budget|mvexpand Budget]
|append [|makeresults |eval Name="OtherCosts", NotActuals="30", NotForecast="30", NotBudget="400" ]

|rename COMMENT as "<--Until here is dummy data generation and should not used in the -->"
|fields Lead,Actuals,Budget,Forecast,NotActuals, NotForecast, NotBudget
|addcoltotals labelfield="Lead" label="Total"
|eventstats first(NotActuals) as NotActuals,first(NotForecast) as NotForecast,first(NotBudget) as NotBudget
|eval Actuals  = if(Lead=="Total", Actuals-NotActuals,Actuals) ,
      Forecast = if(Lead=="Total", Forecast-NotForecast,Forecast) ,
      Budget   = if(Lead=="Total", Budget-NotBudget,Budget)
|fields Lead,Actuals,Budget,Forecast|where isnotnull(Lead)
---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma
Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...