Splunk Search

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

phildefer
Explorer

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,

Labels (2)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

tscroggins
Influencer

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?

0 Karma

phildefer
Explorer

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

tscroggins
Influencer

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")

Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...