How to count events when hour is finished?

[Julia1231]

Hi everyone,

I am doing a search to find all the events that sent from different servers by hour, to find if any server is down, send nothing so that I will send an alert.

raw data looks like this:

_time	count
2022-09-27T10:17:48	1
2022-09-27T09:57:19	1
2022-09-27T09:56:28	1
2022-09-27T09:56:26	1

I search for events by span=1h and have a table like this: (There are several servers but  I put one for example)

_time	Server	Count
27/09/2022 12:00	A	0
27/09/2022 11:00	A	0
27/09/2022 10:00	A	1
27/09/2022 09:00	A	3
27/09/2022 08:00	A	9
27/09/2022 07:00	A	10

It works now but not very fine for current hour.

Imagine it's 12:05 now. when I run the search, I filter by count = 0 at 12:05, I have 2 line
However, for example at 12:30, I receive the 1st event from server A, so the filter 0 returns 1 line of 11:00.

What I want is to taken into account the count  = 0 only when the time passed 1 hour, to send a good alert. In the example, the filter 0 will return only if the server doesn't send anything during 1h (from 12:00 - 12:59)

Currently I do something like this: | where count = 0 AND _time != relative_time(now(), "-1h") but do you have any better solution?

I hope I make it clear.

Thanks for your help! 

[FelixLeh]

If I understand your problem correctly, you have to set your search range to "-1h@h" it will then always start the search at the beginning of the last hour. For example if you start a search at 12:05 with "-1h@h" the range will be 11:00-12:00.

_______________________________________

If this was helpful please consider awarding Karma. Thx!

[andrew_nelson]

To add to this, make sure to set latest=-0h@h. 
That way it disregards everything in the current hour (until the next hour).

[Julia1231]

Hi @andrew_nelson 

At the beginning of the search, I use dbxquery.

So, where can I put the latest=-0h@h please?

[andrew_nelson]

Ok, you should be able to filter it out in the SQL query by using the below WHERE filter. Just replace <timestamp_field> with the name of the field in your database table. It basically calculates the time at the start of this hour and makes sure that the timestamp is before that. 

WHERE <timestamp_field> < dateadd(hour, datediff(hour, 0, getdate()),0)

[Julia1231]

Hi @andrew_nelson , 

When I do this, it tells me that datediff(unknown, integer, timestamp with time zone) doesn't exist

WHERE time < dateadd('hh', datediff('hh', 0, NOW()),0)    

I use Postgresql, so I use NOW() insteads of getdate()

Do you have any idea, please?