Getting Data In

How to get the latest event from duplicate events and count a specific value for that latest event?

maximusdm
Communicator

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

Tags (2)
0 Karma

gokadroid
Motivator

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
0 Karma

cmerriman
Super Champion

|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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...