Audit specific columns

Moderator: NorbertKrupa

Post Reply
fane89
Newbie
Newbie
Posts: 9
Joined: Tue Jul 25, 2017 9:12 am

Audit specific columns

Post by fane89 » Thu Aug 03, 2017 10:20 am

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?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Audit specific columns

Post by JimKnicely » Thu Aug 03, 2017 6:19 pm

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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

fane89
Newbie
Newbie
Posts: 9
Joined: Tue Jul 25, 2017 9:12 am

Re: Audit specific columns

Post by fane89 » Fri Aug 04, 2017 10:57 am

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Audit specific columns

Post by JimKnicely » Fri Aug 04, 2017 7:16 pm

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...
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL Functions”