Is anyone aware of a quick solution for producing user/db access reports from pgsql syslog format logs? in other words, I have a bunch of lines such as: > Apr 22 06:39:04 147283-db3 postgres[13252]: [1800-1] user=database1_remote,db=sqm_remote_database1 LOG: connection authorized: user=database1_remote database=sqm_remote_database1 > Apr 22 06:39:04 147283-db3 postgres[13252]: [1801-1] user=database1_remote,db=sqm_remote_database1 LOG: disconnection: session time: 0:00:00.01 user=database1_remote > Apr 22 06:39:04 147283-db3 postgres[13254]: [1799-1] user=[unknown],db=[unknown] LOG: connection received: host=172.25.150.172 port=58088 > Apr 22 06:39:04 147283-db3 postgres[13254]: [1800-1] user=database1_remote,db=sqm_remote_database1 LOG: connection authorized: user=database1_remote database=sqm_remote_database1 > Apr 22 06:39:04 147283-db3 postgres[13254]: [1801-1] user=database1_remote,db=sqm_remote_database1 LOG: disconnection: session time: 0:00:00.02 user=database1_remote > Apr 22 06:39:12 147283-db3 postgres[13259]: [1799-1] user=[unknown],db=[unknown] LOG: connection received: host=172.25.150.162 port=40768 > Apr 22 06:39:12 147283-db3 postgres[13259]: [1800-1] user=database3_remote,db=sqm_remote_database3 LOG: connection authorized: user=database3_remote > Apr 22 06:39:12 147283-db3 postgres[13259]: [1801-1] user=database3_remote,db=sqm_remote_database3 LOG: disconnection: session time: 0:00:00.01 user=database3_remote > Apr 22 06:39:16 147283-db3 postgres[13263]: [1799-1] user=[unknown],db=[unknown] LOG: connection received: host=172.25.150.69 port=37739 > Apr 22 06:39:16 147283-db3 postgres[13263]: [1800-1] user=database2_remote,db=sqm_remote_database2 LOG: connection authorized: user=database2_remote database=sqm_remote_database2 > Apr 22 06:39:16 147283-db3 postgres[13263]: [1801-1] user=database2_remote,db=sqm_remote_database2 LOG: disconnection: session time: 0:00:00.01 user=database2_remote > Apr 22 06:39:18 147283-db3 postgres[13264]: [1799-1] user=[unknown],db=[unknown] LOG: connection received: host=172.25.150.69 port=37740 and I'd like to be able to generate weekly reports that provide rolled up statistics on which user connected to which db, how many times, etc. I've used pg_fouine for doing reporting on queries, but it doesn't really deal with tracking user/db access. Before I try to whip up something myself with perl or something, I wanted to see if anyone's already done something similar. thanks in advance-- -- Larry J. Prikockis System Administrator 240-965-4597 (direct) lprikockis@xxxxxxxxx http://www.vecna.com http://www.vecnamedical.com http://vecnamed.blogspot.com/ Vecna 6404 Ivy Lane Suite 500 Greenbelt, MD 20770 Phone: 240-965-4500 Fax: 240-547-6133 Better Technology, Better World (TM) The contents of this message may be privileged and confidential. Therefore, if this message has been received in error, please delete it without reading it. Your receipt of this message is not intended to waive any applicable privilege. Please do not disseminate this message without the permission of the author. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
