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

[Sam1]

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.

index=data
| timechart span=1h count
| timewrap d series=short
| addtotals s*
| eval 7dayavg=Total/7.0
| table _time, _span, s0, 7dayavg
| rename s0 as now

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?

[bowesmana]

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?

[Sam1]

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?

[bowesmana · Accepted solution]

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

index=_internal
| timechart span=1m count
| timewrap d series=short
| addtotals s*
| table _time, s0, Total
| streamstats window=60 sum(*) as *
| eval 7dayavg=Total/7.0
| rename s0 as now
| tail 1
| fields _time now 7dayavg

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.

[Sam1]

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?

[Sam1]

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$

[bowesmana]

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

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.