General Discussion

OCI query for historical data

gaurav_verma

Hello, 

 

I am looking to build SQL query to see Cluster's Used, Unused and Allocated capacity from past 7 weeks. One pointer for each week. 

I am able to generate for current date from view "report_storage_summary_aux" but dont know how to use fact tables. 

 

SELECT storage_name, storage_pool_used_capacity 

FROM report_storage_summary_aux

WHERE storage_name LIKE 'Cluster1' AND storage_pool_used_capacity <> 0  OR

storage_name LIKE 'Cluster2' AND storage_pool_used_capacity <> 0 

Thanks

Gaurav

 

1 ACCEPTED SOLUTION

gaurav_verma

For others visibility. This query works for me. 

 

SELECT DISTINCT STORAGE.name, STORAGE.dataCenter AS DC, STORAGE.family, DATEDIM.fullDate AS Date,sum(STORAGE_FACT.CapacityMB) AS CapacityMB, sum(STORAGE_FACT.usedCapacityMB) AS usedCapacityMB 
FROM storage_dimension STORAGE, date_dimension DATEDIM, storage_and_storage_pool_capacity_fact STORAGE_FACT
WHERE 
STORAGE_FACT.storageTk=STORAGE.tk AND 
STORAGE.dataCenter IN('DC1','DC2','DC3') AND 
DATEDIM.tk=STORAGE_FACT.dateTk AND 
DATEDIM.fullDate>(DATE_ADD(CURDATE(),INTERVAL -30 DAY)) AND
GROUP BY STORAGE.name, DATEDIM.fullDate

Thanks

View solution in original post

1 REPLY 1

gaurav_verma

For others visibility. This query works for me. 

 

SELECT DISTINCT STORAGE.name, STORAGE.dataCenter AS DC, STORAGE.family, DATEDIM.fullDate AS Date,sum(STORAGE_FACT.CapacityMB) AS CapacityMB, sum(STORAGE_FACT.usedCapacityMB) AS usedCapacityMB 
FROM storage_dimension STORAGE, date_dimension DATEDIM, storage_and_storage_pool_capacity_fact STORAGE_FACT
WHERE 
STORAGE_FACT.storageTk=STORAGE.tk AND 
STORAGE.dataCenter IN('DC1','DC2','DC3') AND 
DATEDIM.tk=STORAGE_FACT.dateTk AND 
DATEDIM.fullDate>(DATE_ADD(CURDATE(),INTERVAL -30 DAY)) AND
GROUP BY STORAGE.name, DATEDIM.fullDate

Thanks

View solution in original post

Announcements
NetApp on Discord Image

We're on Discord, are you?

Live Chat, Watch Parties, and More!

Explore Banner

Meet Explore, NetApp’s digital sales platform

Engage digitally throughout the sales process, from product discovery to configuration, and handle all your post-purchase needs.

NetApp Insights to Action
I2A Banner
Public