Splunk Search

Date-Time Field

lucasle
Engager

Hi, 

 

I am currently attempting to split the Date and Time from one field into 2 or more fields. I have read some of the questions and answers here, but to no avail.

 

I am working with Starbucks.csv, which shows the Date, Volume and Closing stock price of Starbucks. The Date format is in YYYY-MM-DD. My intention is to split the Date to Year, Month and Day Fields respectively.

 

I have seen some of the community answers and many proposed a simple method such as |eval YearNo=(Date, "%Y) for the Year field. However, I tried and the search simply did not return any new field, Below is a snippet of the attempt. I put Date and YearNo in the same table to show how YearNo was not extracted.

lucasle_0-1592902148701.png

 

My next thought was that maybe splunk did not register the Date field as a date but merely as a string. I went ahead and plotted the Date vs Volume Chart on the visualization option and it does seem that Splunk registered the Date as date, and hence the plot was crafted nicely. The snippet is shown below.

lucasle_1-1592902294970.png

 

I would greatly appreciate if someone could enlighten me on this situation and how can I extract the date to their individual fields.

 

Cheers,

Lucas

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @lucasle ,

I'm not sure to completely understood your need.

are you speaking of the timestamp or of one other field?

if you're speking of the TimeStamp, _time is stored in epochtime so to search you have to transform using strftime and strptime function in eval command.

If instead you have a different fiel (called e.g. date), it depends on the format and you can use substr or a regex,

so, if you have a field called e.g. Date with format YYYY-MM-DD, you can extract year, month and day in this way:

index=my_index
| eval Year=substr(Date,1,4),Month=substr(Date,5,2),Day=substr(Date,7,2)

In this way you can insert the search or where commands to filter your events, e.g. if you want only the events of May, you could try something like this: 

index=my_index
| eval Year=substr(Date,1,4),Month=substr(Date,5,2),Day=substr(Date,7,2)
| search Month="05"
| table .....

To plot a graphic, it's easier if you have a date, but also with a string you could plot a graphic by month  creating something like this:

index=my_index
| eval Year=substr(Date,1,4),Month=substr(Date,5,2),Day=substr(Date,7,2)
| search Month="05"
| stats count BY Year Month

Ciao.

Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @lucasle ,

I'm not sure to completely understood your need.

are you speaking of the timestamp or of one other field?

if you're speking of the TimeStamp, _time is stored in epochtime so to search you have to transform using strftime and strptime function in eval command.

If instead you have a different fiel (called e.g. date), it depends on the format and you can use substr or a regex,

so, if you have a field called e.g. Date with format YYYY-MM-DD, you can extract year, month and day in this way:

index=my_index
| eval Year=substr(Date,1,4),Month=substr(Date,5,2),Day=substr(Date,7,2)

In this way you can insert the search or where commands to filter your events, e.g. if you want only the events of May, you could try something like this: 

index=my_index
| eval Year=substr(Date,1,4),Month=substr(Date,5,2),Day=substr(Date,7,2)
| search Month="05"
| table .....

To plot a graphic, it's easier if you have a date, but also with a string you could plot a graphic by month  creating something like this:

index=my_index
| eval Year=substr(Date,1,4),Month=substr(Date,5,2),Day=substr(Date,7,2)
| search Month="05"
| stats count BY Year Month

Ciao.

Giuseppe

lucasle
Engager

Hi @gcusello ,

 

Thanks for your swift reply, I very much appreciate it.

 

So to clarify again, the initial method that I mentioned - |eval YearNo = strftime(Date, "%Y") is used when dealing with real time data?

 

Also, thanks for your solution. I was just wondering if Splunk has a more intelligent way of reading the date string and recognising it as Year, Month or Day, instead of using substr to isolate the numericals and group them together. Either way, this works fine and I really appreciate it!

 

Best regards,

Lucas

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @lucasle ,

As I said it depends on the format of date (epochtime or human readable), because the date/time functions work wiith epochtime, so when you manage timestamps, they are in epochtime, so you can use the date/time managing functions; the realtime isn't relevant.

You could use these functions also with other dates, but I think that's easier to use

| eval Year= substr(Date,1,4)

instead of

| eval Year=strftime(strptime(Date,"%Y-%m-%d"),"Y")

because you need to transorm the date two times (date->epochtime->year).

Ciao.

Giuseppe

rnowitzki
Builder

Hi @gcusello ,

 

Just to show you how you can work with " the date string" as a date:

 

| eval Date = strptime(Date, "%Y-%m-%d")
| eval Year = strftime(Date, "%Y")
| eval Month = strftime(Date, "%m")
| eval Day =  strftime(Date, "%d")

 

With the strptime() you convert your yyyy-mm-dd to Unix time and afterwards you can derive Year, Month and Day with strftime from it.

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/DateandTimeFunctions
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables


--
Karma and/or Solution tagging appreciated.
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...