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
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.
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.
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
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
"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.
So what should I do to ingest them into the splunk?
Hi @mehrdad_2000,
DB-Connect and search are two things really different even if they are launched from the same interface:
there are also different pre-requisites:
From this considerations, you can easily understand the difference in performances:
Finally, the problem with the tables not show in list of splunk connect db is surely a grants problem.
Ciao.
Giuseppe
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.
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
Have you verified the user configured in dbconnect has permissions to the table in the database you're connecting to?
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!
Grant read privileges to your Splunk user for this table on the DB side.
I’m using sysadmin user of db to connect database.
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.
What kind of log do you need?
Using the dbconnect UI and SQL Explorer, run a simple query against the table in question. Post a screenshot of the results.