Page 1 of 1
Audit specific columns
Posted: Thu Aug 03, 2017 10:20 am
by fane89
Hello,
is there a way to AUDIT (
https://my.vertica.com/docs/8.1.x/HTML/ ... ight=AUDIT) just some columns from a table? Or a diffrent approach to achive this?
Re: Audit specific columns
Posted: Thu Aug 03, 2017 6:19 pm
by JimKnicely
Hi,
I think that you can simply SUM the audit size of an individual column ...
Example:
dbadmin=> SELECT * FROM jim;
c | c2
---+-------
1 | TEST
2 | TEST2
(2 rows)
dbadmin=> SELECT SUM(AUDIT_LENGTH(c2)) column_size FROM jim;
column_size
-------------
9
(1 row)
Note that the SUM of the SUMs of each individual column size, is the result I get back from the AUDIT function:
dbadmin=> SELECT SUM(AUDIT_LENGTH(c)) + SUM(AUDIT_LENGTH(c2)) total_size FROM jim;
total_size
------------
11
(1 row)
dbadmin=> SELECT AUDIT('public.jim');
AUDIT
-------
11
(1 row)
Re: Audit specific columns
Posted: Fri Aug 04, 2017 10:57 am
by fane89
Thank You very much kind Sir
strange, there is nothing about it when i serach the docs -
https://my.vertica.com/docs/8.1.x/HTML/ ... 8_1_0-Live
Re: Audit specific columns
Posted: Fri Aug 04, 2017 7:16 pm
by JimKnicely
AUDIT_LENGTH is a secret function! Don't let anyone else know about it
Just kidding...
You can also simply use the LENGTH function:
dbadmin=> \d jim
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------------+------+---------+----------+-------------+-------------
public | jim | c | int | 8 | | f | f |
public | jim | c2 | varchar(10) | 10 | | f | f |
(2 rows)
dbadmin=> SELECT SUM(LENGTH(c2)) total_size FROM jim;
total_size
------------
9
(1 row)
dbadmin=> SELECT SUM(LENGTH(c::VARCHAR)) + SUM(LENGTH(c2)) total_size FROM jim;
total_size
------------
11
(1 row)
Notice that with the LENGTH function you have to convert numbers to strings...