How to split a time period into hourly intervals?

[jtest372]

index="dummy" url="https://www.dummy.com" status="200 OK"
| stats count by id
| where count > 10

If I apply this above query for 1 day, I would get this result, for example

id    count
ABC    50
XYZ    60

This would mean ABC called `https://www.dummy.com` 50 times in 1 day, and XYZ called that 60 times.

Now I want to check this for 1 day but with every two hours interval

Suppose, ABC called that request 25 times at 12:00 AM, and then calls it 25 times at 3:AM,
and XYZ called all the 60 requests between 12 AM and 2 AM

I want the output to look like this (time format doesn't matter)

id    count   time
XYZ    60   12:00 AM
ABC    25   12:00 AM
ABC    25   2:00 AM

Also, If I modify the query like this,  count > 30, instead of count > 10, then it should only show the XYZ field, since ABC has 25 counts for both of them.

 How do I modify my query? 

[SanjayReddy]

Hi @jtest372 

can you please try this

| timehcart span=2h count by id

[jtest372]

Thanks, Sanjay!

I tried this, but it gives me all ids as column headers like this

                        ABC       DEF       XYZ

12:00 AM               25         60         23

2:00 AM                25          0         12

4:00 AM                0          15         20

[SanjayReddy · Accepted solution]

Hi @jtest372 

Updated Answer

How about this ?

| bin  span=2h  _time
| stats count by id _time


---
If this reply helps you, an upvote/Karma would be appreciated.

[jtest372]

Thanks! This looks almost close, but 

bin _time span=1h is invalid

If I remove this line, it gives me the format I want but the span is not working.

[SanjayReddy]

Hi @jtest372 

may be time is not present in your data 

can you please replace _time with field which contains  contains time value

| bin <field which contains time > span=1h 

[jtest372]

| bin span=2h _time
| stats count by id _time

this worked for me! I just had to place _time at the end

[jtest372]

Placing _time at the end worked, you can edit your comment, I can accept it as the solution then! Thanks

[SanjayReddy]

Done Updated the Answer 

Thanks for the feedback