How to sum up multiple fields without using foreach?

[akarivaratharaj]

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.

[DalJeanis · Accepted solution]

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 

[esalesap]

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. 

[akarivaratharaj]

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.

[akarivaratharaj]

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.

[DalJeanis]

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

[akarivaratharaj]

Yeah... wish you the same.

[inventsekar]

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

[akarivaratharaj]

Thankyou inventsekar for your response.

[Sukisen1981]

have you tried -

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 col=true row=false

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

[akarivaratharaj]

Thankyou Sukisen1981 for your response

[akarivaratharaj]

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.,

[Sukisen1981]

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 ?

[akarivaratharaj]

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:

[akarivaratharaj]

I guess I am unable to upload the screenshot.