How to search for earlier events for a given field?

[pladamsplunk]

I'm trying to create a report which will find the number of 'new users'. I've extracted the field user. I want to find the earliest event generated for every given user within the user field, and then create the restriction to only show the number of users whose first events were created within some recent time. This number will be the number of new users. Can anyone help me create a search to accomplish this goal?

[sundareshr · Accepted solution]

Try this

index=allevents sourcetype=somelogs | stats earliest(_time) as firstevent by user | where _time>some_recent_time

[pladamsplunk]

when I do the search "dex=allevents sourcetype=somelogs | stats earliest(_time) as firstevent by user" i get all the users with a field 'firstevent' wich is a string of numbers and I can't quite make out what this means.

[sundareshr]

The string of numbers is epoch time, You can format the time like this. I would recommend you leave it as epoch to do the time compare and then change to readable format. (.... depicts your current search)

.... | eval firstevent=strftime(firstevent, "%c")

To filter based on time, you could try something like this

.... | where firstevent>relative_time(now(), "-1mon") | eval firstevent=strftime(firstevent, "%x %X")

[pladamsplunk]

.... | where firstevent>relative_time(now(), "-1mon") | eval firstevent=strftime(firstevent, "%x %X")

This command will compare the current date stored in splunk against the -1mon (or whatever is here) correct?

[sundareshr]

That's Correct

[pladamsplunk]

It all works thank you very much, up voted!

[sundareshr]

Please mark the answer as accepted to close it out.