Splunk Search

How to get last weekday of last month and check logs for an date field in the logs having that value

ashutoshwalke
Explorer

I have something that runs every day but i need to see it only for previous EOM which is also a weekday

I have a field as date =2021-03-31 in the logs

I am not sure how can I get the previous EOM(weekday) and compare it with my date field value.

I have already tried answer given here but has not helped.

https://community.splunk.com/t5/Archive/Function-To-Return-Last-Weekday/m-p/172413#M25201

Any help appreciated.

Thanks in advance.

Labels (1)
0 Karma
1 Solution

ITWhisperer
Ultra Champion

I would disagree - it is useful if you know what to do with it 😀

| makeresults | eval last_day=relative_time(now(),"-1mon@mon-1d")
| eval dotw=tonumber(strftime(last_day,"%w"))
| eval adjust=case(dotw=0,2,dotw=6,1,1==1,0)
| eval last_weekday=last_day-(adjust * 24 * 60 * 60)
| fieldformat last_weekday=strftime(last_weekday,"%a %Y-%m-%d")

View solution in original post

ashutoshwalke
Explorer

I was able to do it after I change fieldformat to eval here

| fieldformat last_weekday=strftime(last_weekday,"%Y-%m-%d")

TO 

| eval last_weekday=strftime(last_weekday,"%Y-%m-%d")

0 Karma

ITWhisperer
Ultra Champion
| makeresults | eval _raw=strftime(relative_time(now(),"@mon-1d"),"%w")

1-5 are weekdays, 0 is Sunday, 6 is Saturday

0 Karma

ashutoshwalke
Explorer

Hi, Thank you for your response.

From your query I get the day number and is not helpful.

I need the date of last weekday in previous month, say 2021-03-31 and then I need to check in my logs looking in the date field if date=2021-03-31 is present or not. If yes , I use the logs.

Logically may be it could be something like below, but I am not getting the correct syntax or way of doing it,

| makeresults
| eval current_month_last_day=relative_time(now(),"@mon-1d") | fieldformat current_month_last_day=strftime(current_month_last_day,"%Y-%m-%d")| eval day_value = case(date_wday=="saturday", "1d", date_wday="sunday", "2d") | eval current_month_last_day=relative_time(current_month_last_day-day_value) | table current_month_last_day

Explanation

------------------------------------------------------------------------------------------------------------------------------------

1. | makeresults
| eval current_month_last_day=relative_time(now(),"@mon-1d") | fieldformat current_month_last_day=strftime(current_month_last_day,"%Y-%m-%d") --- with this is get the last date of previous month

2. | eval day_value = case(date_wday=="saturday", "1d", date_wday="sunday", "2d") | eval current_month_last_day=relative_time(current_month_last_day-day_value)--- here i try to check if it is not a weekend, if it happens to be then minus 1 day or 2 day from the date calculated in step 1.

------------------------------------------------------------------------------------------------------------------------------------

Please help.

Thanks

 

0 Karma

ITWhisperer
Ultra Champion

I would disagree - it is useful if you know what to do with it 😀

| makeresults | eval last_day=relative_time(now(),"-1mon@mon-1d")
| eval dotw=tonumber(strftime(last_day,"%w"))
| eval adjust=case(dotw=0,2,dotw=6,1,1==1,0)
| eval last_weekday=last_day-(adjust * 24 * 60 * 60)
| fieldformat last_weekday=strftime(last_weekday,"%a %Y-%m-%d")

View solution in original post

ashutoshwalke
Explorer

Thanks a lot, that worked like a charm 🙂

Just one question, I am unable to compare it with my date field in the log using a WHERE clause. I could see both the dates are in same format.

index=* | eval last_day=relative_time(now(),"+0mon@mon-1d")
| eval dotw=tonumber(strftime(last_day,"%w"))
| eval adjust=case(dotw=0,2,dotw=6,1,1==1,0)
| eval last_weekday=last_day-(adjust * 24 * 60 * 60)
| fieldformat last_weekday=strftime(last_weekday,"%Y-%m-%d") | table last_weekday date

When I run above query it does gives me a table as below, also when i search just using date=2021-03-31 it does gives me the results.

last_weekday           date

2021-03-312021-04-02
2021-03-312021-03-23
2021-03-312021-03-23

 

I also tried doing below to convert my date field just in case,

| fieldformat date=strftime(date,"%Y-%m-%d")

but that didn't help.

Can you advise ?

Thanks

0 Karma

ITWhisperer
Ultra Champion

Fieldformat doesn't change the value of the field or change it from an epoch time to a string for example. I think what you want is to keep events where the date is the last weekday of the month?

index=* | eval date=strptime(date,"%Y-%m-%d")
| eval last_day=relative_time(date,"+1mon@mon-1d")
| eval dotw=tonumber(strftime(last_day,"%w"))
| eval adjust=case(dotw=0,2,dotw=6,1,1==1,0)
| eval last_weekday=last_day-(adjust * 24 * 60 * 60)
| where date=last_weekday

This assumes date in your events is a string that needs to be parsed into an epoch date. If it is already an epoch date, then you don't need the first eval

Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!