Trying to do a search that requires a sub search

[abishop195]

Hey Guys,

I have been stuck on the following for a few days and would love some help

Trying to perform a search of the following type of data:

date name value

What I want to do is create a search that will generate the following for the last 30 days:

data name value min_value max_value

With min_value and max_value being the forth highest/lowest value for the last 30 weeks for that particular day of the week. For simplicity I am assuming their will always be at least 10 weeks worth of data in the index. Their is also the chance for duplicate fields and it would be best to get the last entry indexed for that date.

I know the following search will give me the min_value for the current day:

index=blah sourcetype=some_source name=sample earliest=-210d latest=now | dedup date name value | eval ld=strftime(now(),"%a")
| eval wd=strptime(date, "%Y%m%d") | eval wwd=strftime(wd,"%a") | where ld=wwd | table value | sort -value | tail 4 | sort -value | tail 1

But I have two problems how do I do this for the current date I am looking at and how do I return it as one search.

I have ended up with the following search but it is not working:

index=blah sourcetype=some_source name=sample earliest=-210d latest=now | dedup date name value | eval latest_date=strptime(date, "%Y%m%d") | eval ld=strftime(latest_date,"%a") | join [search index=blah sourcetype=some_source name=sample earliest=-210d latest=date | dedup date name value | eval wd=strptime(date, "%Y%m%d") | eval wwd=strftime(wd,"%a") | where ld=wwd | table value | sort -value | tail 4 | sort -value | tail 1 | rename value as min_value] | join [search index=blah sourcetype=some_source name=sample earliest=-210d latest=date | dedup date name value | eval wd=strptime(date, "%Y%m%d") | eval wwd=strftime(wd,"%a") | where ld=wwd | table value | sort +value | tail 4 | sort +value | tail 1 | rename value as max_value] | table date value min_value max_value

Any help would be appreciated.

-Andrew

[sowings]

Start with stats. Splunk isn't quite like the Un*x command line, despite its pipe-separated search commands. "tail 4 | sort | tail 1" is overkill.

There's an automatic field created for you called date_wday that might help.

index=bla sourcetype=some_source name=sample earliest=-210d latest=now | dedup date name value | stats min(value) AS min_v, max(value) AS max_v by date_wday, name

You could also look into the "bucket" command to reduce the granularity of the _time field (parsed time of the event).

[sideview]

Can I assume you mean that the columns of the results should be "day-of-week name value min_value max_value"? Also when you say you want the values for the lsat 30 weeks for that particular day of the week, do you mean that for every day in the search results you want to compute a trailing 30-week max and a trailing 30-day min? Also what do you mean by "a forth highest/lowest value". Is that a typo and forth should be "fourth", or do you just mean the highest and lowest values.