monitoring the available licensed space through grafana

maksmaks Community Edition User ✭
edited March 2023 in General Discussion

Hello. Could you help me? I want to monitor the available licensed space through grafana, for this I use the SELECT GET_COMPLIANCE_STATUS() function.
But I don't understand how I can use this function to get only the value in the Raw Data Size field, without displaying the rest of the fields. Is it possible?

Answers

  • marcothesanemarcothesane - Select Field - Administrator

    Try this - and then pick the column that you think fits your bill. Note there are "External", "Flex", and "Regular" audited_data, hence the last 3 rows:

    marco ~/1/Vertica/supp $ vsql -U dbadmin -w pwd -h marges1 -d sbx -x -c "                                                                         
dquote> select * from license_audits order by audit_start_timestamp desc limit 3"
-[ RECORD 1 ]-------------------------+------------------------------
database_size_bytes                   | 57382
license_size_bytes                    | 1099511627776
usage_percent                         | 5.21886249771342e-08
audit_start_timestamp                 | 2023-03-06 23:16:04.429775+01
audit_end_timestamp                   | 2023-03-06 23:16:04.430837+01
confidence_level_percent              | 99
error_tolerance_percent               | 5
used_sampling                         | f
confidence_interval_lower_bound_bytes | 57382
confidence_interval_upper_bound_bytes | 57382
sample_count                          | 0
cell_count                            | 0
audited_data                          | External
-[ RECORD 2 ]-------------------------+------------------------------
database_size_bytes                   | 2320229758
license_size_bytes                    | 1099511627776
usage_percent                         | 0.000211023667179688
audit_start_timestamp                 | 2023-03-06 23:16:02.680775+01
audit_end_timestamp                   | 2023-03-06 23:16:04.426545+01
confidence_level_percent              | 99
error_tolerance_percent               | 5
used_sampling                         | t
confidence_interval_lower_bound_bytes | 2264141286
confidence_interval_upper_bound_bytes | 2376318230
sample_count                          | 9999
cell_count                            | 1387776
audited_data                          | Flex
-[ RECORD 3 ]-------------------------+------------------------------
database_size_bytes                   | 4588998836
license_size_bytes                    | 1099511627776
usage_percent                         | 0.00417367012778413
audit_start_timestamp                 | 2023-03-06 23:16:00.013569+01
audit_end_timestamp                   | 2023-03-06 23:16:02.677545+01
confidence_level_percent              | 99
error_tolerance_percent               | 5
used_sampling                         | t
confidence_interval_lower_bound_bytes | 4490315384
confidence_interval_upper_bound_bytes | 4687682288
sample_count                          | 56033
cell_count                            | 623313198
audited_data                          | Regular
  • s_crossmans_crossman Vertica Employee Employee
    edited March 2023

    Hi,
    If you use the query below and set your grafana dash to Table panel and format as table it should give you the raw data size and the +/- size and last audit date. You can of course strip off any of these columns you don't want.
    Notes:
    the last audited col is valuable as the raw and adjust sizes are based on the last time the audit ran.
    the audited_data can be changed to Flex or External or any combination or all

    select (database_size_bytes/1000000)::INTEGER as data_size_raw_mb,
    ((database_size_bytes/1000000)::INTEGER*(error_tolerance_percent/100)) as data_size_estimation_error_mb,
    audit_end_timestamp as latest_audit
    from license_audits
    where audited_data='Regular'
    order by audit_end_timestamp desc limit 1;

    I hope it helps

  • maksmaks Community Edition User ✭

    @s_crossman thanks a lot, but with your query I get a very different value than with the query SELECT GET_COMPLIANCE_STATUS();
    Why is this and what is the correct value then?

    select GET_COMPLIANCE_STATUS();
                                                                                                                                       GET_COMPLIANCE_STATUS                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Raw Data Size: 0.48TB +/- 0.01TB
 License Size : 1.00TB
 Utilization  : 48%
 Audit Time   : 2023-03-09 23:59:15.232149+03
 Node count : 3
 License Node limit : 3
 Compliance Status : The database is in compliance with respect to raw data size.

    select (database_size_bytes/1000000)::INTEGER as data_size_raw_mb,
base-> ((database_size_bytes/1000000)::INTEGER*(error_tolerance_percent/100)) as data_size_estimation_error_mb,
base-> audit_end_timestamp as latest_audit
base-> from license_audits
base-> where audited_data='Regular'
base-> order by audit_end_timestamp desc limit 1;
 data_size_raw_mb | data_size_estimation_error_mb |         latest_audit
------------------+-------------------------------+------------------------------
           229236 |                       11461.8 | 2023-03-09 23:59:01.48986+03
  • s_crossmans_crossman Vertica Employee Employee

    Hi,
    I'll take another look at it. I ran on a similar 1TB license db we have and it seemed to be reasonably close (GET_COMPLIANCE_STATUS() reported .75TB raw size and license_audits reported .82TB). It may be related to different samplings but I think the background audit that runs is supposed to use the same parameters each time, and your last audit date timestamps matched.

    Regards,

  • s_crossmans_crossman Vertica Employee Employee

    Hi,

    Sorry, this got stuck in draft.

    I ran a couple different tests. The docs imply that get_compliance_status sums up types regular and flex where as license_audits breaks them out. As shown below I ran audit, and it came back with 828GB. I ran a slightly different license_audit query and it shows that the entire 828gb is for regular. I also ran a sum of size for all types and got the 828gb.

    My guess is in your database you may have some external and/or flex tables which impact the get_compliance_status raw result, and only if you ask for a sum in the license_audit would you see the same results.

    PartPub80DB=> select audit('');

    audit

    842924596413
    (1 row)
    PartPub80DB=> select database_Size_bytes,audited_data,audit_end_timestamp from license_audits order by audit_start_timestamp desc limit 3;
    database_Size_bytes | audited_data | audit_end_timestamp
    ---------------------+--------------+-------------------------------
    0 | External | 2023-03-10 01:55:34.81215-05
    0 | Flex | 2023-03-10 00:10:15.373464-05
    828608126801 | Regular | 2023-03-10 00:10:15.35928-05
    (3 rows)

    PartPub80DB=> select sum(database_size_bytes),audit_end_timestamp from license_audits group by audit_end_timestamp order by audit_end_timestamp desc limit 1;
    sum | audit_end_timestamp
    --------------+------------------------------
    828608126801 | 2023-03-10 01:55:34.81215-05
    (1 row)

This discussion has been closed.