Created
May 20, 2022 15:26
-
-
Save krisrice/68e23a2101fe10c8efc26371b8c59d5c to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment