Getting Data In

String to Date Time

dhiraj027in
New Member

I am new to splunk and currently trying to get the date and time difference (Opened vs Resolved) for an incident.
Based on the field type Opened & Resolved are string type and what should I do? I have gone to multiple answers but not able to figure out the solution. Please help.

Below is the example of my selected fields

Assigned to = Arjay Torreliza
Category = Alert
Number = INC0975300
Opened = 5/31/2016 22:43
Resolved = 6/1/2016 9:49
host = prd-p-ttsrqrml973d
source = incident.csv
sourcetype = csv
Tags (1)
0 Karma
1 Solution

sundareshr
Legend

First, you need to convert the string to epoch time using the strptime command & then find the difference.. try this

| eval opened_epoch=strptime(Opened, "%-m/%d/%Y %H:%M) | eval resolved_epoch=strptime(Resolved, "%-m/%d/%Y %-H:%M) | eval duration=tostring(resolved_epoch-opened_epoch, "duration")

View solution in original post

0 Karma

sundareshr
Legend

First, you need to convert the string to epoch time using the strptime command & then find the difference.. try this

| eval opened_epoch=strptime(Opened, "%-m/%d/%Y %H:%M) | eval resolved_epoch=strptime(Resolved, "%-m/%d/%Y %-H:%M) | eval duration=tostring(resolved_epoch-opened_epoch, "duration")
0 Karma

dhiraj027in
New Member

Great. This works but the duration is not exactly what I am looking for. Is this possible?

Current output (this shows 6 days, 23 hours & 23 seconds)
6+23:23:00.000000

What I am looking for (167 hours and 23 seconds)
167:23:00.000000

0 Karma

sundareshr
Legend

Add this to the end

| rex field=z "(?<d>\d{0,2})\+?(?<h>\d{2})(?<ms>:\d{2}:\d{2})" | eval d=if(len(d)=0, 0, d) | eval duration=(d*24+h).ms 
0 Karma

dhiraj027in
New Member

I used the above and integrated with my query. I am not getting any output for "duration". Should I change anything here?

index="incidents_stores_wisp" date_year="2016" Category="Alert" | eval opened_epoch=strptime(Opened, "%m/%d/%Y %H:%M") | eval resolved_epoch=strptime(Resolved, "%m/%d/%Y %H:%M") | eval duration=tostring(resolved_epoch-opened_epoch, "duration") | rex field=z "(?\d{0,2})\+?(?\d{2})(?:\d{2}:\d{2})" | eval d=if(len(d)=0, 0, d) | eval duration=(d*24+h).ms | table Number "Assigned to" "Short description" Opened Resolved duration
0 Karma
Get Updates on the Splunk Community!

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...