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.
