All Apps and Add-ons

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

chrisboy68
Contributor

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
Communicator

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

0 Karma

chrisboy68
Contributor

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

0 Karma

Amohlmann
Communicator

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

jcoates_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

chrisboy68
Contributor

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

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

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.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...