On 04/25/2012 06:36 AM, Larry J Prikockis wrote:
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...
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.
What is the etc? You should be able to parse it pretty quickly with awk
but if you want additional stats like length of time a user connected it
will involve additional work.
Have you considered logging to the csv format?
http://www.postgresql.org/docs/9.1/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG
This may be an easier approach as you can just pull the data into a
table and report from there. Of course you can pre-filter the CSV with
grep or similar to avoid importing uninteresting lines.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
