Splunk Search

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

tmaltizo
Path Finder

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

Labels (2)
Tags (2)
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

Inayath_khan
Path Finder

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 alt text

0 Karma

somesoni2
Revered Legend

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
Super Champion

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?

0 Karma

tmaltizo
Path Finder

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!

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

What Is Splunk? Here’s What You Can Do with Splunk

Hey Splunk Community, we know you know Splunk. You likely leverage its unparalleled ability to ingest, index, ...

Level Up Your .conf25: Splunk Arcade Comes to Boston

With .conf25 right around the corner in Boston, there’s a lot to look forward to — inspiring keynotes, ...

Manual Instrumentation with Splunk Observability Cloud: How to Instrument Frontend ...

Although it might seem daunting, as we’ve seen in this series, manual instrumentation can be straightforward ...