Splunk Search

Bad timestamps in DB Connect query results

Karunamon
Explorer

I am running a query via a created dashboard on one of my production databases. I defined this in the DB Connect app, and I am not actually indexing the contents of the database.

One of my searches on this dashboard is:

| dbquery Prod1 "select * from source where guid='$GUID$'"

This query works and returns valid data for all fields except any time stamps. Instead of a readable timestamp, I just get a string of numbers like: 1349484107.000 (I assume this is an epoch date)

The fields in question on the database are of type DATETIME and some TIMESTAMP, and when running this query with the standard command line MySQL, proper dates are returned.

How do I correct the display format of these fields without indexing the data?

0 Karma
1 Solution

cramasta
Builder

you can use the convert command to convert epoch to a human readabletimestamp

| dbquery Prod1 "select * from source where guid='$GUID$'" | convert timeformat="%Y:%m:%d% H:%M:%S" ctime(FIELD_WITH_EPOCH) AS c_time

View solution in original post

cramasta
Builder

you can use the convert command to convert epoch to a human readabletimestamp

| dbquery Prod1 "select * from source where guid='$GUID$'" | convert timeformat="%Y:%m:%d% H:%M:%S" ctime(FIELD_WITH_EPOCH) AS c_time

sowings
Splunk Employee
Splunk Employee

You can use a calculated field (EVAL- in props.conf) to convert your fields to something pretty-printed.

0 Karma

cramasta
Builder

I dont think dbconnect app has a way of being told what fields are timestamps other than what is used to create the index time of the data.

You will probably either have to do it with the convert command or you can possible do it within the sql query itself

Example I stole from the web

SELECT DATE_FORMAT(FROM_UNIXTIME(date_col), '%e %b %Y') AS date_formatted . . .

Karunamon
Explorer

Okay, that definitely works, but it's going to make for some hellaciously ugly search strings considering I have about 7 or 8 different time fields in this query result. Is there a way to have the human readable format be the default for all queries and relevant fields?

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...