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

[ashutoshwalke]

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.

[ashutoshwalke]

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")

[ITWhisperer]

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

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

[ashutoshwalke]

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

[ITWhisperer · Accepted solution]

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")

[ashutoshwalke]

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-31	2021-04-02
2021-03-31	2021-03-23
2021-03-31	2021-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

[ITWhisperer]

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