Splunk Search

YOY Analysis showing up until today's date

hollybross1219
Path Finder

Hi there!

I created a hacky Splunk query for some YOY analysis I'm doing. I was wondering if there was a way to halt data from loading from the previous year up until today's date.

For example, today is 3/12. I'd like data from previous year and this year to show up up until 3/12. The way my query (and time range selector) is now loads all data from previous year (I've attached image of what currently loads). The next day, the "end date" will update to 3/13 and I'd want my previous year data to only reach that date ceiling.

Here's the query I'm working with:

((index=wsi_tax_summary sourcetype=stash capability=109* tax_year=2019 ein=* earliest=1578096000 latest=now()) OR (index=summary_dac_tax partnerId!=*Test* tax_year=2018 capability=*109* tax_year=2018 earliest=1546560000 latest=1556668800)) (intuit_offeringid=Intuit.platform.turbotaxipad.turbotaxmac OR intuit_offeringid=Intuit.platform.turbotaxwindows OR intuit_offeringid=Intuit.tax.ctg.ice.109ximportwidget) error_msg_host=SUCCESS partnerId!=*test* partnerId=* 
| eval Date=strftime(_time,"%m-%d") 
| chart dc(intuit_tid) by Date tax_year 
| rename "2018" as "TY18", "2019" as "TY19" 
| sort by Date 
| streamstats sum(TY18) as TY18 sum(TY19) as TY19

alt text

0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

You can fix this in your base search. You currently have:

((index=wsi_tax_summary sourcetype=stash capability=109* tax_year=2019 ein=* earliest=1578096000 latest=now()) OR (index=summary_dac_tax partnerId!=*Test* tax_year=2018 capability=*109* tax_year=2018 earliest=1546560000 test=1556668800)) (intuit_offeringid=Intuit.platform.turbotaxipad.turbotaxmac OR intuit_offeringid=Intuit.platform.turbotaxwindows OR intuit_offeringid=Intuit.tax.ctg.ice.109ximportwidget) 
  error_msg_host=SUCCESS partnerId!=*test* partnerId=* 
| (evals and stuff...)

All those latest=xxxx and earliest=xxxx clauses can use relative time modifiers. So you can, much as anmolpatel hinted at, do things like this (Trimming out lots of extraneous stuff):

((index=wsi_tax_summary sourcetype=stash capability=109* tax_year=2019 ein=* earliest=-1y@y latest=@y)

I recommend taking a simple search and playing around with those to see their effect, and make sure you understand the difference between @y, -1y@y, and even mixed up things like -1y@w which goes back 1 year, to the closest week to now. (Right now on March 14th 2020, it goes back to March 10th 2019 for me.)

I think with a little use of relative time modifiers, you can achieve what you want.

If you get stuck with anything particular - try it in a simple search, like in your case maybe just one piece at a time, like

index=wsi_tax_summary sourcetype=stash capability=109* tax_year=2019 ein=* earliest=-1y@y latest=@y

Happy Splunking!
-Rich

View solution in original post

0 Karma

Richfez
SplunkTrust
SplunkTrust

You can fix this in your base search. You currently have:

((index=wsi_tax_summary sourcetype=stash capability=109* tax_year=2019 ein=* earliest=1578096000 latest=now()) OR (index=summary_dac_tax partnerId!=*Test* tax_year=2018 capability=*109* tax_year=2018 earliest=1546560000 test=1556668800)) (intuit_offeringid=Intuit.platform.turbotaxipad.turbotaxmac OR intuit_offeringid=Intuit.platform.turbotaxwindows OR intuit_offeringid=Intuit.tax.ctg.ice.109ximportwidget) 
  error_msg_host=SUCCESS partnerId!=*test* partnerId=* 
| (evals and stuff...)

All those latest=xxxx and earliest=xxxx clauses can use relative time modifiers. So you can, much as anmolpatel hinted at, do things like this (Trimming out lots of extraneous stuff):

((index=wsi_tax_summary sourcetype=stash capability=109* tax_year=2019 ein=* earliest=-1y@y latest=@y)

I recommend taking a simple search and playing around with those to see their effect, and make sure you understand the difference between @y, -1y@y, and even mixed up things like -1y@w which goes back 1 year, to the closest week to now. (Right now on March 14th 2020, it goes back to March 10th 2019 for me.)

I think with a little use of relative time modifiers, you can achieve what you want.

If you get stuck with anything particular - try it in a simple search, like in your case maybe just one piece at a time, like

index=wsi_tax_summary sourcetype=stash capability=109* tax_year=2019 ein=* earliest=-1y@y latest=@y

Happy Splunking!
-Rich

0 Karma

anmolpatel
Builder

earliest=-y@d will give you that will give you dd/mm/(yyyy-1)

you can test around further, if you want to go back two years, earliest = -2y@d

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...