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!

Demo Day: Strengthen Your SOC with Splunk Enterprise Security 8.1

Today’s threat landscape is more complex than ever. Security operation centers (SOCs) are overwhelmed with ...

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...