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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...