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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...