pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedparsing audit table
4+ messages / 3 participants
[nested] [flat]
* parsing audit table
@ 2011-08-16 21:02 M. D. <lists@turnkey.bz>
2011-08-16 21:48 ` Re: parsing audit table M. D. <lists@turnkey.bz>
2011-08-16 23:35 ` Re: [SQL] parsing audit table Jaime Casanova <jaime@2ndquadrant.com>
0 siblings, 2 replies; 4+ messages in thread
From: M. D. @ 2011-08-16 21:02 UTC (permalink / raw)
To: pgsql-sql; pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: parsing audit table
2011-08-16 21:02 parsing audit table M. D. <lists@turnkey.bz>
@ 2011-08-16 21:48 ` M. D. <lists@turnkey.bz>
1 sibling, 0 replies; 4+ messages in thread
From: M. D. @ 2011-08-16 21:48 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
Yes, that does work (and I've been doing just that), but I was hoping
for some database function (in python preferably) that I could return a
row with it's columns and then query corresponding data to make more
sense out of it.
I see it as a complicated function, but if someone would have a similar
function, it would make it 10x easier to make sense of the audit table.
I think it could be made to query corresponding data from any table.
On 08/16/2011 03:08 PM, Hoover, Jeffrey wrote:
> If you reformat it like:
>
> original,B04TaEsAAIG5bEEX5xBVPQ,2,jakew,mag,...,2011-07-05
> new, B04TaEsAAIG5bEEX5xBVPQ,3,jakew,mag,...,2011-08-04
>
> you could probably load it into excel
>
>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of M. D.
> Sent: Tuesday, August 16, 2011 5:03 PM
> To: pgsql-sql@postgresql.org; pgsql-admin@postgresql.org
> Subject: [ADMIN] 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] parsing audit table
2011-08-16 21:02 parsing audit table M. D. <lists@turnkey.bz>
@ 2011-08-16 23:35 ` Jaime Casanova <jaime@2ndquadrant.com>
2011-08-28 10:00 ` Re: [SQL] parsing audit table Jan Eskilsson <janeskil1525@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Jaime Casanova @ 2011-08-16 23:35 UTC (permalink / raw)
To: M. D. <lists@turnkey.bz>; +Cc: pgsql-sql; pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] parsing audit table
2011-08-16 21:02 parsing audit table M. D. <lists@turnkey.bz>
2011-08-16 23:35 ` Re: [SQL] parsing audit table Jaime Casanova <jaime@2ndquadrant.com>
@ 2011-08-28 10:00 ` Jan Eskilsson <janeskil1525@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jan Eskilsson @ 2011-08-28 10:00 UTC (permalink / raw)
To: Jaime Casanova <jaime@2ndquadrant.com>; +Cc: pgsql-sql; pgsql-admin@postgresql.org
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:
> > 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-08-28 10:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-16 21:02 parsing audit table M. D. <lists@turnkey.bz>
2011-08-16 21:48 ` M. D. <lists@turnkey.bz>
2011-08-16 23:35 ` Jaime Casanova <jaime@2ndquadrant.com>
2011-08-28 10:00 ` Jan Eskilsson <janeskil1525@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox