Checking the Validity of a Table Audit
Jim_Knicely
Administrator
The Vertica AUDIT function can be used to estimate the raw data size of a database, schema, or table.
Ever wonder if it’s accurate? A simple test shows that it is 100 percent accurate if you specify an error tolerance of 0 and a confidence level of 100.
Example:
First let’s audit the table BIG_TABLE.
[dbadmin@s18384357 ~]$ /opt/vertica/bin/vsql -c "SELECT audit('public.big_table', 0, 100);"
audit
----------
10696205
(1 row)
Next extract the data from the table to a file, eliminating all field separators and record terminators as Vertica does not include those bytes in the audit.
[dbadmin@s18384357 ~]$ /opt/vertica/bin/vsql -o /home/dbadmin/big_table.out -F '' -R '' -At -c "SELECT * FROM public.big_table;" -q
Here is the exported file size:
[dbadmin@s18384357 ~]$ stat -c %s /home/dbadmin/big_table.out 10696206
Wait a second! Why is the output file size 1 byte bigger than the result of the Vertica AUDIT function? It’s because there is a training new line character in the file. Let’s get rid of it!
[dbadmin@s18384357 ~]$ printf %s "$(< /home/dbadmin/big_table.out)" > /home/dbadmin/big_table_no_trailing_nl.out [dbadmin@s18384357 ~]$ stat -c %s /home/dbadmin/big_table_no_trailing_nl.out 10696205
Now we are 100% accurate!
Helpful Links:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/LicenseManagement/AUDIT.htm
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Licensing/CalculatingTheDatabaseSize.htm
Have fun!
Comments
Fyi... Instead of having to create the output file as I did in in the example above, you can stream the output from VSQL to WC to get the output size in bytes:
Example:
[dbadmin@s18384357 ~]$ /opt/vertica/bin/vsql -c "SELECT audit('public.big_table2', 0, 100);" audit ------------ 5916352340 (1 row) [dbadmin@s18384357 ~]$ /opt/vertica/bin/vsql -F '' -R '' -At -c "SELECT * FROM public.big_table2;" -q | wc -c 5916352341Thanks!
Question:
My vsq returns, no data.
sankarmn=> SELECT AUDIT('', 'database');
AUDIT
0
(1 row)
How do I know if audit is enabled in database?
Do you have any tables in your database with data?
Check out the link below that lists what is not audited:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/Licensing/CalculatingTheDatabaseSize.htm
Yes, there are tables with data in database.
How do I know if audit for database is enabled?
sankarmn=> SELECT AUDIT('',25,90);
AUDIT
0
(1 row)
Not sure I understand your question. You are calling the AUDIT function so it's always available. The function is returning 0 because you have no data that auditable. If you are looking for the results of a user audit, check the USER_AUDITS system table.
Example:
dbadmin=> \d No relations found. dbadmin=> SELECT audit('', 'DATABASE'); audit ------- 0 (1 row) dbadmin=> \x Expanded display is on. dbadmin=> SELECT * FROM user_audits; -[ RECORD 1 ]-------------------------+------------------------------ size_bytes | 0 user_id | 45035996273704962 user_name | dbadmin object_id | 45035996273704976 object_type | DATABASE object_schema | object_name | rename_me audited_schema_name | audited_object_name | rename_me license_name | vertica audit_start_timestamp | 2020-04-30 14:52:50.708464-04 audit_end_timestamp | 2020-04-30 14:52:50.71799-04 confidence_level_percent | 99 error_tolerance_percent | 5 used_sampling | f confidence_interval_lower_bound_bytes | 0 confidence_interval_upper_bound_bytes | 0 sample_count | 0 cell_count | 0Ok, I got it. So when you create a table T1 and populate data, then the table T1 is auditable by default. Then if you run, SELECT audit('', 'DATABASE');, you'll get an output. Correct me if I am wrong.
Also to run the audit function do you need dbadmin privileges or pseudosuperuser role? I don't have either, would sysmonitor help. I need to run audit on other schemas/objects as well. So what privileges do I need?
Table data is not audited when you insert data
A table is only audited when you audit it manually! Anyone can run the AUDIT function. You will only be able to audit tables that you own or have been granted access to.
I think the command to audit table is,
=> SELECT AUDIT('online_sales', 'schema');
No, when you specify "SCHEMA", you are auditing a SCHEMA...
Example:
dbadmin=> CREATE SCHEMA sankarmn; CREATE SCHEMA dbadmin=> CREATE TABLE sankarmn.tab1 (c INT); CREATE TABLE dbadmin=> CREATE TABLE sankarmn.tab2 (c INT); CREATE TABLE dbadmin=> INSERT INTO sankarmn.tab1 SELECT 1; OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO sankarmn.tab2 SELECT 1 UNION SELECT 2; OUTPUT -------- 2 (1 row) dbadmin=> SELECT audit('sankarmn.tab1', 'TABLE'); audit ------- 1 (1 row) dbadmin=> SELECT audit('sankarmn.tab2', 'TABLE'); audit ------- 2 (1 row) dbadmin=> SELECT audit('sankarmn', 'SCHEMA'); audit ------- 3 (1 row)You really don't have to specify the object type (i.e. table or schema). Vertica can figure it out.
dbadmin=> SELECT audit('sankarmn.tab1'); audit ------- 1 (1 row) dbadmin=> SELECT audit('sankarmn.tab2'); audit ------- 2 (1 row) dbadmin=> SELECT audit('sankarmn'); audit ------- 3 (1 row)Thanks a lot Jim_Knicely.
Your inputs are so informative to understand AUDIT clearly.