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.
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
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