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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...