All Apps and Add-ons

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

mbrazington
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

mbrazington
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

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

Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...