topic Re: How to extract the date and time from a single column in a CSV file as separate fields? in Splunk Search

How to extract the date and time from a single column in a CSV file as separate fields?

ashabc — Thu, 10 Sep 2015 12:43:53 GMT

I have a CSV file with headers which have date and time stamp fields in a single column. I want to extract date and time as separate fields. e.g. on 1st row of dataset the DeclaredDate is "26/12/2008 10:30" I want to extract fields DeclareDate as 26/12/2008 in date format and 10:30 as DeclareTime in time format.

If I import data using default CSV input type, the DeclareDate is extracted as timestamp for the event. However, I want both DeclaredDate as well as RevokedDate to be extracted in time format..

Can someone give me some clue? I do not have background in regex.

Sample data below:

Id,Name,DeclaredDate,RevokedDate,RegionId,LgaId,Area,AgencyId,ReferenceNo
1,Mt Mologone S44,26/12/2008 10:30,29/12/2008 12:00,4,800,Bland District,NULL,NULL
2,Gulp Road,6/01/2009 15:00,18/01/2009 20:00,1,8350,"Wingecarribee District, nm",NULL,NULL
3,Beaumont,15/01/2009 14:00,16/01/2009 17:00,1,4000,Hornsby Local Goverment Area,NULL,NULL

Re: How to extract the date and time from a single column in a CSV file as separate fields?

woodcock — Thu, 10 Sep 2015 14:02:01 GMT

Like this:

... | rex field=DeclaredDate "(?<DeclareDate>\S+)\s+(?<DeclareTime>.*)" | rex field=RevokedDate "(?<RevokeDate>\S+)\s+(?<RevokeTime>.*)"

Re: How to extract the date and time from a single column in a CSV file as separate fields?

landen99 — Thu, 10 Sep 2015 17:14:52 GMT

you don't need "[" or "]".

\S+ works just as well by itself

Re: How to extract the date and time from a single column in a CSV file as separate fields?

woodcock — Thu, 10 Sep 2015 17:15:59 GMT

True. I will update answer and delete comments.

Re: How to extract the date and time from a single column in a CSV file as separate fields?

landen99 — Thu, 10 Sep 2015 17:33:02 GMT

Otherwise, a very elegant answer, I must say.

I considered giving the eval strftime, and convert or fieldformat strptime approach instead of this one based on rex, but the author never mentioned the need for sorting or filtering the time fields or formatting it from Unix time to various formats or between different time zones, so I feel your rex answer meets the question perfectly.

Re: How to extract the date and time from a single column in a CSV file as separate fields?

ashabc — Thu, 10 Sep 2015 23:04:41 GMT

Thanks Woodcock. It certainly works.
One other question in relation to this. If I now want the duration between DeclareDate and RevokeDate, how can I get that. I tried the example in the url below, but seems not working.

http://answers.splunk.com/answers/39463/calculate-time-difference-between-2-fields-sum-and-group-by-month.html

I tried this:

| convert timeformat="%d/%m/%yT%H:%M" mktime(RevokedDate) mktime(DeclaredDate) | eval duration=RevokedDate-DeclaredDate

Re: How to extract the date and time from a single column in a CSV file as separate fields?

landen99 — Fri, 11 Sep 2015 01:02:59 GMT

This is where you do my solution with eval strftime, strptime, subtraction and convert duration.

Re: How to extract the date and time from a single column in a CSV file as separate fields?

landen99 — Fri, 11 Sep 2015 01:04:12 GMT

Oh, and time zones may be a factor, because it is going to Unix time to be subtracted.

Re: How to extract the date and time from a single column in a CSV file as separate fields?

woodcock — Fri, 11 Sep 2015 03:36:30 GMT

For duration TZ should not be a factor because both times should be the same TZ and we don't care what it is because we are subtracting them; do it like this:

... | rex field=DeclaredDate "(?<DeclareDate>\S+)\s+(?<DeclareTime>.*)" | rex field=RevokedDate "(?<RevokeDate>\S+)\s+(?<RevokeTime>.*)" | DeclaredDateEpoch=strptime(DeclaredDate, "%m/%d/%Y %H:%M") | RevokedDateEpoch=strptime(RevokedDate, "%m/%d/%Y %H:%M") | durationSeconds = RevokedDateEpoch - DeclaredDateEpoch

Re: How to extract the date and time from a single column in a CSV file as separate fields?

landen99 — Fri, 11 Sep 2015 10:23:23 GMT

You Rex date and time separately, so they will not work. Also, the timezones may not be the same, as in the case with bit9 source. Lastly, duration in sec can be made pretty with tostring(sec, "duration").

Re: How to extract the date and time from a single column in a CSV file as separate fields?

woodcock — Fri, 11 Sep 2015 14:09:44 GMT

He asked for 2 things: to separate the TimeDates and to calculate the difference. My answer is in 2 parts that both use the original field. Your comment about tostring is fair, and I thought of it, but this Q&A has been wondering very much so I decided to stick to the main path.

Re: How to extract the date and time from a single column in a CSV file as separate fields?

woodcock — Fri, 11 Sep 2015 14:22:29 GMT

@landen99, I encountered a bug editing the comments in this post (when deleting a duplicate comment of yours) and it deleted the last few comments of yours on accident. I am working to have it restored.

Re: How to extract the date and time from a single column in a CSV file as separate fields?

landen99 — Fri, 11 Sep 2015 14:27:17 GMT

| rex field=DeclaredDate "(?<DeclareDate>\S+)\s+(?<DeclareTime>.*)" | rex field=RevokedDate "(?<RevokeDate>\S+)\s+(?<RevokeTime>.*)" | eval DeclaredDate=DeclaredDate." GMT" | eval RevokedDate=RevokedDate." GMT" | eval DeclaredDateEpoch=strptime(DeclaredDate, "%m/%e/%Y %H:%M %Z") | eval RevokedDateEpoch=strptime(RevokedDate, "%m/%e/%Y %H:%M %Z") | eval duration_sec = RevokedDateEpoch - DeclaredDateEpoch | eval duration=tostring(durationSeconds,"duration")

Time zone is an integral part of creating epoch time. "GMT" is replaced with the respective time zones. The difference on times of the timezones will not care what the timezone is set to, but other operations will care.

Re: How to extract the date and time from a single column in a CSV file as separate fields?

landen99 — Fri, 11 Sep 2015 14:28:37 GMT

No problem, Woodcock. I was just trying to be helpful with a solution which was already on the right track. I'll just compile all of my ideas and corrections to your solution into my own solution. I did not intend to "wander" to topics that you were not interested in discussing. I do think that they are fully relevant to the question, though.

PS: I was not aware that users could delete each other's comments. I've never done that before or seen that option.

Re: How to extract the date and time from a single column in a CSV file as separate fields?

ashabc — Fri, 11 Sep 2015 21:10:54 GMT

Thank you both Woddcock and Landen. Both works!