Splunk Search

How can I display a new calculated total field appended at the end of each event?

Path Finder

I would appreciate any comments.

Search Case 1

host="HP" 
sourcetype="csv"

Displays all fields for 8292 events

Search Case 2

host="HP" 
sourcetype="csv" 
| table ActionObligation 
| makemv ActionObligation 
| mvexpand ActionObligation 
| replace "$*" with "*","($*)" with "-*" in ActionObligation 
| eval ActionObligation1=tonumber(replace(ActionObligation,",","")) 
| eventstats sum(ActionObligation1) as Total 
| eval Total=if(Total>0,"$".tostring(Total,"commas"),"($".tostring(Total*-1,"commas").")")

Displays 8292 events with these 3 fields:

Action Obligation     Action Obligation1     Total
        1                     1             $200,000

How can I display Case 1 and Case 2 together? Case 1 would have 3 additional fields from case 2 added to end of each event.

Example:

(Case 1)                               (Case 2)
event       Action Obligation     Action Obligation1      Total
values...           1                     1              $200,000

Thanks!

0 Karma
1 Solution

Motivator

Hi Splunk2016
to do this use the search below use appendcols command like this :
Case2 | appendcols [search Case1 ]

host="HP" sourcetype="csv"
| table ActionObligation
| makemv ActionObligation
| mvexpand ActionObligation
| replace "$*" with "*","($*)" with "-*" in ActionObligation
| eval ActionObligation1=tonumber(replace(ActionObligation,",",""))
| eventstats sum(ActionObligation1) as Total
| eval Total=if(Total>0,"$".tostring(Total,"commas"),"($".tostring(Total*-1,"commas").")")
| appendcols [search host="HP" sourcetype="csv"  ]

View solution in original post

Motivator

Hi Splunk2016
to do this use the search below use appendcols command like this :
Case2 | appendcols [search Case1 ]

host="HP" sourcetype="csv"
| table ActionObligation
| makemv ActionObligation
| mvexpand ActionObligation
| replace "$*" with "*","($*)" with "-*" in ActionObligation
| eval ActionObligation1=tonumber(replace(ActionObligation,",",""))
| eventstats sum(ActionObligation1) as Total
| eval Total=if(Total>0,"$".tostring(Total,"commas"),"($".tostring(Total*-1,"commas").")")
| appendcols [search host="HP" sourcetype="csv"  ]

View solution in original post

Motivator

i need that you vote me

0 Karma

Path Finder

I just did. Thanks!

0 Karma

Motivator

Good ......

0 Karma

Path Finder

Hello Chimell,

How familiar are you with Splunk's Pivot tables? I had about 110 that have seen my question on summarizing totals by department but no one have come with an answer as of yet. This is a simple sum that can easily be done under Excel but it requires a number of steps under Splunk. I have gotten close to summarizing it using a lookup table but it did not added up the duplicate amounts. Thanks!

0 Karma

Path Finder

No need to respond, I was able to figure it out. Thanks!

0 Karma

Path Finder

Hi Chimell,

It does not work with "eventstats" but it worked with "stats" ! Thank you for your answer!

Also there is no difference if I include "Table event" since it comes up with the
same results. I don't know what the event command does.
host="HP" sourcetype="csv"

| table ActionObligation

| makemv ActionObligation

| mvexpand ActionObligation

| replace "$" with "","($)" with "-" in ActionObligation

| eval ActionObligation1=tonumber(replace(ActionObligation,",",""))

| stats sum(ActionObligation1) as Total

| eval Total=if(Total>0,"$".tostring(Total,"commas"),"($".tostring(Total*-1,"commas").")")

| appendcols [search host="HP" sourcetype="csv"]
Do you know the difference between eventstats and stats?

0 Karma

Motivator

Hi Splunk2016
Thank you for your answer
stats command Provides statistics, grouped optionally by field.
while eventstats command Adds summary statistics to all search results.
For more information , follow this link http://docs.splunk.com/Documentation/Splunk/6.2.2/SearchReference/WhatsInThisManual

0 Karma

SplunkTrust
SplunkTrust

How about just this.

host="HP" 
 sourcetype="csv" 
 | table event ActionObligation 
 | makemv ActionObligation 
 | mvexpand ActionObligation 
 | replace "$*" with "*","($*)" with "-*" in ActionObligation 
 | eval ActionObligation1=tonumber(replace(ActionObligation,",","")) 
 | eventstats sum(ActionObligation1) as Total 
 | eval Total=if(Total>0,"$".tostring(Total,"commas"),"($".tostring(Total*-1,"commas").")")
0 Karma

Motivator

thanks you

0 Karma

Path Finder

Your example is similar to Case 2 with event but the results are still the same. I need Case 1 to show also and the Total field appended to end of all my events in Case 1. Thanks!
Case 1 shows under Events
Case 2 shows under Statistics
Can Total show under Events as one of the fields appended to the end?

0 Karma