Why is the data not indexing with a joined SQL query in Splunk DB Connect v3.1.3?

[jscraig2006]

I am having an issue with DB Connect v3.1.3. When creating an input for a SQL query that has a join, the data is not being indexed. The SQL query is valid in SSMS as well and SQL explorer in DB Connect and it returns data. Once the input is saved and task has ran, no data is received. It is a rising column query. This only happens with SQL queries with a JOIN. The error I get from _internal is:

018-03-28 17:45:00.074 -0700 INFO c.s.dbx.server.task.listeners.JobMetricsListener - action=collect_job_metrics connection=<server> jdbc_url=jdbc:jtds:sqlserver://<server>:1533/tempdb;useCursors=true;domain=<domain>;useNTLMv2=true status=FAILED input_name=<server>_trend_web_security_log batch_size=1000 error_threshold=N/A is_jmx_monitoring=false start_time=2018-03-28_05:45:00 end_time=2018-03-28_05:45:00 duration=73 read_count=0 write_count=0 filtered_count=0 error_count=0

Java Version: jdk1.8.0_121
Driver: MS-SQL Server Using jTDS Driver With Windows Authentication version: 1.3
Splunk Version: 7.0.3

Simple SQL queries work just fine.

[Richfez]

Ok, while this isn't an answer, maybe it is one... 🙂

In all cases with complex queries, I create a view on the SQL side to do the work. While this causes the obvious result of not ever needing a join in the Splunk's DBX syntax, it also causes a few non-obvious things to happen too.

For one; the DB can optimize that view. If you just fired off random SQL statements from outside SQL Server, they may have some moderate amount of optimizations applied. If you save a view, though, then that will be optimized as far as is possible.

For another; doing this moves the logic for how the DB table fit together into the DB itself. That usually is a good place for it. You can even abstract out your field names trivially and consistently if you use views on the DB side of things.

But mostly; I do it this way so that if the two roles ever get broken up at $work (DBA stuff and Splunk Stuff), the DBAs can handle the business logic and just feed the data that's already coerced into meaning to the Splunk side. The Splunk folks can ingest data that already makes sense. Also this sort of interplay can keep the two teams working together to accomplish their goals and keep silos from happening.

So, while I understand this may not be the answer (hence why I wrote it as a comment), maybe it's your answer?

Happy Splunking,
- Rich

[jcoates]

a view is totally the best way to solve the problem for all the reasons Rich mentions. However, if you can't... I bet that this comes down to column name resolution. Try specifying all column names as completely as possible and see if it goes away.

[jscraig2006]

Thank you Rich. I think you are on the right path regarding optimizing it. Our DBA team is the original author of the query. I will pass it back to them to optimize it and see if they can create a view. I will let you know the outcome.