Tracking get_compliance_status

Kurt_Larson

I'm trying to create a table in a Vertica instance to track the results of the get_compliance_status function over time.  However, when I try use the function in an insert, as in

INSERT INTO <some_table> SELECT GET_COMPLIANCE_STATUS();

 I get an error indicating that there is no from clause.  Same error if I use the function in a CTAS statement. 

Why can't I use a select that executes just fine on it's own, i.e.

SELECT GET_COMPLIANCE_STATUS();

and use in another statement that requires a valid select as part of the othe statement?

This seems like a bug in the statement parse tree.

Are there other ways to accomplish what I'm trying do?

[Deleted User]

Hi Kurt,
I think you are getting an error because get_compliance_status is considered an HP Vertica Meta-Function. With INSERT statements, you can't use meta-functions, so you're getting the error message because Vertica thinks you're adding a select statement (vs. meta-function).   For more information see: http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/INSERT.htm '

Hope this helps!

Kurt_Larson

Hi Vicki,

I appreciate your reply but the documentation for the insert statement you refered to is also at least partially wrong then.  Although I see documentation says "Don not use meta-functions..."  is also says "This expression must not contain nested expressions, such as function calls."  However, the following example insert with a function call works in contradiction to the documentation!

create table public.foo as select *, now() as snap_time from v_catalog.databases;
select * from public.foo;
drop table public.foo;

So when are we supposed to know when functions or meta-functions work and when they don't.

[Deleted User]

Sorry, I think that sentence in the documentation is a little misleading. It should say something more along the lines of "...must not contain nested expressions. For example, it must not contain nested function calls." Function calls work with insert and create table statements, just not if they are nested. Additionally, meta-functions won't work.
I'll make sure the documentation is changed to better explain that part.

Kurt_Larson

Write a reply   

Kurt_Larson

Oops, clicked sumit before typing....

Thanks Vicki for following up.  If I could go back to my problem at hand though.  I'm trying to track the license compliance of my instance over time in the database itself.  I've been successful doing this for other operational metrics like storage by disk volume and row counts by inserting a select * from ... from some of the v_monitor tables into my tracking tables on daily basis.  Is there something similar I can do in the database to capture the raw size and utilization metrics that the get_compliance_status() meta-function returns in text and insert that into a table.

Kurt_Larson

I found the solution to my problem.  Frankly, I'm a little embarrased I didn't find it sooner.  The

v_catalog.license_audits

table has everything I'm looking for to track license compliance over time.

   

[Deleted User]

I know you can get a compliance message (tells you if you are in compliance or not) from the compliance_message column in the v_catalog.databases system table. Let me check in with someone on how you might retrieve the other data. Sorry I don't have a full answer at the moment.

[Deleted User]

Excellent! I sent my comment before refreshing my page so I didn't see that you found it. Sorry, I didn't know about that on off hand.

Navin_C

Hi Kurt,

You can also query table DATABASES from time to time to feed data into your table.
This table shows vital stats of your database and the compliance policy too.

select * from databases;

Hope this helps.
NC