Splunk Search

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

Splunk2016
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

chimell
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

chimell
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"  ]

chimell
Motivator

i need that you vote me

0 Karma

Splunk2016
Path Finder

I just did. Thanks!

0 Karma

chimell
Motivator

Good ......

0 Karma

Splunk2016
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

Splunk2016
Path Finder

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

0 Karma

Splunk2016
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

chimell
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

somesoni2
Revered Legend

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

chimell
Motivator

thanks you

0 Karma

Splunk2016
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
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...