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!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...