Splunk Enterprise

Sum of maximum value of field up to point in time, over time

ccampbellveraco
Explorer

Edited to make my field extractions and needs clearer....

This is best explained with an example of my events and what I'm looking for. I'm not entirely sure if the title accurately reflects what I'm after but it's the only way I can think to explain it right now.

I have events, with appropriate field extractions, like this:

| _time      Name  Amount
| 2017-01-01 App-A 1000
| 2017-01-02 App-B 2000
| 2017-01-03 App-A 1500
| 2017-02-01 App-A 2000
| 2017-02-02 App-B 1500
| 2017-02-03 App-B 1500
| 2017-03-01 App-A 2000
| 2017-03-02 App-B 2500
| 2017-04-01 App-A 2500
| 2017-05-01 App-B 3000

I want to chart the sum of the maximum 'amount' seen so far for each 'name', up to the time value of the x axis of the chart.

The statistics should look like (without the bracketed info, this is just to make it clear how I come to the TotalAmount value):

| _time     TotalAmount
| 2017-01   3500         (1500+2000)
| 2017-02   4000         (2000+2000)
| 2017-03   4500         (2000+2500)
| 2017-04   5000         (2500+2500)
| 2017-05   5500         (2500+3000)

The main issue I am having appears to be that I can't figure out how to include a max value of 'amount' for a 'name' in the sum for a month if that name does not appear in the month bin.

Tags (1)
0 Karma
1 Solution

ccampbellveraco
Explorer

I finally figured this out using the filldown command:

search... | streamstats max(Amount) as Biggest_Amount by Name | timechart max(Biggest_Amount) as Biggest_Amount by Name where top1000 | filldown | addtotals | fields _time Total

View solution in original post

0 Karma

ccampbellveraco
Explorer

I finally figured this out using the filldown command:

search... | streamstats max(Amount) as Biggest_Amount by Name | timechart max(Biggest_Amount) as Biggest_Amount by Name where top1000 | filldown | addtotals | fields _time Total
0 Karma

mayurr98
Super Champion

Try this

index=your_index | rex field=_time “?P<month>\d{4}\-\d{2}”| stats max(Amount) as Amount by Name month | stats sum(Amount) As total_count by month

Let me know if it works for you!!

0 Karma

ccampbellveraco
Explorer

That rex doesn't seem to work:

Error in 'rex' command: Encountered the following error while compiling the regex '?P<month>\d{4}-\d{2}': Regex: quantifier does not follow a repeatable item
0 Karma

mayurr98
Super Champion

can you tell me how is your timestamp look like in an event? and where it is specified ? is it specified at the beginning or in between?

0 Karma

mayurr98
Super Champion

There are many ways to do it . Try this as well

 index=your_index  | stats max(Amount) as Amount by Name date_year date_month| stats sum(Amount) As total_count by date_year date_month | eval date=date_year+"-"+date_month | fields date total_count
0 Karma

ccampbellveraco
Explorer

Just to show the output of your suggestion:

2017-april  2177462145
2017-august 2939451426
2017-february   1670151484
2017-january    1300279191
2017-july   2779289387
2017-june   2617983671
2017-march  2572205729
2017-may    2366115019
2017-november   3871717110
2017-october    4344131497
2017-september  3043633510

November should be the highest value but it's not.

0 Karma

ccampbellveraco
Explorer

That doesn't work either. If I run that right now on a subset of my data, I get no column for November as the data has no events in November, even though we are currently in November.

0 Karma

ccampbellveraco
Explorer

My actual event looks like this:

"12345","App-A","Business-unit-A","1114052","30 Dec 2016 Static","static","2017-01-02 10:41:20+00:00","2017-01-02 10:39:51+00:00","147294802","106822","2016-12-14 10:19:58+00:00","3","117","Improper Output Neutralization for Logs","true","2","Open","Not Mitigated","1","appname-1.0-SNAPSHOT.war","filename.java","107"

The field used to create the event _time is the second UTC date, in this case 2017-01-02 10:39:51+00:00. though I'm not really sure that matters?

I also don't think your solution will work for months where the 'name' does not appear in any events. For example, using the data in my original question, I need App-A to be included in the calculation for May even though it does not have any events in May.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Hi
Can you please try this one?

YOUR_SEARCH.....
| table Date Name Amount 
| rex field=Date "(?<C_Month>.*)\-(?<C_Date>.*)" | eventstats max(C_Date) as Max_Date by Name C_Month | where C_Date=Max_Date  | stats sum(Amount) as TotalAmount by C_Month

Thanks

0 Karma

mayurr98
Super Champion

Try this

index=your_index | rex field=_time “?P<month>\d{4}\-\d{2}”| stats max(Amount) as Amount by Name month | stats sum(Amount) As total_count by month

Let me know if it works for you!!

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