How to create a table that count the number of events by hour, based on now?

[Julia1231]

Hi everyone,

I have a db connect and get a table like this:

_time	count
12/09/2022 10:00	1
12/09/2022 10:01	1
12/09/2022 10:03	1
12/09/2022 10:04	1
12/09/2022 11:05	2
12/09/2022 11:15	5
12/09/2022 11:05	6
12/09/2022 11:17	4
12/09/2022 12:05	1
12/09/2022 12:10	1
12/09/2022 12:12	1

I want to find the trend of the event that I receive by hour, base on now:

What I understand, I have to count the number of event by hour, to achieve a table like this before choosing displaying by single value:

_time	count
12/09/2022 10:30	14
12/09/2022 11:30	3

Suppose: now: 12/09/2022 12:30. And I want to count events from 10:30-11:30 et 11:30-12:30.

If I use |timechart span=1h sum(count) as count, I have this table instead:

time	count
12/09/2022 10:00	4
12/09/2022 11:00	17
12/09/2022 12:00	3

Please, is it possible to find the table that I want?

Have  a nice day!

Julia

[scelikok]

Hi @Julia1231,

You can use timechart aligntime option to calculate the exact previous 1-hour data with seconds precision.

| timechart aligntime=@s span=1h sum(count) as count

[gcusello]

Hi @Julia1231,

try something like this:

<your_search>
| bin span=1h _time
| stats count BY _time
| sort 2 -_time

Ciao.

Giuseppe

[Julia1231]

Hi @gcusello 
Thanks for your feedback.

However it returns the table with 
10:00 et 11:00, not 10:30 and 11:30 as expected

[gcusello]

Hi @Julia1231,

please try this, it should work:

<your_search> earliest=-120m@m latest=@m
| eval hour=if(now()-_time>3600,"Hour_1","Hour_2")
| stats earliest(_time) AS earliest latest(_time) AS latest count BY hour
| eval hour=if(hour="Hour_1",strftime(earliest,"%H").".30",strftime(latest,"%H").".30"
| table hour count

note that the condition (>3600) depends on when the search is scheduled, in other words if is scheduled to run at minute 35, you have to use 3900.

Ciao.

Giuseppe

[Julia1231]

Sorry for not making it clear from the beginning. It's not running every "defined period", but I want to get the real time data.
Moreover, I use dbquery to get data from another source

[gcusello]

HI @Julia1231,

dbquery and real time are contradicting concepts, and real time reporting is a very expensive (for reasources) search mode: if you have many users that use this dashboard you could kill your system!

Anyway, you should define scheduling periods for classifying you events, otherwise I fear that your requirement cannot be reached.

Ciao.

Giuseppe

[Julia1231]

@gcusello 
Currently I add the filter by date (from now - days for example) inside the SQL search that I put in Splunk search, to be able to view the data real-time.

However, everytime if I want to change the period, I must do it inside the SQL query.

Do you have other solution please?