different between "splunk db connect" vs "search"

[indeed_2000]

Hi
I try to join two table in "splunk db connect" with sql command but won't join them. now I have 3 questions:

1-sql editor syntax exactly understand sql command ? because I can run it directly on database.
2-what is the different if I run queries in "splunk db connect" vs "search" ? (different performance,action)
3-some table not show in list of splunk connect db while this table exist in database and when I write query to select that table it will show contents , what is the reason?

Thanks

[woodcock]

Slunk DB Connect has 3 modes:

1: dbxquery: SPL to suck data from DB into a Splunk search.  You could use `| collect` to dump to an index or `| outputlookup` to dump to a lookup.
2: dblookup: SPL to do a lookup to augment data already in your Splunk search results.
3: dbinput: A modular input to pull data into Splunk on a regular basis, just like any other input/data.

[woodcock]

You should ALWAYS do as much work as possible in SQL, especially joins. It may help to ask your DB Admin to create a custom view for you that hides the SQL complexity. It sounds like the credentials you are using do not have enough permissions to access the data (tables) that you need. Again, talk to your DBA.

[isoutamo]

Hi

Underscore (_) in the first character on table name has managed on special way and you couldn’t see/use those through dbconnect. In those case it’s better to create view on DB side.

R. Ismo

[indeed_2000]

Now I’m confused 🙂
1-@gcusello told ingested data in splunk is better you told sql is better 🙂

2-I’m using sysadmin user of db to connect database

[codebuilder]

"SQL Explorer" within dbconnect will allow you to run sql commands you are familiar with. "Open in search" will translate those into dbxquery for you.

[indeed_2000]

So what should I do to ingest them into the splunk?

[gcusello]

Hi @mehrdad_2000,
DB-Connect and search are two things really different even if they are launched from the same interface:

• DB-Connect is a JDBC client that permits to run SQL queries on DB tables from an external DB, not on Splunk data;
• a search is runned on data ingested in Splunk, not on an external DB.

there are also different pre-requisites:

• to use DB-Connect, you need to have a JDBC Driver and an established connection with a DB, in other word you cannot run a query on any DB, you can run a query only on configurated DB connections;
• to use a search you only need to have already ingested data in Splunk.

From this considerations, you can easily understand the difference in performances:

• with DB-Connect you pass through the JDBC Driver, the network, the DB Listener and the DB;
• with Splunk you directly search on Splunk data.

Finally, the problem with the tables not show in list of splunk connect db is surely a grants problem.

Ciao.
Giuseppe

[indeed_2000]

1-You mean performance and reliability will be better if I index them on splunk instead of using db-connect.

2-in sql explorer when i hit open in search data will be ingest into the splunk or I should do something else?

3-I’m using sysadmin user of db to connect database.

[gcusello]

Hi @mehrdad_2000,
loading query results in Splunk gives you the possibility to quickly search in Splunk, obvioulsy this consumes license!
Anyway, DB-Connect is useful to extract data from a DB and use them to enrich the Splunk searches adding data non present in logs (e.g. a CMDB).

To ingest data in Splunk using DB-Connect you have to follow instructions at https://docs.splunk.com/Documentation/DBX/3.2.0/DeployDBX/AboutSplunkDBConnect

Anyway, as @woodcoock said, it's always better to build your joins in SQL (not in Splunk) and extract data as you need in Splunk.

Abouts grants, I don't know without seeing your configurations and, always as @woodcoock said, speak with your dba.

Ciao.
Giuseppe

[codebuilder]

Have you verified the user configured in dbconnect has permissions to the table in the database you're connecting to?

[indeed_2000]

I’m using same user to get tables in splunk and db server.
I can read table in splunk and db server but in splunk all table appear Except this table!

[codebuilder]

Grant read privileges to your Splunk user for this table on the DB side.

[indeed_2000]

I’m using sysadmin user of db to connect database.

[codebuilder]

Can you post screen pics or log messages indicating the issue you are experiencing? This should be easy to resolve, based on what you've shared so far...just need more information.

[indeed_2000]

What kind of log do you need?

[codebuilder]

Using the dbconnect UI and SQL Explorer, run a simple query against the table in question. Post a screenshot of the results.