monitoring the available licensed space through grafana

maksmaks Community Edition User ✭
edited March 25 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 7

    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.