How can I get Splunk DB Connect 2 to display Microsoft SQL date time columns as a human readable format, not epoch?

[chrisboy68]

Hi,

How can I get Splunk DB Connect 2 to display datetime, date or time columns as human readable (not epoch) for Microsoft SQL Server? I have used DBX via JDBC and the date/time columns all come back as human readable. I saw a few posts to cast the date/time, but this seems clunky. Seems there has got to be a config somewhere for this change...

Thank you,

Chris

[Amohlmann]

can you just format them like this:

basesearch|eval time=strftime(_time,"%c")

This will make the format of the new time field this:
The date and time in the current locale's format as defined by the server's operating system. For example, Mon Jul 13 09:30:00 2015 for US English on Linux.

Common time formats can be found here:
http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/Commontimeformatvariables

[chrisboy68]

Hmm, that did work for me. In testing I have the following field in EPOCH, that I know is "2015-09-15T13:56:00"

CreatedDate:1442339760000

Doing the following search:

basesearch |eval mytime=strftime(CreatedDate,"%c")

"mytime" came back as "Fri Dec 31 23:59:59 9999"

Thanks

Chris

[Amohlmann]

Ah, I see. Splunk does not like it in that format. It wants it to be like 1442339760.000

Slap in :

eval CreatedDate=CreatedDate/1000

So it will look something like this:

basesearch| eval CreatedDate=CreatedDate/1000|eval MyTime=strftime(CreatedDate,"%c")

[jcoates_splunk]

There's two different answers for this, depending on whether you're trying to set the time that Splunk cares about or not. Splunk is a time-series index, so each event indexed has a time field that Splunk cares a lot about. If you're trying to set that time's behavior, you can use a date in the database column -- hopeful a datetime format instead of a string, because that makes format conversions easier. Specify date formatting in your input, like so: http://docs.splunk.com/Documentation/DBX/2.0.4/DeployDBX/Createandmanagedatabaseinputs#Set_parameter...

If you're trying to format dates that are just part of the data, then you need to either do it in SQL as part of your query, or in SPL after you've indexed the data.

[chrisboy68]

Thanks. I'm not sure this solves my issues. Anyway, I did find a work around by doing calculated fields that override the same field name that used to display EPOCH time. Like below is a MS SQL Datetime stamp field:

strftime(TransactionInterval/1000,"%Y-%m-%dT%H:%M:%S.%3N")

Unfortunately, I have to do this for every date or time field and remove microseconds or something else if its a different type of MS SQL date format. Seems like there could be a config that says display EPOCH or human readable.

Tx

Chris

[jcoates_splunk]

yeah, that's what I mean by doing it in SPL after you've indexed. The alternative at this point would be an SQL eval so that the database does the formatting and the field indexes in the format you want.