This is my sample data:
_time duration ID
2017-01-12 19:40:03 5 AAAAA
2017-01-12 19:42:03 10 AAAAA
2017-01-12 19:45:03 19 AAAAA <== count as Total_Events_Checked
----------------------------------------------------------------------------------------------------------------
2017-01-12 19:40:03 5 BBBBB
2017-01-12 19:41:03 15 BBBBB
2017-01-12 19:42:03 39 BBBBB
2017-01-12 19:43:03 48 BBBBB
2017-01-12 19:44:03 51 BBBBB <== * count as > 20
I need to count the number of time the 'duration' is greater than 20. However only the latest record for a specific ID should be checked.
If greater than 20 then I count it.
I also need to count the total of IDs checked so that I can create a percentage value.
And I need to display the data per day, so something like:
Date Count_GreaterThan_20 Total_Events_Checked
2017-01-12 1 2
I could remove the dups but that wouldnt give me the latest event of each ID.
I also found a command called 'transaction' but that seems to not help in my scenario here.
From my research I found that you can also get the most recent value of a particular field using the "first" function in stats
but that seems to not be working.
I only have some draft notes. Sorry I am new to this splunk thing:
* | timechart span=24h count(eval(duration<=50)) AS Count_GreaterThan_20, count(duration) AS Total_Events_Checked
Thank you
Hope this works out:
your query to return events
| bucket _time span=1d
| eval date=strftime(_time, "%Y-%m-%d")
|eventstats dc(ID) as TEC
| stats latest(duration) as Latest, values(TEC) as TotalEventsChecked by ID, date
| search Latest>=20
| stats count(Latest) as Count_GreaterThan_20, values(TotalEventsChecked) as Total_Events_Checked, values(date) as Date
|dedup
should give you the latest event, if you did |dedup _time ID
but you said that doesn't work?
you can also try |sort 0 ID - _time|stats latest(duration) as duration by _time ID|eval GreaterThan20=if(duration>20),1,0)|stats sum(GreaterThan20) as GreaterThan20 count as TotalEventsChecked