Splunk Search

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

Sam1
Explorer

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?

Labels (2)
Tags (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

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?

0 Karma

Sam1
Explorer

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?

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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
Explorer

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?

 

0 Karma

Sam1
Explorer

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$

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...