thodoris77

SQL queries - Output to .csv file

Discussion created by thodoris77 Expert on Mar 17, 2015

Custom SQL queries are useful for creating custom reports, but if you want to quickly run a query and get a .csv file with the output, you can do this from the console of the appliance, or connecting via ssh. Log in to the appliance using the root account (the default password is 'vmturbo'), and run the command below, inserting your SQL query as noted:

 

mysql -uvmtplatform -pvmturbo vmtdb -e "SQL QUERY" | tr '\t' ',' > outputfile.csv

 

 

For your knowledge:

-uvmtplatform - This is a read-only account to the mysql database

-pvmturbo - This is the default password for the user name above

outputfile.csv - This is whatever you want to call the output file.

 

The file is placed in the root directory on the appliance, and you can download this using SSH FTP (using a tool such as WinSCP or Filezilla).

 

As an example, I ran the command below, which references a query to show the last 100 login attempts to VMTurbo:

 

mysql -uroot -pvmturbo vmtdb -e "select

snapshot_time as 'Date & Time', action_name as 'Action Type', details as "User"

from audit_log_entries

where target_object_class = 'LoginManager'

ORDER BY snapshot_time DESC

limit 100;" | tr '\t' ',' > data.csv

 

See the screenshots below:

 

Running the query on the VMTurbo appliance console.

Screen Shot 2015-03-17 at 11.45.39.png

 

Locating the file using Filezilla (SSH FTP connection to the appliance).

Screen Shot 2015-03-17 at 11.38.31.png

 

 

For Database Definitions, please see here.

For Custom Reports, please see here.

 

Outcomes