topic Re: How to timewrap using the last 1 hour and check the same hour for previous 7 days in Splunk Search

How to timewrap using the last 1 hour and check the same hour for previous 7 days

Sam1 — Mon, 22 Jun 2020 23:19:59 GMT

Hi everyone,

I want to create an alert which runs every hour, checks the last 60 minutes of events to get the count number, then compares this with the average of the past 7 days.

This displays every hour for today and 7dayavg but how do i just show for the past 60 minutes, then compare that with the 7dayavg of the same 60 minute time block?

Re: How to timewrap using the last 1 hour and check the same hour for previous 7 days

bowesmana — Tue, 23 Jun 2020 01:28:56 GMT

If I follow you correctly, you want to only show the most recent hour (or is it really 60 minutes?) and the 7 day average.

If so, they just adding

| tail 1

to the end of the query will give you the last row, which is most recent time.

Note that because you're doing a timechart span=1h your most recent 'hour' may not be representative of the hour, as it will only include minutes from :00

So, you could do a

| tail 2 | tail 1

which is somewhat counterintuitive, as I would have expected to be able to do head 1, but it seems to reverse the results with the tail 2.

Anyway, does that give you what you're after?

Re: How to timewrap using the last 1 hour and check the same hour for previous 7 days

Sam1 — Tue, 23 Jun 2020 02:57:14 GMT

Thank you. How about to get a true 60 minutes? So if i was to run my search at 3:11 it would capture from 2:11 - 3:11?

Re: How to timewrap using the last 1 hour and check the same hour for previous 7 days

bowesmana — Tue, 23 Jun 2020 22:13:22 GMT

As span=1d will round the time window down to the hour you can't run span=1h, so this may work for you

It's using a 1m span and then later using streamstats to create the totals from the 60 row groups, so the last row will contain what you want.

Not sure if there's a more efficient way to do this, but this works.

Re: How to timewrap using the last 1 hour and check the same hour for previous 7 days

Sam1 — Tue, 30 Jun 2020 07:39:15 GMT

Thank you!

I'm now trying to work out how to do this with

| timechart span=1m count by $field$

Any ideas? Do i need to have an addtotals for each field value? And once i get to tail -1, can the values be grouped in rows by the field value?

Re: How to timewrap using the last 1 hour and check the same hour for previous 7 days

Sam1 — Tue, 30 Jun 2020 07:47:05 GMT

Or can i create another search which calls the search you provided

Something like savedsearch but i want to iterate through multiple values of my field.

I'd need to add the field to the search you provided i guess as well:

index=_internal field=$value$

Re: How to timewrap using the last 1 hour and check the same hour for previous 7 days

bowesmana — Tue, 30 Jun 2020 22:02:49 GMT

Take a look at the solution I proposed here

https://community.splunk.com/t5/Splunk-Search/Using-timechart-command-isn-t-working-for-renaming/m-p/469691#M132171

which describes how to handle the naming issue around columns when using the split by clause. You should be able to combine the solution below with that to achieve what you are trying to do.

When using the split by in the timechart, the columns become named based on the split by field, so the key feature in that post is to add a common prefix to the field value, so when it becomes a field name in the timechart, it will allow you to 'discover' the field names using the foreach command.

Hope this helps.