Announcements

SecurID® Community Blog

Subscribe to the official SecurID Community blog for information about new product features, industry insights, best practices and more.

How to Output PSQL Queries to CSV Format in Authentication Manager

DavidAllison
Respected Contributor Respected Contributor
Respected Contributor
3 0 1,007

Sometimes you need to run a SQL query against the Authentication Manager database to get information not available in the Security Console reports.  There is an easy way to get these reports to output in CSV format, just like the canned reports.

 

1.   Start with a working SQL query, like this one:

 

Select ctkipkey_last_downloaded_by, ctkipkey_last_downloaded_on from am_token where ctkipkey_last_downloaded_on is not null; 

 

 

2.   To run it from the command line, you would execute PSQL like so (in this example the readonly database username is rodbuser):

 

cd /opt/rsa/am/pgsql/bin

 

./psql -h localhost -p 7050 -d db -U rodbuser -c 'Select ctkipkey_last_downloaded_by, ctkipkey_last_downloaded_on from am_token where ctkipkey_last_downloaded_on is not null;'

 

 

 

3.   To take the same command, but have it output to a CSV file, add the following characters in bold (and notice that the trailing semicolon and the single quotes have to be removed):

 

./psql -h localhost -p 7050 -d db -U rodbuser -c "copy (Select ctkipkey_last_downloaded_by, ctkipkey_last_downloaded_on from am_token where ctkipkey_last_downloaded_on is not null) TO STDOUT WITH CSV HEADER " > /tmp/report_ctkip-activations.csv

 

You can now transfer your file from the /tmp directory and manipulate it the same as any Security Console report.