Query to loop through data in splunk

freephoneid — Mon, 26 Sep 2011 16:02:50 GMT

I've below lines in my log:

...useremail=abc@fdsf.com id=1234 ....
...useremail=pqr@fdsf.com id=4565 ....
...useremail=xyz@fdsf.com id=5773 ....

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

I just need the final count in one query.

Can I achieve this in Splunk?

Thanks!

Re: Query to loop through data in splunk

gkanapathy — Mon, 26 Sep 2011 22:37:11 GMT

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

topic Query to loop through data in splunk in Security

Query to loop through data in splunk

Re: Query to loop through data in splunk