Splunk Search

How to get the last Sunday of each month?

ettaly
Engager

Hello,

Can I know how to get the last Sunday of each month? For example, 31st is last Sunday of Jan 2021, 28th is last Sunday of Feb 2021, 28th is last Sunday of March 2021... Thank you.

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

Here's another one that calculates sundays from 1971 to 2030

It simply gets the first of the month then finds the previous Sunday for each month

| makeresults 
| fields - _time
| eval month=mvrange(1,721)
| mvexpand month
| eval month=month % 12 + 1
| streamstats c as group
| eval year=(floor((group / 12)) + 1971)
| eval first=strptime(year."-".month."-01", "%Y-%-m-%d")-1
| eval sunday=relative_time(first,"@w0")
| fieldformat sunday = strftime(sunday,"%A %Y-%m-%d")
| eval date=strftime(sunday, "%b %Y")
| table date sunday

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

Here's another one that calculates sundays from 1971 to 2030

It simply gets the first of the month then finds the previous Sunday for each month

| makeresults 
| fields - _time
| eval month=mvrange(1,721)
| mvexpand month
| eval month=month % 12 + 1
| streamstats c as group
| eval year=(floor((group / 12)) + 1971)
| eval first=strptime(year."-".month."-01", "%Y-%-m-%d")-1
| eval sunday=relative_time(first,"@w0")
| fieldformat sunday = strftime(sunday,"%A %Y-%m-%d")
| eval date=strftime(sunday, "%b %Y")
| table date sunday

bnikhil0584
Explorer

Hi,

I've a similar use case but to get the second and last Saturday's. I tried to use your search but couldn't get there. Can you please help me with that. TIA

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Assuming you have some events covering these times, you can try this sort of thing

| where date_wday = "sunday" | stats max(date_mday) as lastSunday by date_month

pclewis
Explorer

I am sure there is an elegant mathematical way to do it but generally I just solve things like this the same way I'd do it with a spreadsheet..   Brute force...

| makeresults 
| rename REM as "decide your start date and end date" 
| eval start=strptime("2020-06-12","%Y-%m-%d") 
| eval end=strptime("2021-06-12","%Y-%m-%d") 
| rename REM as "%w	Weekday as a decimal number. (0 = Sunday, ..., 6 = Saturday)" 
| rename REM as "figure out date of the sunday prior to start date" 
| eval date_start_sunday=start- 86400*tonumber(strftime(start,"%w")) 
| rename REM as "figure out days between start and end - round up/down or whatever you like" 
| eval date_end_sunday=end +(7- 86400*tonumber(strftime(end,"%w")) ) 
| rename REM AS "use mvrange to make the list of sundays"
| eval date_sunday=mvrange(date_start_sunday,date_end_sunday,7*86400) 
| mvexpand date_sunday

| eval year=strftime(date_sunday,"%Y"), month=strftime(date_sunday,"%m"), day=strftime(date_sunday,"%d")
| sort +date_sunday
| stats  max(day) as maxday by year month
| eval date_sunday=strptime(year . "-" . month . "-" . maxday,"%Y-%m-%d")

| fieldformat date_sunday = strftime(date_sunday,"%A %Y-%m-%d")
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...