Splunk Search

How to get timestamp of the beginning event in the stats count by

ibob0304
Communicator
 sourcetype=priorityEvents | rex field=_raw "User\sID\s(?<user_id>.\d{0,8}+)"  | stats count by user_id | where count > 1

Output

      user_id           count

      234919              3
      871241             12
      881222              4

My desired Output should be

  date_firstoccurance         user_id           count

    05/12/2017                 234919                 3
    05/13/2017                 871241                12
    05/16/2017                 881222                 4

The interesting thing for me is the user_id might come in multiple days,for example, 234919 comes in 12th and 13th logs as well, but we would require only first occurrence date, i.e 12th.

0 Karma
1 Solution

rjthibod
Champion

Change your stats to include min(_time) or earliest(_time). The latter is faster, but the former is going to be correct if you indexed things out of chronological order.

For example,

sourcetype=priorityEvents 
| rex field=_raw "User\sID\s(?<user_id>.\d{0,8}+)"  
| stats count as count, earliest(_time) as date_firstoccurance by user_id 
| where count > 1

Note, if your timestamps are not in the date format you want, then you will need to add another eval to get the field date_firstoccurance in the correct format.

| eval date_firstoccurance = strftime(earliest, "%m/%d/%Y")

So your full query might be

sourcetype=priorityEvents 
| rex field=_raw "User\sID\s(?<user_id>.\d{0,8}+)"  
| stats count as count, earliest(_time) as earliest by user_id 
| where count > 1
| eval date_firstoccurance = strftime(earliest, "%m/%d/%Y")
| table date_firstoccurance user_id count

View solution in original post

rjthibod
Champion

Change your stats to include min(_time) or earliest(_time). The latter is faster, but the former is going to be correct if you indexed things out of chronological order.

For example,

sourcetype=priorityEvents 
| rex field=_raw "User\sID\s(?<user_id>.\d{0,8}+)"  
| stats count as count, earliest(_time) as date_firstoccurance by user_id 
| where count > 1

Note, if your timestamps are not in the date format you want, then you will need to add another eval to get the field date_firstoccurance in the correct format.

| eval date_firstoccurance = strftime(earliest, "%m/%d/%Y")

So your full query might be

sourcetype=priorityEvents 
| rex field=_raw "User\sID\s(?<user_id>.\d{0,8}+)"  
| stats count as count, earliest(_time) as earliest by user_id 
| where count > 1
| eval date_firstoccurance = strftime(earliest, "%m/%d/%Y")
| table date_firstoccurance user_id count
Get Updates on the Splunk Community!

OpenTelemetry for Legacy Apps? Yes, You Can!

This article is a follow-up to my previous article posted on the OpenTelemetry Blog, "Your Critical Legacy App ...

UCC Framework: Discover Developer Toolkit for Building Technology Add-ons

The Next-Gen Toolkit for Splunk Technology Add-on Development The Universal Configuration Console (UCC) ...

.conf25 Community Recap

Hello Splunkers, And just like that, .conf25 is in the books! What an incredible few days — full of learning, ...