Splunk Search

date time format

ryoku
Explorer

Hi,
we have a tool, which is writing to a database.
I use splunk db connect to get the data out of it.
The tool writes a timestamp with YYYY-MM-DD into the database.
This is not respected by splunk, because it is doing like MM/DD/YYY

When I use the dbquerys as they come on a default splunk environment splunk has the date format:10/28/13 3:38:39.000 AM
The replication monitor tool is writing to the database in this format: 2013-10-23 06:33:47.443

So when I search with the DBquery it cannot match them. When I remove the time related query it is showing me results.

Solution approaches:
- I set the date/time in the windows machine like it is in the database --> no success
- I tried with different urls - like described here: http://answers.splunk.com/answers/525/how-can-i-change-the-time-format-in-splunk-web --> no success
- I tried to use the Time_Format Parameter like this:
[Vault Monitor]
TIME_FORMAT = %Y-%m-%d %k:%M:%S
in
C:\Program Files\Splunk\etc\system\local\props.conf --> no success

anyone an fruitful idea?
Thanks!

BR

ryoku
Explorer

hello...
I did it now in SQL with
Select format(sampledate,'MM/dd/yyyy hh:mm:ss','en-US')

but it is not taking this into consideration when I use the date picker.
Or I want to narrow down to the last 60 seconds.

it does not compare my column with the splunk date....
anyone else an idea?

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

hi, does this help? http://docs.splunk.com/Documentation/DBX/1.1/DeployDBX/Troubleshoot#Issues_with_bad_line_breaking.2F...

I usually try to solve time issues in SQL first, it's easier to ask the database to CAST than it is to write a TIME_FORMAT.

ryoku
Explorer

hello...
I did it now in SQL with
Select format(sampledate,'MM/dd/yyyy hh:mm:ss','en-US')

but it is not taking this into consideration when I use the date picker.
Or I want to narrow down to the last 60 seconds.

it does not compare my column with the splunk date....
anyone else an idea?

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Leveraging Automated Threat Analysis Across the Splunk Ecosystem

Are you leveraging automation to its fullest potential in your threat detection strategy?Our upcoming Security ...

Can’t Make It to Boston? Stream .conf25 and Learn with Haya Husain

Boston may be buzzing this September with Splunk University and .conf25, but you don’t have to pack a bag to ...

Splunk Lantern’s Guide to The Most Popular .conf25 Sessions

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...