count based on time range

[harish_ka]

I have search result of last 10 days.
Can we get the count based on time range, like "count(Alert) as Total count where timestamp=CurrentDate-5" (to get count of last 5 days).
I have to get the count of last 7 days,last 3 days from the same search result.
Now i am using "Join" with earliest and latest for each and every column like count of last 7 days,last 3 days etc..
the size of query is getting bigger when i use join. Any other way to get this result??

Any idea how to implement this...

[wpreston]

You could try using eval to get this. A day is 86,400 seconds, so 7 days would be 604,800. 3 days would be 259,200 seconds. So maybe something like this:

... your search here... 
| eval timeDiff=now() - _time 
| stats count(eval(timeDiff < 604801 AND timeDiff > 259200)) as Last7Days count(eval(timeDiff < 259201)) as Last3Days

[wpreston]

If Book is a field in your data, just can just add it into the eval statement. As for evaluating a number of days worth of records, just multiply 86,400 by the number of days you need and make eval statement similar to what I've written above. So for your specific case:

5 days = 86,400 * 5 =
7 days = 86,400 * 7 = 604,800
10 days = 86,400 * 10 = 864,000

So your search will look like this:

... your search here... 
| eval timeDiff=now() - _time 
| stats stats count(eval(timeDiff < 432001 AND Book=*)) as Last5Days count(eval(timeDiff < 604801 AND Book=*)) as Last7Days count(eval(timeDiff < 864001 AND Book=*)) as Last10Days

In this search, the counts are inclusive of each other, meaning that the count for the last 7 days includes the books from the count of the last 5 days, and the count of the last 10 days includes the books from the last 7 days and last 5 days.

[harish_ka]

Thanks Wpreston for your answer,

I need to get count of books.. it is like stats count(Book).
So how can i use the above query to get count for last 10,7,5 days...
Can you help me with the syntax...

[harish_ka]

Can anyone help me on this?? its urgent...