String to Date Conversion

[sandeep_thosar]

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

[ShaneNewman]

That would be my bad...

[aholzer]

@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 (/)

[ShaneNewman]

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

[aholzer]

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

[sandeep_thosar]

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

[grijhwani]

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.

[ShaneNewman]

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

[sandeep_thosar]

Hi,

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

[emiller42]

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.

[aholzer]

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.

[sandeep_thosar]

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

[aholzer]

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.

[sandeep_thosar]

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.

[aholzer]

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