topic How to sum up multiple fields without using foreach? in Splunk Search

How to sum up multiple fields without using foreach?

akarivaratharaj — Fri, 12 Aug 2022 14:57:42 GMT

I am using the below search query which contains multiple fields. All the fields (DATA_MB, INDEX_MB, DB2_INDEX_MB, etc.,) contains size values of a particular DB.

index=main|timechart span=1w sum(DATA_MB) as datamb, sum(INDEX_MB) as indexmb, sum(DB2_DATA_MB) as db2datamb, sum(DB2_INDEX_MB) as db2indexmb, sum(DB2_LOB_MB) as db2lobmb, sum(DB2_LONG_MB) as db2longmb, sum(DB2_XML_MB) as db2xmlmb by DOMAIN limit=25

I want all these 7 fields such as datamb, indexmb, db2datamb, etc., to be summed up together and display it in a single field name without using "foreach" clause. Is it possible? (Because I need that final field to be used in another query as a main source value)

Could anyone please help me on this.

Re: How to sum up multiple fields without using foreach

Sukisen1981 — Tue, 29 Sep 2020 16:02:09 GMT

have you tried -

Ref - https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Addtotals

Re: How to sum up multiple fields without using foreach

inventsekar — Wed, 27 Sep 2017 07:55:52 GMT

Please try addtotals -

index=main|timechart span=1w sum(DATA_MB) as datamb, sum(INDEX_MB) as indexmb, sum(DB2_DATA_MB) as db2datamb, sum(DB2_INDEX_MB) as db2indexmb, sum(DB2_LOB_MB) as db2lobmb, sum(DB2_LONG_MB) as db2longmb, sum(DB2_XML_MB) as db2xmlmb by DOMAIN limit=25| addtotals

please check addtotals
http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Addtotals

Re: How to sum up multiple fields without using foreach

akarivaratharaj — Wed, 27 Sep 2017 09:10:43 GMT

The above query is giving me addition of all "datamb" field values, all "indexmb" field values (& other fields too) for a particular domain. It is showing the results as below:

_time datamb: Domain1 datamb: Domain2 indexmb: Domain1 indexmb: Domain2 etc.....
2017-06 24 45 20.8 40
2017-07 30.05 56.4 25 60
Total 54.05 101.4 45.8 100

But how I need is a variable should hold the sum of all the field values
var = datamb+indexmb+db2datamb+db2indexmb+db2lobmb+db2longmb+db2xmlmb

then at last the sum of that variable should be displayed according to the Domain as below
|timechart span=1mon sum(var) as Size by DOMAIN

_time Size Domain
2017-06 40 Domain1
2017-07 100 Domain2
etc.,

Re: How to sum up multiple fields without using foreach

Sukisen1981 — Wed, 27 Sep 2017 16:22:43 GMT

I can understand that you want to mask your data, but you need to provide more clarity, this explanation is not clear at all....what is your result looking like and what do you want? Have you tried just using | addtotals?

this will add a new column to the extreme right with the summation of all the column field values, is that what you want?
Can you just add a screen shot of your output WITHOUT using any addtotals ?

Re: How to sum up multiple fields without using foreach

akarivaratharaj — Tue, 29 Sep 2020 16:02:30 GMT

Hi Sukisen,

Basically, I am trying to add all the above mentioned fields' values into one field and that I call as "Size". Then I want to find size difference i.e., delta between two time intervals. For example, Delta = July month's size value - June month's size value.

As per below query I am getting the attached screenshot 1:
index=main|timechart span=1w sum(DATA_MB) as datamb, sum(INDEX_MB) as indexmb, sum(DB2_DATA_MB) as db2datamb, sum(DB2_INDEX_MB) as db2indexmb, sum(DB2_LOB_MB) as db2lobmb, sum(DB2_LONG_MB) as db2longmb, sum(DB2_XML_MB) as db2xmlmb by DOMAIN limit=25
Analytic Platform and Financial are the Domains which I used. my query is giving me the size value of each DB for each Domain.

For calculating the delta value of consolidated size details of all db, I have to add the below query into my current query:
| untable _time DOMAIN sum | streamstats current=false window=1 global=false sum(sum) as p_sum by DOMAIN | eval delta=sum-p_sum |stats sum(delta) as Delta by DOMAIN

Before adding this query, I should have a field which holds the summed up value of all the 7 fields which I mentioned earlier.
i.e., var = datamb+indexmb+db2datamb+db2indexmb+db2lobmb+db2longmb+db2xmlmb

So that I could change my delta query accordingly and add into my old query. My final answer I would get is as per attached example screenshot 2:

Re: How to sum up multiple fields without using foreach

akarivaratharaj — Wed, 27 Sep 2017 18:01:09 GMT

I guess I am unable to upload the screenshot.

Re: How to sum up multiple fields without using foreach

DalJeanis — Wed, 27 Sep 2017 18:59:12 GMT

I'm not understanding why you can't use eval?

index=main
| timechart span=1w
    sum(DATA_MB) as datamb, sum(INDEX_MB) as indexmb, sum(DB2_DATA_MB) as db2datamb, 
    sum(DB2_INDEX_MB) as db2indexmb, sum(DB2_LOB_MB) as db2lobmb, 
    sum(DB2_LONG_MB) as db2longmb, sum(DB2_XML_MB) as db2xmlmb 
    by DOMAIN limit=25
| eval totalmb = datamb + indexmb + db2datamb + db2indexmb + db2lobmb + db2longmb + db2xmlmb

Re: How to sum up multiple fields without using foreach

akarivaratharaj — Thu, 28 Sep 2017 02:43:00 GMT

Hi DalJeanis,

If I use eval for adding all the fields and continue with my delta operation, it is showing no results found. I tried this already.

Re: How to sum up multiple fields without using foreach

akarivaratharaj — Thu, 28 Sep 2017 05:50:32 GMT

Sorry I was trying the eval clause for addition along with the function "round". so it didnot returned me results. Now I got the answer with eval only.

Thankyou all for the help.

Re: How to sum up multiple fields without using foreach

akarivaratharaj — Thu, 28 Sep 2017 05:51:50 GMT

Thankyou inventsekar for your response.

Re: How to sum up multiple fields without using foreach

DalJeanis — Thu, 28 Sep 2017 05:53:04 GMT

@akarivaratharaj - Great! Have a great evening/day/whatever time it is there...

Re: How to sum up multiple fields without using foreach

akarivaratharaj — Thu, 28 Sep 2017 05:53:35 GMT

Thankyou Sukisen1981 for your response

Re: How to sum up multiple fields without using foreach

akarivaratharaj — Thu, 28 Sep 2017 05:54:42 GMT

Yeah... wish you the same.

Re: How to sum up multiple fields without using foreach

esalesap — Fri, 12 Aug 2022 14:38:32 GMT

less typing might be:

| timechart span=1w sum(eval(DATA_MB+INDEX_MB+DB2_DATA_MB+DB2_INDEX_MB+DB2_LOB_MB+DB2_LONG_MB+DB2_XML_MB)) as totalmb by DOMAIN limit=25

if you don't need all the individual sums.