Checking the Validity of a Table Audit

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

  • Jim_KnicelyJim_Knicely Administrator

    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
    5916352341
    

    Thanks!

  • Question:
    My vsq returns, no data.
    sankarmn=> SELECT AUDIT('', 'database');

    AUDIT

    0
    (1 row)
    How do I know if audit is enabled in database?

  • Jim_KnicelyJim_Knicely Administrator

    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)

  • Jim_KnicelyJim_Knicely Administrator
    edited April 2020

    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                            | 0
    
  • Ok, 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?

  • Jim_KnicelyJim_Knicely Administrator

    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');

  • Jim_KnicelyJim_Knicely Administrator
    edited May 2020

    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.

Sign In or Register to comment.