Searching back in time to determine newness (or not) of a value in a given field

[JohnR]

Given a field containing a "userId", I want a count per day of unique userIds by "new" vs "returning". E.g. Ends up with this output...

| timechart span=1d dc(userId) by newOrReturning

or I suppose...

| bin _time span=1d
| stats dc(userId) by _time, newOrReturning

A userId is "new" on a given day if there is no record of it from any previous day, going as far back as a known fixed date.

The bit I'm having trouble with is how to set newOrReturning, since (I assume) I need to somehow search back in time for a matching userId from previous days.

[ITWhisperer · Accepted solution]

Try something like this (set time period to be all time or since whatever date you want to consider)

| bin _time span=1d
| eventstats earliest(_time) as firsttime by userid
| eval newOrReturning=if(_time=firsttime,"New","Returning")
| stats dc(userId) by _time, newOrReturning