Help with Subsearch using different time range than main search

[cchimento]

Hello

I am trying to make a subsearch that will search events from a different time period than the original (outer) search.

I have a search that will search for events (we will refer to them as "calls") for the last 30 days. In that I have set it up so that it will produce a high water mark for these calls. The issue is that when the 30 day rolling period in fact rolls beyond the event with the HWM.. the value changes. Thus making it not a true high water mark, just only for the time period that was selected.

What I would like is to have my normal search for a 30 day rolling period.. but the HWM value should pull from the last year's worth of calls or beyond.

Here is my original search: (-30d)

index=ast sourcetype=poc_agi_logs agi_dnid=* grp=* 
| timechart count as "Total Calls" 
| eventstats allnum=true max("Total Calls") AS "High Water Mark"

and then here is what I have tried. I don't get an error, but rather it doesn't return any results.

index=ast sourcetype=poc_agi_logs agi_dnid=* grp=* 
[search index=ast sourcetype=poc_agi_logs agi_dnid=* grp=* earliest=-1y latest=-1d
     |stats count as "HWM" 
     |eventstats allnum=true max("HWM") AS "High Water Mark"]
|timechart count AS "Total Calls" 
|fields + "High Water Mark"

I also tried..

 index=ast sourcetype=poc_agi_logs agi_dnid=* grp=* 
 [search index=ast sourcetype=poc_agi_logs agi_dnid=* grp=* earliest=-1y latest=-1d|stats count as "HWM"] 
 |timechart count AS "Total Calls" |eventstats allnum=true max("HWM") AS "High Water Mark"

So I am not sure what I might have out of order. I want the HWM to retain the values for the last year. while only displaying 30 days in the chart with the rest of the info. I would think that something like a High water mark would be fairly easy to perform within Splunk, but this was the best i could do.

Bonus points, if someone could help me chart the value of the HWM over time as if to keep a running record of it.

Thanks in advance!

[maciep]

I'm not quite sure what the hmw is supposed to represent, because it looks like it's just a count of events over the past year (up until yesterday). Is that what you want it to be? The count in your 30-day timechart will be a count by day (or whatever span Splunk uses). So is the goal to have the hwm use the same span as the 30-day timechart (so about 12 different count values). And from those values you want the max?

In general though, I don't think you're using subsearch way you want. If you look at the job inspector, it's likely just adding another condition to your base search for a "High Water Wark" field, which I don't think exists in your dataset?. In other words, that subsearch isn't adding to data to your search results, it's trying to filter the initial results.

Assuming I understand what you're looking for, something like this way work a little better, but still feels a bit "out of the way", so there's probably a better approach yet.

    index=ast sourcetype=poc_agi_logs agi_dnid=* grp=* 
    | timechart count as "Total Calls"
    | appendcols
    [
        search index=ast sourcetype=poc_agi_logs agi_dnid=* grp=* earliest=-1y latest=-1d
        | timechart span=1d count
        | stats max(count) as "High Water Mark"
    ]
   | filldown "High Water Mark"

So build your timechart, then append a column that represents the hwm from last year. But since that just adds it to the first event, fill that down through the rest of the events.

[cchimento]

Hi Thank you for the reply, but this also did not work. In fact, it did not produce any events or results after running.

The HWM (High Water Mark) is a Max Value over a time period. I would like the HWM to accommodate values older (-1y) than the selected time range for the normal call counts (time picker=-30d).

Does this make sense? In other words, I don't want the HWM to change values and find a new HWM once the 30 day period rolls beyond the previous high value. If the Max value was say 6 months ago.. I would like to see a timechart for the last 30 days while still showing the true HWM value recorded 6 months ago.

[maciep]

Hmm, ok. I ran this same search against a simple _internal data set and did return results. Not sure what is different with your data? Did you run it over 30 days like you wanted? Can you run either the outer search or subsearch on their own?

For HWM, I understand the concept. But a max value of what over which period? It seems like you're just comparing counts of events (calls) essentially. But your timechart over 30 days will be counts per day. The subsearch in your original post is counting events over an entire year. The comparison doesn't make sense. I understand if your hwm mark intended to represent the count during the day in the past year that had the most calls, but I can't figure out if that's what you're actually asking....but that's what I was trying to answer.

[cchimento]

I'm not sure how I am not conveying my thoughts to you here. You're correct in thinking of what I want. But what about the "comparison" are you missing? Not trying to be rude, I understand this is text. But I'm not trying to compare anything. I would like to display a line chart for the last 30 days of call totals, while displaying the HWM (of daily call totals) over the last year. NOT just the last 30 days as previously configured. If that is considered a comparison, then that is what I'm after. It is querying count of events in both aspects. yes.

Below is the output of the chart as it was configured originally. My goal is to have the HWM line display the max value for count of calls for the year while only still displaying the 30 day rolling period for the daily total counts. So when the HWM in this instance on June 14th of 1304 calls passes on July 15th.. the HWM will be a different value. I don't want this.

[maciep]

Ok, I think we're on the same page. This is the subsearch above that was throwing me off, because it just gets a total count of events over the past year.

[search index=ast sourcetype=poc_agi_logs agi_dnid=* grp=* earliest=-1y latest=-1d
      |stats count as "HWM" 
      |eventstats allnum=true max("HWM") AS "High Water Mark"]

That said, I thought the search in my answer was doing what you wanted. I don't know why it's not returning any results. Does the subsearch return what looks to be a correct hwm at least? I think that should get a counts for every day over the past year and the grab the max value.

[cchimento]

Ok - so i tried your query again, and typing it in manually got it going. Must have cut something out before. 🙂

So this does run but is not returning the correct results. In an effort to make this run faster I changed this to search 30 days worth of events for the HWM while keeping the main search at last 7 days.

[maciep]

Ok, looks like we're getting closer, maybe? If you run the subsearch on its own, without the call to stats (so just the timechart), do those counts look right? And if so, does adding the stats max command back in work correctly? I feel like I'm missing something silly here...

[sundareshr]

You may be better off by not using subsearch. See if this gives you what you're looking for...

index=ast sourcetype=poc_agi_logs agi_dnid=* grp=* earliest=-1y latest=-1d | eval when=if(_time>relative_time(now(), "-30d@d"), "Current", "Previous") | eventstats allnum=true max("HWM") AS "HighWaterMark" | timechart span=1d count(eval(when="Current")) as "Total Calls" max(HighWaterMark) as "High Water Mark"

[cchimento]

Thank you - but I don't think that is it. This produced a very slow search and throughout that time, the HWM remained at zero. I changed the search for a much shorter time period and it was still slower than anything. I ultimately canceled it.

[gfreitas]

Can you provide us the result of the subsearch??

[search index=ast sourcetype=poc_agi_logs agi_dnid=* grp=* earliest=-1y latest=-1d
      |stats count as "HWM" 
      |eventstats allnum=true max("HWM") AS "High Water Mark"]

[cchimento]

Functions as expected.