How get data for yesterday, last month on that day, and last year on that day

[kuul13]

I want o create a dashboard for my API response times and TPS for comparison between multiple timeframes. When ever someone open the dashboard, I want to show a bar chart for yesterday, last month on the same day, and last year on the same day.

Example: 11/24/2025 (yesterday), 10/24/2025 (last month), and 11/24/2024 (last year)

I tried using the following but last month and year doesn't give the correct datetime range:

• Yesterday: earliest=`-1d@d latest=@d`. << 11/24/2025
• Same Day Last Month: earliest=`-30d@d latest=-29d@d`. << 11/26/2025
• Same Day Last Year: earliest=`-365d@d latest=-364d@d`.<< 11/25/2025

How can I add an accurate date filter based on when this report is run, 28 day month, 29 day month, 30 day month or 31 day month. I want time range to be more dynamic and automatic.

[gcusello]

Hi @kuul13 ,

you have two solutions that depend on how many data you have:

1)

run three subsearches adding results to the same search using append:

<your_search> earliest=-d@d latest=@d
| table <your_fields>
| append [ search 
     <your_search> earliest=-30d@d latest=-29d@d
     | table <your_fields> ]
| append [ search 
     <your_search> earliest=-365d@d latest=-364d@d
     | table <your_fields> ]

I used table, but you can apply every output you like (e.g. timestamp, stats, etc...), obviously using the same in all subsearches.

2)

classify events using eval:

<your_search> earliest=-365d@d latest=@d
| eval period=case(
     _time>now()-86400,"yesterday",
     _time>now()-30*86400 AND _time>now()-29*86400,"last_month",
     _time>now()-365*86400 AND _time>now()-364*86400,"last_year")
| table <your_fields> period

I prefer the first solution that's faster, especially if you have many events.

Ciao.

Giuseppe

[livehybrid]

Hi @kuul13 

Ive been working on the previous month search, you could use a subsearch to set the earliest/latest like below, this works out the number of days in the previous month and sets to the last day of the month if the current day is 31 but previous month only have 30 days (for example).

index=main [| makeresults 
| eval daysInLastMonth=tonumber(strftime(relative_time(_time,"@mon-1d"),"%d"))
| eval earliest="-1mon@mon+".(min(daysInLastMonth+1,tonumber(strftime(_time,"%d")))-1)*86400, latest="-1mon@mon+".min(daysInLastMonth+1,tonumber(strftime(_time,"%d")))*86400 
| table earliest latest]

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[bowesmana]

You can add this subsearch to any search and it will give you the time range from the time picker and then the same period 1 month earlier and the same period 1 year earlier.

[ 
  | makeresults 
  ``` This gets the search range from the time picker ```
  | addinfo
  ``` Calculate the related periods using relative_time eval statement ```
  | eval last_month_earliest=relative_time(info_min_time, "-1mon"), last_month_latest=relative_time(info_max_time, "-1mon") 
  | eval last_year_earliest =relative_time(info_min_time, "-1y"), last_year_latest =relative_time(info_max_time, "-1y")
  ``` Now construct the search term ```
  | eval search=printf("((earliest>=%d AND latest<%d) OR (earliest>=%d AND latest<%d) OR (earliest>=%d AND latest<%d))", info_min_time, info_max_time, last_month_earliest, last_month_latest, last_year_earliest, last_year_latest)
  | fields search
]

e.g. you could then do

index=bla sourcetype=bla [
  __INSERT_SUBSEARCH_HERE__
]

It simply constructs the search phrase

((earliest>=X AND latest<Y) OR (earliest>=X-1mon AND latest<Y-1mon) OR (earliest>=X-1y AND latest<Y-1y))

which controls the data selected in the search.

[bowesmana]

As an update to this, there is a significant optimisation to the single subsearch, which will cause events to be scanned between the overall earliest and latest time ranges, which may be a significant amount of the data.

The solution is to use a 3 search multisearch, each one focused on it's own time range, which you can do in a similar way the first search will always use the time picker date, so no subsearch needed, but for the other dates...

| multisearch [
  search index=_audit 
]
[
  ``` Search the last month ```
  search index=_audit [ 
    | makeresults | addinfo | eval earliest=relative_time(info_min_time, "-1mon"), latest=relative_time(info_max_time, "-1mon") | fields earliest latest
  ]
]
[
  ``` Search the last year ```
  search index=_audit [ 
    | makeresults | addinfo | eval earliest=relative_time(info_min_time, "-1y"), latest=relative_time(info_max_time, "-1y") | fields earliest latest
  ]
]

| bin _time span=1d 
| stats count by _time

Hope this helps.

[bowesmana]

And this is what that would look like

[livehybrid]

Hi @kuul13 

I havent worked out the same day on previous month using earliest/latest, however for same day on previous year you can do this:

index=yourIndex earliest=@d-31536000 latest=@d-31449600 

Where the 31536000 and 31449600 are the number of seconds in 365/364 days (you cant do 86400*365 for example).

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[kuul13]

I didn't consider that but you are right that the query needs to consider the edge cases. I am not an expert in Splunk, how do I write those fitlers? 

[PickleRick]

I'd go for  strptime/strftime manipulation.

Did you consider border cases however? How about days like Dec 31st? What would be "last month, same day"? There is no Nov 31st. How about "last year" from Feb 29th on a leap year?