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!

Observability Unlocked: Kubernetes Monitoring with Splunk Observability Cloud

 Ready to master Kubernetes and cloud monitoring like the pros? Join Splunk’s Growth Engineering team for an ...

Update Your SOAR Apps for Python 3.13: What Community Developers Need to Know

To Community SOAR App Developers - we're reaching out with an important update regarding Python 3.9's ...

October Community Champions: A Shoutout to Our Contributors!

As October comes to a close, we want to take a moment to celebrate the people who make the Splunk Community ...