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?
Audit specific columns
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Audit specific columns
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)
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Audit specific columns
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
strange, there is nothing about it when i serach the docs - https://my.vertica.com/docs/8.1.x/HTML/ ... 8_1_0-Live
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Audit specific columns
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...
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...
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.