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

[ibob0304]

 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.

[rjthibod · Accepted solution]

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