Splunk Search

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

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

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

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

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!