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

Re: [SQL] parsing audit table

From: Jan Eskilsson <janeskil1525(at)gmail(dot)com>
To: Jaime Casanova <jaime(at)2ndquadrant(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
Subject: Re: [SQL] parsing audit table
Date: 2011-08-28 10:00:21
Message-ID: CAHot5JGZQNo5P7SN0zuz51oj9CN1OaxjpXnc3W6XK5HFkO=nBg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-sql
Hi Jamie & All


I experimented a bit with the audit function and i added a field to the
audit table where i store which table the audit record originates from. I
was thinking it should be possible to to pare the hstore fields using
populate_record to its original form using the original table definition.
The query below works but of course only as long as there is only one record


select * FROM populate_record(null::t,
                              (select log_new_values FROM audit.audit_log
                              WHERE  log_schema = 'public' AND log_table =
't' AND log_id = 10 ));

What i would like to do is to return a goup of records for example all
record within a time span for a certain table and my sql understanding is
not good enough to solve this and i cant really find any examples how it
should be done. The query below gets audit records within a times pan but it
fails since more then one record is returned.

select * FROM populate_record(null::t,
                              (select log_new_values FROM audit.audit_log
                              WHERE  log_schema = 'public' AND log_table =
't' AND log_when BETWEEN '2011-08-20' AND '2011-08-25'  ));


Is what i like to to at all possible to solve ?

Thank you in Advance!


Jan Eskilsson


2011/8/17 Jaime Casanova <jaime(at)2ndquadrant(dot)com>

> On Tue, Aug 16, 2011 at 4:02 PM, M. D. <lists(at)turnkey(dot)bz> wrote:
> > Hi everyone,
> >
> > I'm a bit lazy, or actually in a bit of a crunch.  I added an audit
> > recording a few months ago, but never really used it much, but today I'm
> > seeing a bunch of suspicious activity by one user.  Does someone have any
> > function to quickly parse this data?
> >
>
> that's not exactly auditable information :D
>
> i made this one for that: https://github.com/jcasanov/pg_audit
>
> it has two versions one using hstore in which you will see in old
> "column=>old_data" and in new "column=>new_data" seems better and i
> guess you can use hstore functions on it
> (http://www.postgresql.org/docs/9.0/static/hstore.html)
>
> The other one uses arrays to store column names, old values, new
> values, still more parseable
>
> --
> Jaime Casanova         www.2ndQuadrant.com
> Professional PostgreSQL: Soporte 24x7 y capacitaciĆ³n
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

In response to

pgsql-admin by date

Next:From: Craig RingerDate: 2011-08-28 13:43:37
Subject: Re: What is postgresql status? I'm not sure
Previous:From: AndrejDate: 2011-08-28 04:43:22
Subject: Re: Using Postgresql as application server

pgsql-sql by date

Next:From: Emi LuDate: 2011-08-30 14:51:36
Subject: where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
Previous:From: MIkhail PuzanovDate: 2011-08-26 07:06:23
Subject: Re: new table with a select

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