All Apps and Add-ons

Splunk DB Connect upgrade (v2>v3) -- search error

Engager

Hello,

The following input query worked fine in DBX2, but now in DBX3 I'm getting an error that says:

A processing error "Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon."

It runs successfully in the first step where you "Execute SQL", but then when I hit "Next" and try to save, it throws an error.

I know there is no semicolon needed, because when I add in a semicolon it tells me the syntax is wrong in the "Execute SQL" step. In addition, I can run the query just fine through SQL Server Management Studio. Here is the query:

WITH T1 AS
(Select count(*) AS [Count] FROM people WHERE primaryphone not like '%555%')

, T2 AS
(SELECT count(*) AS [Count] FROM people WHERE secondaryphone not like '%555%' and len(secondaryphone) > 3)

,T3 AS
(SELECT count(*) AS [Count] FROM people WHERE email not like '%@emaildomain2.com' and email not like '%emaildomain1.com%')

SELECT * FROM T1 UNION
SELECT * FROM T2 UNION
SELECT * FROM T3

Thanks in advanced for the help!

0 Karma
1 Solution

Engager

I was able to actually find the answer on my own:

Just removing the temp tables and union:

SELECT count() AS [Count] FROM people WHERE primaryphone not like '%555%' UNION
SELECT count(
) AS [Count] FROM people WHERE secondaryphone not like '%555%' and len(secondaryphone) > 3 UNION
SELECT count(*) AS [Count] FROM people WHERE email not like '%@emaildomain2.com' and email not like '%emaildomain1.com%'

The above query was accepted by DBX3 and I was able to create my input. 🙂

View solution in original post

Engager

I was able to actually find the answer on my own:

Just removing the temp tables and union:

SELECT count() AS [Count] FROM people WHERE primaryphone not like '%555%' UNION
SELECT count(
) AS [Count] FROM people WHERE secondaryphone not like '%555%' and len(secondaryphone) > 3 UNION
SELECT count(*) AS [Count] FROM people WHERE email not like '%@emaildomain2.com' and email not like '%emaildomain1.com%'

The above query was accepted by DBX3 and I was able to create my input. 🙂

View solution in original post

Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!