All Apps and Add-ons

How to output a CSV file with certain columns from a dbxquery in Splunk DB Connect 2.2.0?

splunkrocks2014
Communicator

Hi.

I am using Splunk DB Connect version 2.2.0 to download the data from a database and output the data into a CSV file as a lookup file. The following is my sample query:

| dbxquery connection=xyz query="SELECT col1, col2, col3, col4, col5 FROM mytable" | outputcsv mylookup.csv

However, the csv file, "mylookup.csv", has lots of garbage contents such as column name, data type, etc. from each row!!! How can I get rid of column name and data type from the outputcsv?

0 Karma
1 Solution

woodcock
Esteemed Legend

First of all, I think you will prefer the output when you use the shortnames directive. Try this:

| dbxquery shortnames=t connection=xyz query="SELECT col1, col2, col3, col4, col5 FROM mytable" | table col* | outputcsv mylookup.csv

View solution in original post

vj8210
Explorer

hey , try using option: shortnames=t

Example:
| dbxquery query="SELECT actor_id, first_name as fname, last_name as lname, last_update FROM actor" connection="mySQL" shortnames=t

http://docs.splunk.com/Documentation/DBX/2.2.0/DeployDBX/Commands

woodcock
Esteemed Legend

First of all, I think you will prefer the output when you use the shortnames directive. Try this:

| dbxquery shortnames=t connection=xyz query="SELECT col1, col2, col3, col4, col5 FROM mytable" | table col* | outputcsv mylookup.csv

splunkrocks2014
Communicator

It doesn't work. This is a biggest problem in DB Connect 2 and it doesn't work as expected as DB Connect 1. Here is the sample output:

(001) NULL.col1 VARHAR2    (002) NULL.col2 VARCHAR2 .... _raw .... _time
0 Karma

woodcock
Esteemed Legend

Did you notice my comment about shortnames? The key is to do dbxquery shortnames=t; did you do that part?

0 Karma

splunkrocks2014
Communicator

I missed the option, "shortnames=t". It works as expected. Thank you for your help.

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...