Getting Data In

db connect timestamp conversion

aaronkorn
Splunk Employee
Splunk Employee

Hello,

We are running queries directly in the splunk db connect and not doing an input but the timestamps are getting reformatted and there is no obvious correlation between the two. For example: the query in SQL returns the Update_Time field as 2013-04-10 10:11:50 (yes it is set as a date/time field) but when I run the same query in splunk db connect it returns 1365603110.000. Any ideas how to reformat it?

0 Karma
1 Solution

Dan
Splunk Employee
Splunk Employee

In Splunk, add: | convert ctime(Update_Time)

View solution in original post

zsteinkamp_splu
Splunk Employee
Splunk Employee

To use a DB result field as the event time, then do this:
| dbxquery connection=your.db.connection query="SELECT createdAt, name FROM some_table" | eval _time=strptime(createdAt, "%Y-%m-%d %H:%M:%S") | timechart span=7d count by name
This assumes date/time fields come back from your DB like 2017-10-16 16:20:00.

0 Karma

Dan
Splunk Employee
Splunk Employee

In Splunk, add: | convert ctime(Update_Time)

Dan
Splunk Employee
Splunk Employee

| bucket Update_Time span=2m | stats count by Update_Time

0 Karma

ihayesjr
New Member

This is helpful, but how do I create a time chart after doing this?

0 Karma

aaronkorn
Splunk Employee
Splunk Employee

excellent! Thank you

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 ...