sql_oci_metrics.sql

set serveroutput on
DECLARE
    credential_name varchar2(200) := 'OCI$RESOURCE_PRINCIPAL';
    region varchar2(200)          := 'us-phoenix-1';
    base_uri varchar2(200)       := 'https://telemetry-ingestion.' || region|| '.oraclecloud.com/20180401/metrics';      
    resp dbms_cloud_types.RESP;       
BEGIN

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

  for r in ( 
                with q as ( 
                    /*  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,
                            '{"resourceId":"ocid1.exampleresource.region1.phx.exampleuniqueID","appName":"myAppA"}' dimensions,
                            count(1) value
                        from v$session
                        group by 1,2,3,4,5
                    /* END  MAIN QUERY for metrics */                        
                )
                /* format to oci metric payload */
                select json_object('metricData' value json_array(
                        json_object('namespace'     value q.namespace,
                                    'compartmentId' value q.compartment_id,
                                    'resourceGroup' value q.resource_group,
                                    'name'          value q.name,
                                    'dimensions'    value q.dimensions  FORMAT JSON,
                                    'datapoints'    value json_array(json_object(
                                        'timestamp' value to_char(systimestamp, 'YYYY-MM-DD"T"HH24:mm:ss"Z"'),
                                        'value' value q.value
                                    )))
                ))  metric
                from q        
  ) loop


     resp := dbms_cloud.send_request(
        credential_name => credential_name,
        uri => base_uri,
        headers => JSON_OBJECT('Content-Type' value 'application/json'),
        method => DBMS_CLOUD.METHOD_POST,
        body => UTL_RAW.cast_to_raw(to_char(r.metric))
    );
    dbms_output.put_line(dbms_cloud.get_response_text(resp));

  end loop;


 end;
/