Convert field values (e.g 15/12) to date format understandable to SPLUNK , and then check if the date is older than 5 working.

[alfiyashaikh]

I have field with values such as "06/12", "13/01", "20/05" i/e human readable dd/mm.
I dont know weather splunk understands the format is date format dd/mm or it takes it as a string.

I have to perform a check on the date , if it is older thn5 working days
(eg if today is Thursday 19th, then anything older than Thursday 12th),

I have tried few related to the following , so far it just gave me blank "strf_time" table

[
index="extractt"
| fieldformat strf_time=strftime(VTN_field_2, "%d/%m/%y")

comment("fieldformat strf_time = strftime(VTN_field_2, "%d/%m")")
| table VTN_field_2, strf_time>
]

[woodcock · Accepted solution]

Like this:

| eval strp_time=strptime(VTN_field_2, "%d/%m")
| where strp_time<(now() - (7*24*60*60))

Or something similar on the last line.

---

updated to use strptime() instead of strftime() and < instead of >

[alfiyashaikh]

****index="extractt"
| eval strf_time=strftime(VTN_field_2, "%d/%m")

|table VTN_field_2, strf_time

---

I tried this earlier too n even now , when I want to check what values I am getting in strftime field, the table displays blank
I can see values in VTN_field_2 column but nothing in strf_timje column

[damien_chillet]

It's strptime that you need to use, not strftime

[alfiyashaikh]

Hi woodcock and Damien

yes, strptime worked

| eval strf_time=strptime(VTN_field_2, "%d/%m")

I want to check if the field date is older than 5 working days from current date
(eg if today is Thursday 19th, then anything older than Thursday 12th)
then I should enter feedback as" resolution need chase".

so I used this code:

| eval strf_time=strptime(VTN_field_2, "%d/%m")
| where strf_time>(now() - (7*24*60*60)) | eval Feedback=" resolution need chase"

I am getting events filtered, but I am not able to evaluate

please pardon me , I am not able to get what calculation is suggested by you woodcock , can you please explain

| where strf_time>(now() - (7*24*60*60))

how does this gives fields that are older than last 5 working days.

[damien_chillet]

It should be lesser than (<) rather than greater than (>).

| where strf_time < (now() - (7*24*60*60))

7*24*60*60 is 7 normal days in second (or 5 business days assuming it is a normal week with weekend as only non working days)

[alfiyashaikh]

okay will check , but still can you explain what the calculation performed is (7*24*60*60)

I am still new to splunk 🙂

[damien_chillet]

I've edited my previous comment.

Do you know about UNIX epoch timestamp?
It's the number of seconds since 1 January 1970.

now() returns current time epoch timestamp - let's say 08 Dec 13:00
(7*24*60*60) is 7 days in seconds (7 days * 24 hours * 60 minutes * 60 seconds)

now()-(7*24*60*60) will be epoch timestamp for 01 Dec 13:00.

So you get events with date older that 01 Dec 13:00.

[alfiyashaikh]

Okay, understood thank you . Now what about the "working days".. like if the date in my field represents a Sunday I have to check last 5 working days that will be Monday .Then in this case this won't be applicable .because this will give me the date of past Sunday.

[damien_chillet]

Dealing with working days is not straight forward..

You can try:

| eval test_time=strptime(VTN_field_2,"%d/%m")
| eval Date=mvrange(test_time,now(),86400)
| convert ctime(Date) timeformat="%+" 
| eval NoOfBusinessDays=mvcount(mvfilter(NOT match(Date,"(Sun|Sat).*")))
| where NoOfBusinessDays > 5

This is inspired from @somesoni2 's answer to this question https://answers.splunk.com/answers/186662/i-am-try-to-to-find-the-number-of-business-days-be.html