Skip site navigation (1) Skip section navigation (2)

postgresql log parsing to report on user/db access

From: Larry J Prikockis <lprikockis(at)vecna(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: postgresql log parsing to report on user/db access
Date: 2012-04-25 13:36:16
Message-ID: 4F97FDD0.4070004@vecna.com (view raw or flat)
Thread:
Lists: pgsql-general
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(at)vecna(dot)com
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.


Responses

pgsql-general by date

Next:From: Steve CrawfordDate: 2012-04-25 15:49:07
Subject: Re: postgresql log parsing to report on user/db access
Previous:From: Willy-Bas LoosDate: 2012-04-25 12:30:30
Subject: Re: Psql dosent log error messages on windows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group