All Apps and Add-ons

No columns listed when choosing a column as timestamp

jackreeves
Explorer

I am in the process of creating a new input in DBConnect. After running my SQL query which returns correct column, I then attempt to select a column as a timestamp, which is "Date Reported". "Date Reported" is in this format, 2017-12-15 08:39:23.0.

However DB Connect fails to identify any columns and returns "no matches" under the column drop down.

SQL Query is:
use osmq
SELECT "Date Reported" AS Date_Reported FROM Tickets_IT

Please could anyone advise!

Thanks

cmerriman
Super Champion

what version of db connect do you have and what type of db? postgres? mysql? can you try to put the timestamp in epoch? something like this: round(("Date Reported" -DATE '1970-01-01' ) * 86400,0) as epoch_date_reported ?
http://docs.splunk.com/Documentation/DBX/3.1.1/DeployDBX/Createandmanagedatabaseinputs

0 Karma

jackreeves
Explorer

DB Connect Version - 3.1.1
DB Type - MS-SQL Server Using MS Generic Driver

Where would the round(("Date Reported" -DATE '1970-01-01' ) * 86400,0) as epoch_date_reported fit into my query? Not experienced with SQL.

Thanks

0 Karma

cmerriman
Super Champion

it would be in the select statement.

SELECT "Date Reported" AS Date_Reported, round(("Date Reported"  -DATE '1970-01-01' ) * 86400,0) as epoch_date_reported FROM Tickets_IT
0 Karma

jackreeves
Explorer

Thanks - just tried this & get following error message:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '1970-01-01'.

0 Karma

cmerriman
Super Champion

sorry, that was originally for oracle sql. try something more like DATEDIFF(SECOND,{d '1970-01-01'}, "Date Reported") there are slight differences with different sql database syntaxes, so googling can help you if this doesn't work, as i'm not the most familiar with ms-sql.

0 Karma

jackreeves
Explorer

That query works but hasn't resolved the issue of the issue of being unable to select a column for the timestamp.

Thanks

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