topic Re: Why is Splunk DB Connect changing the table output of my query? in Splunk Search

Why is Splunk DB Connect changing the table output of my query?

PPape — Thu, 19 Feb 2015 14:01:05 GMT

Hello,

I found a strange behavior with my mySQL Server.

I try to do this query:

select t.change_time, t.create_time_unix, t.tn, t.title, t.timeout, q.name AS Queue, ty.name AS 'type', s.name AS Service 
from ticket as t 
inner join queue as q 
on t.queue_id = q.id 
inner join ticket_type as ty 
on t.type_id = ty.id 
inner join service as s 
on t.service_id = s.id

I expect a table like:

change_time, create_time_unix, tn, title, timeout, Queue, type, Service

this command works in SQL Studio.

But when I'm using it with the DB Connect App, it gives me back a table like this:

change_time, create_time_unix, tn, title, timeout, name

and name has the data from Service.

Do you guys have an idea what's going wrong and how I can manage this?

Thanks in advance for your help.

Re: Why is Splunk DB Connect changing the table output of my query?

PPape — Fri, 20 Feb 2015 08:53:36 GMT

Thanks for editing ppablo! 🙂

Any new Ideas here?

Re: Why is Splunk DB Connect changing the table output of my query?

PPape — Fri, 20 Feb 2015 15:02:27 GMT

Okay I opened an support Case. I will give an answer here if I have some news from Splunk Support

Re: Why is Splunk DB Connect changing the table output of my query?

PPape — Mon, 23 Feb 2015 07:48:20 GMT

No answer from Splunk Support yet. But I'll keep you updated. Some new ideas here?

Re: Why is Splunk DB Connect changing the table output of my query?

PPape — Tue, 24 Feb 2015 08:52:16 GMT

Splunk Support answered.

They can reproduce this issue it seems like there is an bug in the JDBC Driver with mySQL.
Because on MSSQL it works correctly.
Splunk Support opend an bug report and gave it to their dev team to verify.

I'll keep you informed when I have some new Infos.

Re: Why is Splunk DB Connect changing the table output of my query?

ppablo — Wed, 25 Feb 2015 02:15:39 GMT

No problem @PPape

I'm the content manager for Splunk Answers, so I edit most of the posts that come up on the site to make them easier to find for the experts that can answer them and for other users who are searching for solutions to the same questions.

Glad you got a response from Splunk support 🙂 Once you hear back some concrete info, it'll be great if you can share it on this post. Hope you get an answer soon.

Cheers!

Re: Why is Splunk DB Connect changing the table output of my query?

mchang_splunk — Mon, 28 Sep 2020 19:00:34 GMT

This is MySQL JDBC driver issue:
http://www.anujgakhar.com/2012/01/11/railo-mysql-and-column-aliases/
The JDBC specification states that a column name is not changed by “AS”, it’s always the actual name of the column (if such a name exists), so that clients can build a query based on metadata alone. One can only find the name used in the ‘AS’ through ResultSetMetaData’s “getColumnLabel()” method.

Please use this SQL instead:

select t.change_time, t.create_time_unix, t.tn, t.title, t.timeout, q.Queue, ty.type, s.Service
from ticket as t
inner join (select id, name as Queue from queue) as q
on t.queue_id = q.id
inner join (select id, name as 'type' from ticket_type) as ty
on t.type_id = ty.id
inner join (select id, name as Service from service) as s
on t.service_id = s.id

MSSQL doesn't have this issue

Re: Why is Splunk DB Connect changing the table output of my query?

PPape — Thu, 26 Feb 2015 08:06:04 GMT

This is it! Thank you very much!

Re: Why is Splunk DB Connect changing the table output of my query?

PPape — Thu, 26 Feb 2015 09:30:54 GMT

Okay this creates a very huge Database load.

A coworker had the Idea of an View as workaround.
Now I'll test if this is a bit more recource friendly.

Re: Why is Splunk DB Connect changing the table output of my query?

PPape — Thu, 26 Feb 2015 14:03:56 GMT

Okay, the solution with the view is much mor efficient.
But thank you @mchang_splunk for your quick help.