Splunk Search

How to search the number of days a request was open for?

Contributor

I have three fields "Request Date" , "Remote Access Date Fulfilled" and "R_Drive Date Fulfilled". I need to find how many days open a request was but, i need to use the latest date between "Remote Access Date Fulfilled" and "R_Drive Date Fulfilled" since it could be one or the other. the dates are in month/day/year "2/26/2015" with no time. I'm not sure how to build out this search or what eval statements to use.

Tags (4)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

Let's say the fields are called "ra_date_fulfilled", "rdrive_date_fulfilled", and "request_date".

Important things to know!
1) that depending on your timestamp extraction, the request date may actually already be the "_time" field.

2) Even though "_time" often looks like a formatted string, it is actually an epochtime number (seconds since 1/1/1970), and it's customary appearance in the splunk UI is the UI special casing the field's handling, trying to help you.
3) field names in Splunk can have spaces in them, but in some parts of the platform, those spaces kind of cannot easily go. The eval command is one of those places, so I recommend renaming the fields here, either inline in the search, or better yet, just change the field name you're using in any configured field extractions.

The answer:
| eval ra_date_fulfilled=strptime(ra_date_fulfilled, "%m/%d/%Y") | eval rdrive_date_fulfilled=strptime(rdrive_date_fulfilled, "%m/%d/%Y") | eval request_date=strptime(request_date, "%m/%d/%Y") | eval fulfill_date=min(ra_date_fulfilled,rdrive_date_fulfilled) | eval duration=fulfill_date - request_date

So that will give you your duration field on all the raw events.

Let me know if request_date is actually the "_time" field, and I will update my answer for you.

View solution in original post

SplunkTrust
SplunkTrust

Let's say the fields are called "ra_date_fulfilled", "rdrive_date_fulfilled", and "request_date".

Important things to know!
1) that depending on your timestamp extraction, the request date may actually already be the "_time" field.

2) Even though "_time" often looks like a formatted string, it is actually an epochtime number (seconds since 1/1/1970), and it's customary appearance in the splunk UI is the UI special casing the field's handling, trying to help you.
3) field names in Splunk can have spaces in them, but in some parts of the platform, those spaces kind of cannot easily go. The eval command is one of those places, so I recommend renaming the fields here, either inline in the search, or better yet, just change the field name you're using in any configured field extractions.

The answer:
| eval ra_date_fulfilled=strptime(ra_date_fulfilled, "%m/%d/%Y") | eval rdrive_date_fulfilled=strptime(rdrive_date_fulfilled, "%m/%d/%Y") | eval request_date=strptime(request_date, "%m/%d/%Y") | eval fulfill_date=min(ra_date_fulfilled,rdrive_date_fulfilled) | eval duration=fulfill_date - request_date

So that will give you your duration field on all the raw events.

Let me know if request_date is actually the "_time" field, and I will update my answer for you.

View solution in original post

Contributor

This is awesome but, i have a few questions,

1.I need to use the latest date between "Remote Access Date Fulfilled" and "R_Drive Date Fulfilled" since it could be one or the other but it has to be the latest date, "Remote Access Date Fulfilled" and "R_Drive Date Fulfilled" could be in the same log for example: "Remote Access Date Fulfilled" may have been completed on 2/25/15 and "R_Drive Date Fulfilled" was done on 2/28/15 meaning i would need to use "R_Drive Date Fulfilled" and find how many day this was open for by the "Request Date" field.

  1. request_date is not the "_time" field. How would i change results showing like this 1424926800.000000 to a human readable formant?
0 Karma

SplunkTrust
SplunkTrust

Here I've changed it to take the later of the two fulfill dates, rather than the earlier, and also to create new fields to hold the epochtime values, so you can still have the old string-formatted date fields for display. Lastly, I've formatted the duration field in two ways that are probably more useful than just the giant number of seconds. Cheers.

| eval ra_date_fulfilled_epoch=strptime(ra_date_fulfilled, "%m/%d/%Y") | eval rdrive_date_fulfilled_epoch=strptime(rdrive_date_fulfilled, "%m/%d/%Y") | eval request_date_epoch=strptime(request_date, "%m/%d/%Y") | eval fulfill_date_epoch=max(ra_date_fulfilled_epoch,rdrive_date_fulfilled_epoch) | eval duration=fulfill_date_epoch  -request_date_epoch | eval durationInDays=duration/86400 | eval fancyDurationString=duration | convert sec2dur(fancyDurationString)

And in general if you want to take an epochtime value and turn it into a string formatted date, you would use
| eval someDateString=strftime(someEpochTimeField,"%m/%d/%Y")

Contributor

THANKS!!!!

host=Access_Admin |Rename "Remote Access Date Fulfilled" AS "ra_date_fulfilled" | Rename"R_Drive Date Fulfilled" AS "rdrive_date_fulfilled" | Rename "Request Date" AS "request_date" | eval ra_date_fulfilled_epoch=strptime(ra_date_fulfilled, "%m/%d/%Y") | eval rdrive_date_fulfilled_epoch=strptime(rdrive_date_fulfilled, "%m/%d/%Y") | eval request_date_epoch=strptime(request_date, "%m/%d/%Y") | eval fulfill_date_epoch=max(ra_date_fulfilled_epoch,rdrive_date_fulfilled_epoch) | eval duration=fulfill_date_epoch -request_date_epoch | eval "days open"=strftime(duration,"%d") | table ra_date_fulfilled rdrive_date_fulfilled request_date "days open"

0 Karma

Contributor

when the ra_date_fulfilled is the same as the request_date it defaults it to 31 days. is there a way to get to say 1 day?

0 Karma

SplunkTrust
SplunkTrust

What you're doing here isn't right - eval "days open"=strftime(duration,"%d") That says take the duration (in seconds), treat it as an epochtime value (number of seconds elapsed since 1/1/1970 in GMT), and then grab the day of month (%d) at that time, which is probably December 31st in localtime, hence the "31". Instead look back to my previous comment - I had two ways of giving you duration in days or elapsed time and you should use one of those.

Contributor

sorry, you are correct.

thanks for the help!

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!