Splunk Search

date time format

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

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

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.

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