topic Re: How to get last weekday of last month and check logs for an date field in the logs having that value in Splunk Search

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

ashutoshwalke — Mon, 05 Apr 2021 12:36:03 GMT

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.

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

ITWhisperer — Mon, 05 Apr 2021 13:17:56 GMT

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

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

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

ashutoshwalke — Mon, 05 Apr 2021 13:58:44 GMT

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

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

ITWhisperer — Mon, 05 Apr 2021 15:03:37 GMT

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

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

ashutoshwalke — Mon, 05 Apr 2021 17:17:53 GMT

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

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

ashutoshwalke — Mon, 05 Apr 2021 17:38:46 GMT

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

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

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

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

ITWhisperer — Mon, 05 Apr 2021 17:39:05 GMT

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?

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