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

[splunkrocks2014]

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?

[vj8210]

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 · Accepted solution]

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]

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

[woodcock]

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

[splunkrocks2014]

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