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