topic How to visualize data using the time field from the raw data, as opposed to the Timestamp? in Splunk Search

How to visualize data using the time field from the raw data, as opposed to the Timestamp?

phildefer — Sun, 12 Nov 2023 20:33:39 GMT

Hello, I am a beginner with Splunk. I am experimenting with a csv dataset containing the daily average temperature for different cities across the world. As a first step, I would like to see, for a given city, the graph for the average temperature over time. However by default, the X axis on the timechart shows the timestamp of the source file, as opposed to the time field contained in each event. As a result, all events show the same date, which is probably the date the dataset was created.

How do I use the "Date" field contained in each event, instead of the Timestamp of the dataset file?

Thanks,

Re: How to visualize data using the time field from the raw data, as opposed to the Timestamp?

tscroggins — Sun, 12 Nov 2023 21:36:43 GMT

Hi @phildefer,

I would normally recommend extracting the timestamp correctly when the data is indexed, but if you've uploaded the csv file as a lookup file, your approach would differ.

How are you searching the data? How is the Date field formatted?

Re: How to visualize data using the time field from the raw data, as opposed to the Timestamp?

bowesmana — Sun, 12 Nov 2023 21:54:32 GMT

As @tscroggins says, it's always important to get your ingest dates correctly extracted from the data in the first place.

However, to extract a time from a field in the data you use the strptime() function, e.g.

| eval _time=strptime(date_field, "format_string")

which will overwrite the existing _time field with the time converted from your data field called date_field according to the format string you specify.

Time format variables are documented here

https://docs.splunk.com/Documentation/Splunk/9.1.1/SearchReference/Commontimeformatvariables

e, g. this example, which you can paste into your search bar will convert the string in my_date_field to _time.

| makeresults | eval my_date_field="2023-11-13 08:01:02.123" | eval _time=strptime(my_date_field, "%F %T.%Q")

Note that times are converted to epoch times, but the _time field is special in that it will show you the formatted date, rather than the epoch.

Re: How to visualize data using the time field from the raw data, as opposed to the Timestamp?

phildefer — Sun, 12 Nov 2023 21:54:45 GMT

Date has YYYY-MM-DD format. I managed changing the '_time' field by using the command:
eval _time=strptime(Date,"%Y-%m-%d")

Now the Time column in the events list shows the date in the dd/mm/yyyy, with the actual time of 00:00:00.000

Re: How to visualize data using the time field from the raw data, as opposed to the Timestamp?

tscroggins — Sun, 12 Nov 2023 22:10:07 GMT

As @bowesmana noted, this is the way. The timestamp is time zone-aware, though, so be mindful of the offset. If you prefer, you can include a time zone in your conversion, e.g. as a shortcut for for UTC:

| eval _time=strptime(Date."Z", "%Y-%m-%d%Z")