topic Re: String to Date Conversion in Splunk Search

String to Date Conversion

sandeep_thosar — Mon, 21 Oct 2013 13:36:08 GMT

Hi Team,

I am new to splunk and currently we are working to visualize splunk reports to Tableau, but when we import data into Tableau using Splunk ODBC driver we get Date field as string in splunk.In Tableau for increamental Refersh required either int or Date field and hence it is not working.

My query is as follows.

index="indexname" sourcetype="Sourcetype" Search condition | stats count by Date

Please help me out.

Thanks is advance.

Regards,

Sandeep Thosar

Re: String to Date Conversion

aholzer — Mon, 28 Sep 2020 15:01:39 GMT

Look at the convert command:

http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/Convert

Example: index="indexname" sourcetype="Sourcetype" Search condition | convert auto(Date) | stats count by Date

Or look at the strptime() function:

http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/CommonEvalFunctions
http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/Commontimeformatvariables

Example: index="indexname" sourcetype="Sourcetype" Search condition | eval date_time = strptime(Date, "%H:%M") | stats count by date_time

Hope this helps

Re: String to Date Conversion

sandeep_thosar — Mon, 21 Oct 2013 14:11:18 GMT

Thanks for help Aholzerbut still i am facing issue with rex as i am extracing date field using rex, so can you told me how to aply eval function with rex beacuse i am trying the same and get error invalid rex command.please find query with Rexindex="indexname" sourcetype="Sourcetype" Search condition | rex "[(?P[^ :s*?]+)" | convert auto(Date) | stats count by DatePlease help me.Thanks in advance.

Re: String to Date Conversion

aholzer — Mon, 21 Oct 2013 14:17:43 GMT

So you are having trouble extracting the date in the first place?

If your rex is/was working, then your error is pertaining to the fact that you are trying to perform the convert on the field called "Date", but you are extracting to a field called "date". When dealing with fields, case matters.

Re: String to Date Conversion

sandeep_thosar — Mon, 21 Oct 2013 14:34:16 GMT

Hi Aholzer,

sorry it's typo mistake, but still it's not working.

Please suggest me my folloiwng query is right or i want to change something to work your suggetion.

Rexindex="indexname" sourcetype="Sourcetype" Search condition | rex "[(?P[^ :s*?]+)" | eval date_time = strptime(Date, "%H:%M")| stats count by DatePlease

Regards,

Sandeep

Re: String to Date Conversion

aholzer — Mon, 21 Oct 2013 14:36:06 GMT

The strptime(Date, "%H:%M") was just an example, I don't know what the pattern of your time is... You are going to have to change the "%H:%M" to match your time pattern. Look at the third link I sent you for pattern options.

Before you get into testing the strptime, you should probably confirm that your rex is working.

Re: String to Date Conversion

emiller42 — Mon, 21 Oct 2013 21:13:00 GMT

As aholzer says, make sure your rex is properly extracting your date. Then give us an example of what the extracted date looks like. Then we can possibly give you a suggestion for how to convert it.

Re: String to Date Conversion

ShaneNewman — Tue, 22 Oct 2013 02:19:41 GMT

I assume you are getting date in epoch time...

Try putting this in your search command at the end...

eval Date=strftime(Date, "%m/%d/%Y %H:%M:%S")

This will make the epoch time stamp look like 10/21/2013 21:19:00

Re: String to Date Conversion

grijhwani — Tue, 22 Oct 2013 03:15:20 GMT

Your query would be a lot easier to answer if we could see a sample of the data you are attempting to reformat, rather than flying blind and guessing.

Re: String to Date Conversion

sandeep_thosar — Wed, 23 Oct 2013 06:48:37 GMT

Hi,
Thanks for the reply.

Please find below sample data for your reference.

Event1 server1 [17/Oct/2013:00:00:52 -0500] "IPAddress
Event2 server1 [17/Oct/2013:01:02:59 -0500] "IPAddress
Event3 server1 [18/Oct/2013:02:00:50 -0500] "IPAddress

Please find search query
index=Indexname sourcetype="Sourcetype" "Search Condition" | rex "[(?P[^ :\s*?]+)" | stats count As Total by Date

Date Total
01-Oct-2013 2
01-Oct-2013 1

Above output date columns datetype is string and i reuqired this to convert into Date format ("dd-mm-yyy")

Please help me.

Regards,

Sandeep

Re: String to Date Conversion

sandeep_thosar — Wed, 23 Oct 2013 07:02:37 GMT

Hi,

I am tried above but still unable to convert string into date it's just change the date format.

Re: String to Date Conversion

aholzer — Wed, 23 Oct 2013 16:02:19 GMT

Using this link http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/Commontimeformatvariables you can use this pattern "%d-%b-%Y" to convert to a date.

Here's the strptime function call that should work for you:
strptime(Date, "%d-%b-%Y")

Re: String to Date Conversion

ShaneNewman — Thu, 24 Oct 2013 03:13:14 GMT

Actually, just a small modification to aholzer's comment: strptime(Date, "%d/%b/%Y")

Re: String to Date Conversion

aholzer — Mon, 28 Oct 2013 14:17:27 GMT

@ShaneNewman, if you notice after he does the extraction the Date field looks like "01-Oct-2013", hence why I used the dashes (-) rather than the slashes (/)

Re: String to Date Conversion

ShaneNewman — Mon, 28 Oct 2013 23:53:44 GMT

That would be my bad...