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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...