4i.2.311. Top ADs - Total Costs, between 2020-05-11 and 2020-06-20 (OCI360_REPORTS_COST)
Notes:
1) drag to zoom, and right click to reset
2) up to 31 days of awr history were considered
3)
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
lineItem/referenceNo VARCHAR2(4000)
lineItem/tenantId VARCHAR2(4000)
lineItem/intervalUsageStart VARCHAR2(4000)
lineItem/intervalUsageEnd VARCHAR2(4000)
product/service VARCHAR2(4000)
product/compartmentId VARCHAR2(4000)
product/compartmentName VARCHAR2(4000)
product/region VARCHAR2(4000)
product/availabilityDomain VARCHAR2(4000)
product/resourceId VARCHAR2(4000)
usage/billedQuantity VARCHAR2(4000)
usage/billedQuantityOverage VARCHAR2(4000)
cost/subscriptionId VARCHAR2(4000)
cost/productSku VARCHAR2(4000)
product/Description VARCHAR2(4000)
cost/unitPrice VARCHAR2(4000)
cost/unitPriceOverage VARCHAR2(4000)
cost/myCost VARCHAR2(4000)
cost/myCostOverage VARCHAR2(4000)
cost/currencyCode VARCHAR2(4000)
cost/billingUnitReadable VARCHAR2(4000)
cost/overageFlag VARCHAR2(4000)
lineItem/isCorrection VARCHAR2(4000)
lineItem/backreferenceNo VARCHAR2(4000)
tags/Oracle-Tags.CreatedBy VARCHAR2(4000)
tags/Oracle-Tags.CreatedOn VARCHAR2(4000)
tags/orcl-cloud.free-tier-retained VARCHAR2(4000)
WITH t1 AS (
SELECT SUM("cost/myCost") COMPUTEDAMOUNT,
TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"') ENDTIMEUTC,
"product/availabilityDomain" ITEM_ID
FROM OCI360_REPORTS_COST
/* WHERE */
GROUP BY TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"'), "product/availabilityDomain"
),
trange as (
select trunc(min(TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"')),'HH24') min,
trunc(max(TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"')),'HH24') max
FROM OCI360_REPORTS_COST
),
alldays as ( /* Will generate all days between Min and Max Start Time */
SELECT trunc(trange.min,'DD') + (rownum - 1) vdate,
rownum seq
FROM trange
WHERE trange.min + (rownum - 1) <= trange.max - 1 /* Skip last entry as may be incomplete. */
CONNECT BY LEVEL <= (trange.max - trange.min) + 1
)
select seq snap_id,
TO_CHAR(vdate, 'YYYY-MM-DD HH24:MI') begin_time,
TO_CHAR(vdate+1,'YYYY-MM-DD HH24:MI') end_time,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'CYtq:US-ASHBURN-AD-1',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line1,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'CYtq:US-ASHBURN-AD-2',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line2,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line3,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'CYtq:US-ASHBURN-AD-3',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line4,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line5,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line6,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line7,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line8,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line9,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line10,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line11,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line12,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line13,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line14,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line15
from t1, alldays
where ENDTIMEUTC(+) >= vdate and ENDTIMEUTC(+) < vdate+1
group by seq, vdate
order by seq;
40 rows selected.
oci360 (c) 2020, All rights reserved. oci360 v20.06 (2020-06-25) based on moat369 v2002 (2020-02-27). Timestamp: 2020-06-30T16:12:52