topic Re: Stats count and field with oldest event in Splunk Search

Stats count and field with oldest event

dangeloma — Thu, 05 Nov 2020 20:04:31 GMT

I'm sure it's out there somewhere and maybe I'm just brain fried from looking at Splunk for too long, but I wasn't able to find or figure it out. Any help linking me to the answer or providing the answer would be greatly appreciated. I'm generating the results shown in the screen capture with this search - index="<my index>" sourcetype="<my sourcetype>"
| stats count BY Plugin,Severity
| sort -count

I'd like to add a column called "First Discovered" which contains a date value and I only want to see the oldest date from that field data. If I add to the stats count command "First Discovered" it's not going to work because there are multiple unique values. What's the best way to return the data I already have but add a column containing the oldest date from the field "First Discovered", for each of the Plugins. "First Discovered" data sample is - Jul 2, 2020 02:23:25 EDT and in case I wasn't clear, this field is extracted from the logs along with the "Plugin" and "Severity" fields.

Thanks in advance!

Re: Stats count and field with oldest event

aohls — Thu, 05 Nov 2020 20:14:16 GMT

This is what I have used and it seems to work well.

| stats count first(_time) as LastSeen by plugin, severity
| eval lastSeen=strftime(lastSeen, "%m/%d/%d %H:%M:%S")

Re: Stats count and field with oldest event

dangeloma — Thu, 05 Nov 2020 20:36:36 GMT

Thank you, that gets me a lot closer. I can't use "_time" because I'm ingesting the data from a CSV. So all of the events are stamped with the same ingestion time. I did modify your search to use the "First Discovered" field.

| stats count first("First Discovered") as "First Discovered" by Plugin, Severity

It returned "Jul 2, 2020 02:23:25 EDT" but the oldest value I have for the sample plugin I searched is "Sep 9, 2019 18:13:38 EDT".

I also tried "| stats count earliest" and the same date was returned. If I run the search with "| sort 1 -"First Discovered" and leave out the stats functions I get the date from 2019.

Re: Stats count and field with oldest event

bowesmana — Thu, 05 Nov 2020 20:46:14 GMT

@dangeloma

You just need to parse the date value before doing stats and then you use min(), i.e.

| eval fdd=strptime('First Discovered', "%b %d, %Y %H:%M:%S %Z") | stats count min(fdd) as fdd BY Plugin,Severity | sort -count | eval "First Discovered"=strftime(fdd, "%b %d, %Y %H:%M:%S")

So, you're just converting your first discovered date to an epoch, then min() is just looking for the lowest, i.e. oldest, date. Then you just reformat the date as needed after the stats.

Re: Stats count and field with oldest event

bowesmana — Thu, 05 Nov 2020 20:51:45 GMT

Just a quick comment on first(), earliest() and earliest_time()

first() is about event order and not related to time. earliest(X) will find the earliest _value_ of X and earliest_time(X) finds the earliest _time of X according to the event's _time field

See https://docs.splunk.com/Documentation/Splunk/8.1.0/SearchReference/Stats#Usage

Re: Stats count and field with oldest event

dangeloma — Fri, 06 Nov 2020 13:10:15 GMT

Thank you, this also got me a lot closer. I ended up having to convert the time with "mktime".

This post helped with the conversion:
https://community.splunk.com/t5/Splunk-Search/How-can-i-convert-String-Type-Time-field-a-to-a-human-readable/m-p/93941#M24184