Splunk Search

Table with with a column that contains positive and negative values. I want to sum the total of all values and have the results as a Total, but it ignores the negative values. What am I doing wrong?

ihaveasplunkacc
Loves-to-Learn Lots

| table Account "Estimated Gain_Loss"
| addcoltotals labelfield="Account" label="Totals"
| sort -"Estimated Gain_Loss"

alt text

0 Karma
1 Solution

to4kawa
Ultra Champion

hi, @ihaveasplunkaccount

| makeresults
| eval _raw="Account Estimated_Gain_Loss
ACCOUNT1 1000
ACCOUNT2 2000
ACCOUNT3 500
ACCOUNT4 -300"
| multikv forceheader=1
| table Account Estimated_Gain_Loss
| addcoltotals labelfield=Account

This query shows correct result. but,

| makeresults
| eval _raw="Account Estimated_Gain_Loss
ACCOUNT1 $1000
ACCOUNT2 $2000
ACCOUNT3 $500
ACCOUNT4 -$300"
| multikv forceheader=1
| table Account Estimated_Gain_Loss
| addcoltotals labelfield=Account

this query is not work.

your sample has $ mark. Actually, is this right?

| makeresults
| eval _raw="Account Estimated_Gain_Loss
ACCOUNT1 $1000
ACCOUNT2 $2000
ACCOUNT3 $500
ACCOUNT4 -$300"
| multikv forceheader=1
| table Account Estimated_Gain_Loss
| eval Estimated_Gain_Loss=replace(Estimated_Gain_Loss,"\$","")
| addcoltotals labelfield=Account

this query excludes $ mark and calculates total.
sorry, why I don't use Code Sample is because extra space is added.

View solution in original post

0 Karma

to4kawa
Ultra Champion

hi, @ihaveasplunkaccount

| makeresults
| eval _raw="Account Estimated_Gain_Loss
ACCOUNT1 1000
ACCOUNT2 2000
ACCOUNT3 500
ACCOUNT4 -300"
| multikv forceheader=1
| table Account Estimated_Gain_Loss
| addcoltotals labelfield=Account

This query shows correct result. but,

| makeresults
| eval _raw="Account Estimated_Gain_Loss
ACCOUNT1 $1000
ACCOUNT2 $2000
ACCOUNT3 $500
ACCOUNT4 -$300"
| multikv forceheader=1
| table Account Estimated_Gain_Loss
| addcoltotals labelfield=Account

this query is not work.

your sample has $ mark. Actually, is this right?

| makeresults
| eval _raw="Account Estimated_Gain_Loss
ACCOUNT1 $1000
ACCOUNT2 $2000
ACCOUNT3 $500
ACCOUNT4 -$300"
| multikv forceheader=1
| table Account Estimated_Gain_Loss
| eval Estimated_Gain_Loss=replace(Estimated_Gain_Loss,"\$","")
| addcoltotals labelfield=Account

this query excludes $ mark and calculates total.
sorry, why I don't use Code Sample is because extra space is added.

0 Karma

ihaveasplunkacc
Loves-to-Learn Lots

Wow, your sample proved thing correct. Thank you. The problem was with the negative numbers in my sample. They actually had the $ before the value in the field. I thought it was showing up because of table formatting that I had done in my dashboard. I stripped the $ sign out and then the math worked. Your answer is correct and really helped. Thank you! Have a great weekend.

0 Karma

ihaveasplunkacc
Loves-to-Learn Lots

I just realized the sample data I posted had slight formatting error, but the problem is still the same. The samples below have the correct values.

Results in Excel with the output that I am looking for

Account Estimated Gain_Loss
Total 3200
ACCOUNT1 1000
ACCOUNT2 2000
ACCOUNT3 500
ACCOUNT4 -300

Results in Splunk with incorrect total

Account Estimated Gain_Loss
Total 3500
ACCOUNT1 1000
ACCOUNT2 2000
ACCOUNT3 500
ACCOUNT4 -300

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...