Splunk Search
Highlighted

How do you convert the date field and time field on a lookup table to _time?

New Member

Hi,

How do I convert two fields (date and time) from a lookup table to _time?

I would like to use it to create time chart.

I tried this, but it doesn't show the correct _time.

| inputlookup mylookup.csv
| stats count as _time by fieldx
| timechart span=1d count(fieldx)

Sample data from CSV file:

Date,Time
01-01-19,0:03:52
01-01-19,4:15:46
01-01-19,14:11:53

Needed as _time like,

3/24/19 12:25:37.000 AM
01/01/19 00:03:52.000 AM
01/01/19 04:15:46.000 AM
01/01/19 02:11:53.000 PM

Thank you very much.

Tags (2)
0 Karma
Highlighted

Re: How do you convert the date field and time field on a lookup table to _time?

Influencer

_time is the epoch time or the number of seconds from Midnight January 1 1970 UTC.

In general what you want to do is take the separate fields, combine them into one field, and then use a conversion function to parse the represented time into epoch format and store that as _time.

I'm assuming your inputlookup is giving fields just like in your example... Instead of using the inputlookup command, I have this run anywhere search to generate me a couple of fields to simulate your CSV:

|makeresults | fields - _* * | eval _raw="Date,Time
01-01-19,0:03:52
01-01-19,4:15:46
01-01-19,14:11:53" | multikv forceheader=1 | table Date Time

and boom... 3 records each with Date and Time fields per your example.

Combining the Date and Time fields into a single field, I would leverage the eval and the concatenation operator . very simply like so:

<inputlookup or otherwise start of search> | eval datetime=Date." ".Time

From here, it's a matter of converting the datetime into epoch... which eval also has a strptime function, which would enable us to skip the intermediate field step like so:

<inputlookup or otherwise start of search> | eval _time=strptime(Date." ".Time,"%m-%d-%y %H:%M:%S")

Alternatively to get from datetime to _time, one could use the convert command specifying the timeformat. If your timestamps aren't exactly like your example, you may want to also keep reference of the common Date and time format variables which you would use to get your format string for strptime.

Highlighted

Re: How do you convert the date field and time field on a lookup table to _time?

New Member

Thank you very much!

After some time I was able to convert the time to my desired _time.

The only issue is that there is no difference on the data output everytime I use the time picker to search data based on time.

| inputlookup MYDATA.csv
| rename "Call Start Date" as Call_Start_Date "Call Start Time" as Call_Start_Time
| eval cduration_sec = round('Call Duration Minutes'*60, 0)
| eval "Call Duration(hh:mm:ss)" = tostring(cduration_sec, "duration")
| eval Time_With_TZ=Call_Start_Time+" BST"
| eval Date_Time=Call_Start_Date." ".Time_With_TZ
| eval _time=strptime(Call_Start_Date." ".Time_With_TZ,"%Y-%m-%d %H:%M:%S%Z")
| eval Epoch_Time=_time
| convert timeformat="%m-%d-%y %H:%M:%S" mktime(Epoch_Time) | eval Time= strftime(Epoch_Time, "%+")
| table _time field1 field2 ...
| sort 0 -time
0 Karma
Highlighted

Re: How do you convert the date field and time field on a lookup table to _time?

SplunkTrust
SplunkTrust

Time picker doesn’t apply to lookups unless you’re using a time based lookup really. Even then, you’re not “feeding” any time calculations from the time picker to the lookup. The time picker only applies to data in indexes or tsidx files (datamodels, and summary indexes).

0 Karma
Highlighted

Re: How do you convert the date field and time field on a lookup table to _time?

New Member

Thank you very much.

0 Karma
Highlighted

Re: How do you convert the date field and time field on a lookup table to _time?

Esteemed Legend

Like this:

| inputlookup mylookup.csv
| eval _time =  strptime(Date . " " . Time, "%m-%d-%y %H:%M:%S")

The rest of yours stuff looks wonky to me and I am not sure what you are trying to do after this.

0 Karma