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
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...