topic Re: Finding Metrics That Fell by 10% in an Hour in Splunk Search

Finding Metrics That Fell by 10% in an Hour

stephan_berger — Sat, 09 Feb 2013 18:49:50 GMT

Hello Splunk Community,

I have a question regarding this query (excerpt from the great splunk book):

      earliest=-2h@h latest=@h
      | stats count by date_hour,host
      | stats first(count) as previous, last(count) as current by host
      | where current/previous < 0.9

For now, I have a query like this:

      earliest=-2m latest=now() | 
      stats count(request) by request, access_canonical_server_name

Which gives me a list of all requests made to a domain. I would like to compare the counted requests in the past to the count of the requests made in the present, to detect a fall of the metrics like in the inital query above.

Thanks for your support.

Best regards

Re: Finding Metrics That Fell by 10% in an Hour

martin_mueller — Sat, 09 Feb 2013 21:28:09 GMT

How is your problem different from the book's example?

Re: Finding Metrics That Fell by 10% in an Hour

lguinn2 — Sun, 10 Feb 2013 01:31:58 GMT

[Edited - Thanks Martin! I made a dumb typo! I have fixed the time range in the first line of the search so that latest is now the beginning of the latest hour, not the beginning of the hour before that....]

Despite the fact that the book uses date_hour, I would do it this way:

earliest=-2h@h latest=@h request=*
| eval hour = relative_time(_time,"@h")
| stats count by access_canonical_server_name, hour
| stats first(count) as previous, last(count) as current by access_canonical_server_name
| where current/previous < 0.9

I would not use date_hour for two reasons: first, it isn't always available. Second, it is a simple hour number (0-23), which means that it won't work well when the hours cross a date boundary. The above calculation for hour will solve both of those problems.

Also, I have set the time range to be full hours: 2 hours ago compared to 1 hour ago. If you set this search to run every hour at :05 past the hour, it will reliably report on the previous hours. However, if you don't use the @h, you may find that you are comparing a full hour to a partial hour - which is probably not what you want.

Re: Finding Metrics That Fell by 10% in an Hour

stephan_berger — Mon, 28 Sep 2020 13:17:07 GMT

Hello,

thanks for your answer and your effort! Unfortunately, the query is not working quite right:

access_canonical_server_name, current, previous, delta
domain.com, 145, 145, 1
anotherdomain, 56, 56, 1
subdomain.domain.com, 77,77,1

The search finds the same results for current and previous, resulting in a delta from 1 or 0 respectively. Would you mind to re-check your query again?

Thanks!

Best regards,
Steven

Re: Finding Metrics That Fell by 10% in an Hour

lguinn2 — Sun, 10 Feb 2013 20:09:39 GMT

What do you get when you run just this part of the search?

earliest=-2h@h latest=-1h@h request=* | eval hour = relative_time(_time,"@h") | stats count by access_canonical_server_name, hour

Re: Finding Metrics That Fell by 10% in an Hour

martin_mueller — Sun, 10 Feb 2013 22:20:49 GMT

"by hour" should only yield one result since you snapped to the hour with an interval of only one hour.

Re: Finding Metrics That Fell by 10% in an Hour

stephan_berger — Mon, 28 Sep 2020 13:17:10 GMT

access_canonical_server_name, hour, count
domain.com, 1360555200.000000, 20
anotherdomain.com, 1360555200.000000, 11

Do you have an idea how to solve my problem I asked on the initial question? Thank.

Re: Finding Metrics That Fell by 10% in an Hour

martin_mueller — Mon, 11 Feb 2013 06:45:08 GMT

I'm not sure I understand what you want yet 🙂

Re: Finding Metrics That Fell by 10% in an Hour

stephan_berger — Mon, 28 Sep 2020 13:17:13 GMT

🙂

-> 2 hours ago
access_canonical_server_name, count_of_requests
domain.com, 512
anotherdomain.com, 77

-> 1 hour ago
access_canonical_server_name, count_of_requests
domain.com, 65
anotherdomain.com, 80

As you can see, the requests for domain.com dropped dramatically in the last hour, compared to two hours ago. I want to detect such behavior. Is this clearer now? Thanks for your assistance.

Re: Finding Metrics That Fell by 10% in an Hour

martin_mueller — Mon, 11 Feb 2013 07:52:34 GMT

So in other words if it is 8:51am you want to count the requests from 7:51am to now and compare them to the count of requests from 6:51am to 7:51am?