Splunk Search

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
New Member

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>
]

0 Karma
1 Solution

woodcock
Esteemed Legend

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 >

View solution in original post

0 Karma

woodcock
Esteemed Legend

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 >

0 Karma

alfiyashaikh
New Member

****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

0 Karma

damien_chillet
Builder

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

alfiyashaikh
New Member

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.

0 Karma

damien_chillet
Builder

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)

0 Karma

alfiyashaikh
New Member

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

I am still new to splunk 🙂

0 Karma

damien_chillet
Builder

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.

0 Karma

alfiyashaikh
New Member

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.

0 Karma

damien_chillet
Builder

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...