Splunk Search

How to fetch results to a table whose columns are sorted by date AND a timeframe of that date?

lmonahan
Path Finder

I have a test that I run between 1am and 6am each night. I am able to fetch the results for the last 21 days using this query:

index=myindex host=host1 OR host=host2 event=“*event1*” OR event=“*event2*” earliest=-21d latest=now 
| eval date=strftime(_time, "%Y-%m-%d") 
| eval rounded_mean = round(‘my data’, 2) 
| chart perc50(rounded_mean) over name by date limit=0

This query results in a table with a column for each date and a row for each event.

However, sometimes I run the test during the day for debugging purposes and I want to omit any of this daytime data by fetching only the results from 1am to 6am.

How can I refine the results further with a criteria that says "For each day of the last 21 days, between the hours of 1am and 6am, fetch the results"?

Tags (3)
0 Karma
1 Solution

dmarling
Builder

If your goal is to limit the base data to only look from 1-6 am each of the past 21 days this will accomplish that:

index=myindex date_hour>=1 date_hour<7 (host=host1 OR host=host2 event=“event1” OR event=“event2”) earliest=-21d latest=now 
| eval date=strftime(_time, "%Y-%m-%d") 
| eval rounded_mean = round(‘my data’, 2) 
| chart perc50(rounded_mean) over name by date limit=0
If this comment/answer was helpful, please up vote it. Thank you.

View solution in original post

lmonahan
Path Finder

Thank you @dmarling ! Yes I think your solution will work. In my data I am not receiving data_hour so I cannot use it.

My workaround for now is similar: use eval to get the hour and perform a subsearch based on it.

Here is my query:

index=myindex host=host1 OR host=host2 event=“event1” OR event=“event2” earliest=-21d latest=now
| eval hour=strftime(_time, "%H")
| eval date=strftime(_time, "%Y-%m-%d")
| search hour<=3
| eval rounded_mean = round(‘my data’, 2)
| chart perc50(rounded_mean) over name by date limit=0

0 Karma

dmarling
Builder

Glad that works. You can also use a where command to trim out a line:

index=myindex host=host1 OR host=host2 event=“event1” OR event=“event2” earliest=-21d latest=now
| eval date=strftime(_time, "%Y-%m-%d")
| where tonumber(strftime(_time, "%H"))<=3
| eval rounded_mean = round(‘my data’, 2)
| chart perc50(rounded_mean) over name by date limit=0
If this comment/answer was helpful, please up vote it. Thank you.
0 Karma

dmarling
Builder

If your goal is to limit the base data to only look from 1-6 am each of the past 21 days this will accomplish that:

index=myindex date_hour>=1 date_hour<7 (host=host1 OR host=host2 event=“event1” OR event=“event2”) earliest=-21d latest=now 
| eval date=strftime(_time, "%Y-%m-%d") 
| eval rounded_mean = round(‘my data’, 2) 
| chart perc50(rounded_mean) over name by date limit=0
If this comment/answer was helpful, please up vote it. Thank you.
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...