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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...