Query to loop through data in splunk

[freephoneid]

I've below lines in my log:

[email protected] id=1234 ....
[email protected] id=4565 ....
[email protected] id=5773 ....

1. Capture all those ids for the period from -1d@d to @d
2. For each id, search from beginning of index until -1d@d & see if the id is already present by comparing actual id field
3. If it is not present, then add it into the counter
4. Display this final count.

I just need the final count in one query.

Can I achieve this in Splunk?

Thanks!

[gkanapathy · Accepted solution]

It's much better to state your problem, rather than presume an algorithm for it. Would it be accurate to say that you want to know "how many people logged in for the first time yesterday"?

If that's the case, then I would ask you further: Are you running this query once? Or every day? Or how often, approximately? And I would ask, how many days are in your index before yesterday, approximately? Do you plan to keep this history around forever, or just a limited amount of time?

A simple, one shot answer is below. It is extremely inefficient if you are going to be running this query regularly, and/or if you have a large amount of history. But maybe your data set isn't that large in total, so it doesn't matter.

... latest=@d 
    | stats min(_time) as firsttime by id 
    | where (relative_time(now(),"-1d@d") <= firsttime) AND (firsttime < relative_time(now(),"@d"))
    | stats count