How to format search for year to date (YTD) and syntax for operators

[tmaltizo]

I am currently grabbing a date (openDate, actualenddate) and using strptime in order to reformat it to Splunk's expectations in order to run comparisons with relative_time. The condition I want to search on is all records where the "openDate" is between now and the beginning of the year (I'm currently using "-6mon"), that were implemented (actualenddate) in the past 1 month ("-1mon").

[base search...] |eval openDate= strptime(opened_at, "%Y-%m-%d %H:%M:%S") | where openDate>=relative_time(now(), "-6mon")

I have an additional criteria that filters further on records where the Implementation date (actualenddate) is within the past month.

...| eval actualenddate = strptime(u_actual_impl_end_date, "%Y-%m-%d %H:%M:%S") | where actualenddate >=relative_time(now(),"-1mon")

I saw in another post that @y indicates YTD, but I'm still not getting records beyond 1month.
Please let me know if any additional info is needed to help with this.

Thank you!
Trista

[Inayath_khan]

Hi I also have a similar requirement could you please help me with that.,The requirement is I need to create a trend value that will compare the result in percentage from 01-01-2019 to current date of 2019 (ex, 04-06-2019) and 01-01-2020 to current date of 2020. Attaching the screenshot for reference. Also , I want to create this trend value on a custom field . Please let me know how I can achieve it..Thanks

[somesoni2 · Accepted solution]

For "The condition I want to search on is all records where the "openDate" is between now and the beginning of the year ", try this (YTD)

[base search...] |eval openDate= strptime(opened_at, "%Y-%m-%d %H:%M:%S") | where openDate>=relative_time(now(), "@y")

Not sure if I understood the problem with second filter. You're doing a filter with last one month, so you'd see records where actualenddate values are within ~last 30 days. If you want to include last month to current date (e.g. today is May 16 and you want from Apr 1 to May 16), use -1mon@mon instead of -1mon

[cmerriman]

where are you putting the @y? if i understand correctly, you want it in the openDate where statement?
| where openDate>=relative_time(now(), "@y")

can you give more details on the data you're working with and/or results you're seeing?

[tmaltizo]

Hi @cmerriman!

Yes, I placed "@y" that way. It seems to work ok until I add the second search criteria for actualenddate. After I updated the second filter to -1mon@mon which @somesoni2 suggested, I'm getting from the beginning of the last month, which is what I was looking for!

This is the search now...

index=...
| eval openDate= strptime(opened_at, "%Y-%m-%d %H:%M:%S")
| where openDate>=relative_time(now(),"@y")
| eval actualenddate = strptime(u_actual_impl_end_date, "%Y-%m-%d %H:%M:%S")
| where actualenddate >=relative_time(now(),"-1mon@mon")

Thank you!