Hello,
I am getting the error "there was an error processing your request. It has been logged" when I try to save the following query in a Data Lab Input for DB Connect:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,108) AS Date,
CAST(CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,114) AS VARCHAR(20)) AS Measured_Date,
LL.prod_id
INTO #TempData
FROM eng..lost_loads LL INNER JOIN met..range_radius RR ON RR.prod_id = LL.prod_id
WHERE LL.run_datetime BETWEEN DATEADD(mi, -15, GETDATE()) AND GETDATE()
order by LL.run_datetime
SELECT
Date,
CONVERT(smalldatetime, Measured_Date) AS Measured_Date
,COUNT(1) AS TempData
FROM #TempData
GROUP BY
Date, CONVERT(smalldatetime, Measured_Date)
ORDER BY Measured_Date DESC
DROP TABLE #TempData
I entered the query manually in the db_inputs.conf as per this thread: https://answers.splunk.com/answers/607692/splunk-dbconnect-311-build-34-there-was-an-error-p.html
However, I am still not seeing it when I refresh the Data Inputs page, I am assuming the server may need rebooted, but this is not an option at this time. Any suggestions?
Thanks
So instead of a UNION (since this was placing data into a temp table and then working off that data), I just rewrote the second SELECT statement. It's ugly, but this works perfect:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,108) AS Date
,CONVERT(smalldatetime, CAST(CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,114) AS VARCHAR(20))) AS Measured_Date
,COUNT(LL.prod_id) AS TempData
FROM eng..lost_loads LL inner join met..range_radius RR ON RR.prod_id = LL.prod_id
WHERE LL.run_datetime between dateadd(mi, -15, getdate()) and getdate()
GROUP BY
CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,108)
,CAST(CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,114) AS VARCHAR(20))
ORDER BY CAST(CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,114) AS VARCHAR(20))
Like I said, it's ugly. But it works lol.
So instead of a UNION (since this was placing data into a temp table and then working off that data), I just rewrote the second SELECT statement. It's ugly, but this works perfect:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,108) AS Date
,CONVERT(smalldatetime, CAST(CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,114) AS VARCHAR(20))) AS Measured_Date
,COUNT(LL.prod_id) AS TempData
FROM eng..lost_loads LL inner join met..range_radius RR ON RR.prod_id = LL.prod_id
WHERE LL.run_datetime between dateadd(mi, -15, getdate()) and getdate()
GROUP BY
CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,108)
,CAST(CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,114) AS VARCHAR(20))
ORDER BY CAST(CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,114) AS VARCHAR(20))
Like I said, it's ugly. But it works lol.
As far as I know, DB connect doesn't support SP (Stored Procedure) style queries. Once upon a time, I remember using
UNION
SQL command to have my query work.
if you must drop table after transaction, perhaps a second scheduled search that runs after 1st one can do it with:
|dbxquery "DROP TABLE <TABLE_NAME>"
A defined temp table will not work when doing a CREATE TABLE, however, I took a query and instead of the CREATE TABLE, I did SELECT INTO table_name and it worked just fine in Splunk. Really odd. I did not think about using the UNION, I will look in to that. I appreciate your comment!
If you can merge your queries into one SELECT, I think it is definitely better than UNION.
UNION is a shortcut to me if I have the query from some DB admin and not have a time to make it better 🙂