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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...