topic Using multiple time range on the same index to return different row values in Splunk Search

Using multiple time range on the same index to return different row values

egonstep — Fri, 08 May 2020 12:06:29 GMT

Hello all,

I have a report that searches for differents time range like Year to now, Month to now, Last 5 days and last 24 hrs.

The current search is:

search index
| stats sum(FAIL) as "Failures", sum(PASS) as "Passed", sum(TOTAL) as "Total"
| eval "Date" = "Last 24 hrs"
| append [ search "same search index" earliest=-5d@d latest=now()
| stats sum(FAIL) as "Failures", sum(PASS) as "Passed", sum(TOTAL) as "Total"
| eval "Date" = "Last 5 days"]
| append [ search "same search index" earliest=-0mon@mon latest=now()
| stats sum(FAIL) as "Failures", sum(PASS) as "Passed", sum(TOTAL) as "Total"
| eval "Date" = "Month to now"]
| append [ search "same search index" earliest=-0year@year latest=now()
| stats sum(FAIL) as "Failures", sum(PASS) as "Passed", sum(TOTAL) as "Total", earliest(_time) as "Date"
| convert timeformat="%m/%d/%Y" ctime("Date") as "Date"
| eval "Date" = "Year to now - ".'Date']
| table "Date", "Failures", "Passed", "Total"

The final result is a table with 4 rows in it. Containing Last 24 hours, Last 5 days, Month to now and Year to now results.

So the problem that I'm currently facing is sometimes the Year to now results, returns a different Date like 01/27/2020. It's supposed to be the first day of the current year.

The Job Inspect tells me that the append command is consuming a lot of time to be completed.

Is it possible to do the longest search (Year to now) and have multiples stats commands by different time range to get the final result or a way to improve this search?

Thanks for all your help.

Re: Using multiple time range on the same index to return different row values

harshpatel — Fri, 08 May 2020 13:43:40 GMT

Hi @egonstep,

I created this hack earlier which might be helpful here. So It gives you behavior where you have multiple searches as your basesearch and then you combine all those search results into one search. Which is as follows:

Step 1: Define your searches as follows:

<search>
    <query>index="X" | stats count  | eval "search name"="search1"</query>
    <earliest>-24h</earliest>
    <latest>now</latest>
    <done>
      <set token="search1_sid">$job.sid$</set>
    </done>
  </search>
  <search>
    <query>index="Y" | stats count | eval "search name"="search2"</query>
    <earliest>-7d</earliest>
    <latest>now</latest>
    <done>
      <set token="search2_sid">$job.sid$</set>
    </done>
  </search>

Step 2: Now the sids will set when the searches are finished execution. Use them in the main search with loadjob command as follows:

| loadjob $search1_sid$
| append 
    [| loadjob $search2_sid$ ]

As the searches ran parallel (having separate job for each search) and the results in the main search are fetched directly from the job sid, this way you can combine results of long-running searches without affecting them by append as before.

Edit: You can also split the search across multiple report searches.

Hope this was helpful.

Thanks,
Harsh

Re: Using multiple time range on the same index to return different row values

egonstep — Tue, 12 May 2020 11:59:04 GMT

Hey harshpatel, thanks for your answer.

But I have a question, this is to create a new dashboard to be sent as pdf, right?

Re: Using multiple time range on the same index to return different row values

harshpatel — Tue, 12 May 2020 16:40:36 GMT

@egonstep, Yes it requires creating a dashboard. Also I've added an edit saying you can create multiple savedsearch as well like this. You can specify savedsearch name to loadjob command as well in your report search. For documentation (https://docs.splunk.com/Documentation/Splunk/8.0.3/SearchReference/Loadjob).

Re: Using multiple time range on the same index to return different row values

egonstep — Tue, 12 May 2020 20:07:48 GMT

@harshpatel I see, thanks for the help, I'll try to create this way to see if everything works just fine.

Re: Using multiple time range on the same index to return different row values

BenTreeser — Thu, 01 Sep 2022 06:43:08 GMT

@harshpatel could you provide the full dashboard code example? I was not able to successfully use this approach