Thread: parsing audit table
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? I followed this: http://wiki.postgresql.org/wiki/Audit_trigger - so I'm hoping someone else has something to parse that. original data: "(B04TaEsAAIG5bEEX5xBVPQ,2,jakew,mag,"",2011-07-05,2011-07-05,,,7,528284,"",2011-07-05,13:20:59,2011-07-05,"",A04CQUUABSxYfxftPQqJlg,,JUfhSzwADKqAFSN1Cbv+mg,BUiZqlIABburW7jqdY9JJQ,HUZPx0gACfCxy1Y34QSTQw,,,0,N,Y,Y,919.9100,0.0000,0.0000,2011-07-05,2011-07-05)" new data: "(B04TaEsAAIG5bEEX5xBVPQ,3,jakew,mag,"",2011-07-05,2011-07-05,,,7,528284,"",2011-07-05,13:20:59,2011-07-05,"",A04CQUUABSxYfxftPQqJlg,,JUfhSzwADKqAFSN1Cbv+mg,BUiZqlIABburW7jqdY9JJQ,HUZPx0gACfCxy1Y34QSTQw,,,0,N,N,Y,919.9100,919.9100,0.0000,,2011-08-04)" those 22 alphanumeric columns are IDs. Thanks for any help, Mark
On Tue, Aug 16, 2011 at 4:02 PM, M. D. <lists@turnkey.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
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@2ndquadrant.com>
On Tue, Aug 16, 2011 at 4:02 PM, M. D. <lists@turnkey.bz> wrote:that's not exactly auditable information :D
> 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?
>
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin