Splunk Search

How to change the date format if query MySQ data base in DB connect.

felix_fxm
Engager

After query MySQL data base in DB connect, the date is number, how to make it as "YYYY-MM-DD HH-MM-SS"?

sroback_splunk
Splunk Employee
Splunk Employee

Hi Tom, You might want to have a look at this documentation about timestamps and dboutput in DB Connect:
http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring#About_timestam...
Make sure to review the section on "Timestamp best practices." In case you are using timestamp as rising column, it is not recommended.

Also, you might want to review this Search Reference doc to make sure you are using proper syntax for the "eval" command:
http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/CommonEvalFunctions

0 Karma

sroback_splunk
Splunk Employee
Splunk Employee

Hi. You can also try using an SQL statement in your query that converts epoch time to datetime. For example:

SELECT FROM_UNIXTIME(epoch timestamp, optional output format)
The default output is YYYY-MM-DD HH:MM:SS
0 Karma

tom_frotscher
Builder

Hi Felix,

if your date is an epoch timestamp, you can use the strftime command to convert it. Assume time_epoch is your date as number, then:

... | eval date=strftime(time_epoch, "%F %T")

Greetings

Tom

felix_fxm
Engager

Thanks Tom and Sroback! I have tried both, but failed. Following is my search command.

| dbquery WAS_Dev limit=1000 "select * from wsa_db.metering left join (wsa_db.user)\n on (wsa_db.user.Oxygen_Id=wsa_db.metering.Oxygen_Id)"|eval date=strftime(time_epoch, "%F %T")

But the column of “Start_Time” is still a number, such as "1402012699.000".
Type of this column_name is "Date_Time".

Is there some problem with my search command?

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...