Splunk Search

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

alc2019
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

woodcock
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

acharlieh
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.

alc2019
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

jkat54
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

alc2019
New Member

Thank you very much.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...