Security

Query to loop through data in splunk

freephoneid
Path Finder

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

Tags (2)
0 Karma
1 Solution

gkanapathy
Splunk Employee
Splunk Employee

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

View solution in original post

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

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
0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...