Comments and answers for "How to search the number of days a request was open for?"https://answers.splunk.com/answers/218609/how-to-search-the-number-of-days-a-request-was-ope.html
The latest comments and answers for the question "How to search the number of days a request was open for?"Comment by sbattista09 on sbattista09's commenthttps://answers.splunk.com/comments/218626/view.html
sorry, you are correct.
thanks for the help!Mon, 02 Mar 2015 20:53:14 GMTsbattista09Comment by sideview on sideview's commenthttps://answers.splunk.com/comments/219622/view.html
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.Mon, 02 Mar 2015 20:44:07 GMTsideviewComment by sbattista09 on sbattista09's commenthttps://answers.splunk.com/comments/218624/view.html
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?Mon, 02 Mar 2015 20:21:39 GMTsbattista09Comment by sbattista09 on sbattista09's commenthttps://answers.splunk.com/comments/218621/view.html
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"Mon, 02 Mar 2015 19:54:50 GMTsbattista09Comment by sideview on sideview's commenthttps://answers.splunk.com/comments/219614/view.html
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")Mon, 02 Mar 2015 19:19:53 GMTsideviewComment by sbattista09 on sbattista09's answerhttps://answers.splunk.com/comments/218615/view.html
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.
2. request_date is not the "_time" field. How would i change results showing like this 1424926800.000000 to a human readable formant?Mon, 02 Mar 2015 19:03:17 GMTsbattista09Answer by sideviewhttps://answers.splunk.com/answering/219606/view.html
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:
<your search terms> | 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.Mon, 02 Mar 2015 18:17:36 GMTsideview