dbms_cloud_metric_sql_pkg.sql

create or replace package oci_metrics as
--
-- ╔═╗┌─┐┌┬┐┬ ┬┌─┐  ╔═╗┌┬┐┌─┐┌─┐┌─┐
-- ╚═╗├┤  │ │ │├─┘  ╚═╗ │ ├┤ ├─┘└─┐
-- ╚═╝└─┘ ┴ └─┘┴    ╚═╝ ┴ └─┘┴  └─┘
--
-- Current PDB's OCID
--      select json_value(cloud_identity , '$.DATABASE_OCID') from v$pdbs
--
-- Crate Dynamic Group "klrice-testing"
--      resource.id = 'ocid1.autonomousdatabase.oc1.phx.abcxyz'	
--
-- Policy
--      Allow dynamic-group klrice-testing to use metrics in compartment dbtools-dev
---- Flush the RP to reload policy
 --     dbms_cloud_admin.disable_resource_principal;
 --     dbms_cloud_admin.enable_resource_principal;


    /*
       setup : 

        p_crednential_name : Name of the DBMS_CREDENTIAL to use for manual credential
                           : For ADB-S Resource built in Resource Principal use "OCI$RESOURCE_PRINCIPAL"

        p_region           : Region to send the metrics which can differ from the region of the DB                           
    */
    procedure setup(p_credential_name varchar2,
                    p_region           varchar2);
    /*
        addMetric:
                p_namespace      - The namespace of the metric                    :  'my_namespace'
                p_compartment_id - The OCID of the compartment to post the metric :  'ocid1.compartment....'
                p_resource_group - The resource group of the metric               :  'my_resource_group'
                p_name           - The name of the metric                         :  'my_metric_name'
                p_dimensions     - The dimensions of the metric in json format    :  '{"factioid":"this","other":"else"}'
                p_value          - The numberic value of the metric               :  123.45             
                p_timestamp      - The timestamp of the metric in format YYYY-MM-DD"T"HH24:mm:ss"Z"                 
    */
    procedure addMetric(p_namespace     varchar2,
                    p_compartment_id varchar2,
                    p_resource_group varchar2,
                    p_name           varchar2,
                    p_dimensions     varchar2,
                    p_value          number,
                    p_timestamp      timestamp);         
    procedure sendBatch;
end;
/

create or replace package body oci_metrics as
    c_base_uri          varchar2(200)       := 'https://telemetry-ingestion.%REGION%.oraclecloud.com/20180401/metrics';      
    l_url               varchar2(200);
    l_region            varchar2(200);
    l_credential_name   varchar2(200);

    l_metrics     json_array_t := json_array_t ();
    l_datapoints   json_array_t := json_array_t ();
    l_datapoint   json_object_t;
    l_payload     json_object_t;
    l_metric      json_object_t;
    resp dbms_cloud_types.RESP;  

    --
    --
    --
    procedure setup(p_credential_name varchar2,
                    p_region           varchar2) as
    begin
        l_region := p_region;
        l_credential_name := p_credential_name;
        l_url := replace(c_base_uri, '%REGION%', l_region);
        l_payload := json_object_t ('{}');


    end;

    --
    -- Send current batch
    --
    procedure  sendBatch as
    BEGIN
        -- make sure there is something to send
        if ( l_metrics.get_Size > 0 ) then 
            l_payload.put('metricData', l_metrics);
            resp := dbms_cloud.send_request(
                credential_name => l_credential_name,
                uri => l_url,
                headers => JSON_OBJECT('Content-Type' value 'application/json'),
                method => DBMS_CLOUD.METHOD_POST,
                body => UTL_RAW.cast_to_raw(l_payload.to_string)
            );

        -- debugging print the return from oci
            dbms_output.put_line(dbms_cloud.get_response_text(resp));

        -- restart the next payload
            l_payload := json_object_t ('{}');
            l_metrics     := json_array_t ();
        end if;
    end;



procedure addMetric(p_namespace     varchar2,
                    p_compartment_id varchar2,
                    p_resource_group varchar2,
                    p_name           varchar2,
                    p_dimensions     varchar2,
                    p_value          number,
                    p_timestamp      timestamp) as 
    begin
        
        l_metric:= json_object_t ('{}');
        l_metric.put('namespace', p_namespace);
        l_metric.put('compartmentId' , p_compartment_id);
        l_metric.put('resourceGroup' , p_resource_group);
        l_metric.put('name'          , p_name);
        l_metric.put('dimensions'    , json_object_t(p_dimensions ) );
            l_datapoint:= json_object_t ('{}');
            l_datapoint.put('timestamp',to_char(systimestamp, 'YYYY-MM-DD"T"HH24:mm:ss"Z"'));
            l_datapoint.put('value', p_value);
            l_datapoints   := json_array_t ();
            l_datapoints.append(l_datapoint);
        l_metric.put('datapoints' , l_datapoints);
        l_metrics.append(l_metric);

        -- oci has a limit of 50 per batch
        if ( l_metrics.get_Size mod 50 = 0 ) then 
            sendBatch;
        end if;
    end;

end;
/

set serveroutput on

--
--███████╗██╗  ██╗ █████╗ ███╗   ███╗██████╗ ██╗     ███████╗    ██╗   ██╗███████╗ █████╗  ██████╗ ███████╗
--██╔════╝╚██╗██╔╝██╔══██╗████╗ ████║██╔══██╗██║     ██╔════╝    ██║   ██║██╔════╝██╔══██╗██╔════╝ ██╔════╝
--█████╗   ╚███╔╝ ███████║██╔████╔██║██████╔╝██║     █████╗      ██║   ██║███████╗███████║██║  ███╗█████╗  
--██╔══╝   ██╔██╗ ██╔══██║██║╚██╔╝██║██╔═══╝ ██║     ██╔══╝      ██║   ██║╚════██║██╔══██║██║   ██║██╔══╝  
--███████╗██╔╝ ██╗██║  ██║██║ ╚═╝ ██║██║     ███████╗███████╗    ╚██████╔╝███████║██║  ██║╚██████╔╝███████╗
--╚══════╝╚═╝  ╚═╝╚═╝  ╚═╝╚═╝     ╚═╝╚═╝     ╚══════╝╚══════╝     ╚═════╝ ╚══════╝╚═╝  ╚═╝ ╚═════╝ ╚══════╝                                                                                                       
--


begin
oci_metrics.setup('OCI$RESOURCE_PRINCIPAL', 'us-phoenix-1');

  for r in (   /*  MAIN QUERY for metrics Should only need to adjust this section to adhere to this structure 

                        Expected Columns:
                            NAMESPACE      - The namespace of the metric                    :  'my_namespace'
                            COMPARTMENT_ID - The OCID of the compartment to post the metric :  'ocid1.compartment....'
                            RESOURCE_GROUP - The resource group of the metric               :  'my_resource_group'
                            NAME           - The name of the metric                         :  'my_metric_name'
                            DIMENSIONS     - The dimensions of the metric in json format    :  '{"factioid":"this","other":"else"}'
                            VALUE          - The numberic value of the metric               :  123.45
                    */
                    select 'a_namespace' namespace,
                            'ocid1.compartment.oc1..aaaaaaaacw2ft7eu33tlaoppsu6mck7qn2wsqefuixcjhza6xhhsbnhvjorq' compartment_id,
                            'sample_resource_group' resource_group,
                            'A_SAMPLE_METRIC'  name,
                            '{"machine":"'|| machine ||'","username":"' ||username ||'"}' dimensions,
                            count(1) value
                        from v$session
                        group by username,machine
                    /* END  MAIN QUERY for metrics */                        
                ) loop

                oci_metrics.addMetric(r.namespace, r.compartment_id, r.resource_group, r.name, r.dimensions, r.value, systimestamp);
    end loop;
    -- send any remaining metrics
    oci_metrics.sendBatch;
end;
/