topic Re: Sum of maximum value of field up to point in time, over time in Splunk Enterprise

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

ccampbellveraco — Fri, 10 Nov 2017 23:59:41 GMT

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.

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

mayurr98 — Sat, 11 Nov 2017 03:02:17 GMT

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

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

kamlesh_vaghela — Sat, 11 Nov 2017 07:28:56 GMT

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

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

mayurr98 — Sat, 11 Nov 2017 11:27:50 GMT

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

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

ccampbellveraco — Sat, 11 Nov 2017 11:39:01 GMT

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

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

mayurr98 — Sat, 11 Nov 2017 11:44:45 GMT

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?

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

ccampbellveraco — Sat, 11 Nov 2017 11:57:06 GMT

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.

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

mayurr98 — Sat, 11 Nov 2017 12:01:22 GMT

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

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

ccampbellveraco — Sat, 11 Nov 2017 12:14:11 GMT

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.

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

ccampbellveraco — Sat, 11 Nov 2017 12:18:46 GMT

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.

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

ccampbellveraco — Mon, 13 Nov 2017 18:58:23 GMT

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