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
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
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
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
Thanks - just tried this & get following error message:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '1970-01-01'.
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.
That query works but hasn't resolved the issue of the issue of being unable to select a column for the timestamp.
Thanks