Finding Metrics That Fell by 10% in an Hour

[stephan_berger]

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

[lguinn2]

[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.

[martin_mueller]

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?

[stephan_berger]

🙂

-> 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.

[martin_mueller]

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

[stephan_berger]

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.

[martin_mueller]

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

[lguinn2]

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

[stephan_berger]

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

[martin_mueller]

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