All Apps and Add-ons

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

jscraig2006
Communicator

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.

0 Karma

Richfez
SplunkTrust
SplunkTrust

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
Communicator

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.

0 Karma

jscraig2006
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...