Query export of built in ocum reports.

ijustwantthemodule — Wed, 04 Jun 2025 14:03:37 GMT

Is there any way to see the sql query for the built in reports? I'm looking for this one specifically.

https://library.netapp.com/ecmdocs/ECMP1653272/html/GUID-C7F78D5B-459E-49F4-BBFC-AF307F366B8E.html

Re: Query export of built in ocum reports.

mbeattie — Fri, 09 Feb 2018 04:42:31 GMT

Hi,

I had a quick look for you...

C:\>cd /d C:\Program Files\NetApp\ocum\reports\repository\public

C:\Program Files\NetApp\ocum\reports\repository\public>type aggregate-capacity-utilization.rptdesign | clip

"select * from ocum_report_birt.aggregatecapacityutilization"

select `inventoryaggregate`.`objid` AS `aggrId`,`inventoryaggregate`.`name` AS `aggrName`,`inventoryaggregate`.`clusterId` AS `clusterId`,(select `cluster`.`name` from `netapp_model_view`.`cluster` where (`cluster`.`objid` = `inventoryaggregate`.`clusterId`)) AS `clusterName`,`hapair`.`haPairName` AS `haPair`,`hapair`.`objid` AS `haPairId`,`inventoryaggregate`.`nodeId` AS `nodeId`,`inventoryaggregate`.`sizeTotal` AS `totalDataCapacity`,`inventoryaggregate`.`sizeUsed` AS `usedDataCapacity`,`inventoryaggregate`.`sizeAvail` AS `availableDataCapacity`,`inventoryaggregate`.`snapshotSizeUsed` AS `snapshotReserveUsedCapacity`,`inventoryaggregate`.`snapshotSizeAvail` AS `snapshotReserveAvailCapacity`,`inventoryaggregate`.`snapshotSizeTotal` AS `snapshotReserveTotalCapacity`,`inventoryaggregate`.`totalCommitted` AS `totalCommitted`,`ocum`.`aggregateregressioninfo`.`bytesUsedPerDay` AS `bytesUsedPerDay`,cast((select floor(`ocum`.`aggregate`.`daysUntilFull`) from `ocum`.`aggregate` where (`inventoryaggregate`.`objid` = `ocum`.`aggregate`.`id`)) as unsigned) AS `daysToFull`,(case when (`inventoryaggregate`.`aggregateTypeRaw` = 'ssd') then 'ssd' when (`inventoryaggregate`.`isHybridEnabled` = 1) then 'flashPool' when ((`inventoryaggregate`.`aggregateTypeRaw` = 'vmdisk') or (`inventoryaggregate`.`aggregateTypeRaw` = 'hdd') or (`inventoryaggregate`.`aggregateTypeRaw` = 'lun')) then 'standard' end) AS `aggregateType`,`inventoryaggregate`.`raidTypeRaw` AS `raidType`,`inventoryaggregate`.`stateRaw` AS `state`,(select `ocum`.`optionchainvalue`.`optionValue` from `ocum`.`optionchainvalue` where ((`ocum`.`optionchainvalue`.`domainObject_id` = `inventoryaggregate`.`objid`) and (`ocum`.`optionchainvalue`.`optionName` = 'event.growthRate.sensitivity') and (`ocum`.`optionchainvalue`.`domainObject_type` = 'inventory.ontap.storage.Aggregate'))) AS `growthRateSensitivityThreshold`,(select `ocum`.`optionchainvalue`.`optionValue` from `ocum`.`optionchainvalue` where ((`ocum`.`optionchainvalue`.`domainObject_id` = `inventoryaggregate`.`objid`) and (`ocum`.`optionchainvalue`.`optionName` = 'event.growthRate.minChangePct.aggregate') and (`ocum`.`optionchainvalue`.`domainObject_type` = 'inventory.ontap.storage.Aggregate'))) AS `growthRateThreshold`,(select `ocum`.`optionchainvalue`.`optionValue` from `ocum`.`optionchainvalue` where ((`ocum`.`optionchainvalue`.`domainObject_id` = `inventoryaggregate`.`objid`) and (`ocum`.`optionchainvalue`.`optionName` = 'threshold.aggrSpaceFull') and (`ocum`.`optionchainvalue`.`domainObject_type` = 'inventory.ontap.storage.Aggregate'))) AS `spaceFullThreshold`,(select `ocum`.`optionchainvalue`.`optionValue` from `ocum`.`optionchainvalue` where ((`ocum`.`optionchainvalue`.`domainObject_id` = `inventoryaggregate`.`objid`) and (`ocum`.`optionchainvalue`.`optionName` = 'threshold.aggrSpaceNearlyFull') and (`ocum`.`optionchainvalue`.`domainObject_type` = 'inventory.ontap.storage.Aggregate'))) AS `spaceNearlyFullThreshold`,(select `ocum`.`optionchainvalue`.`optionValue` from `ocum`.`optionchainvalue` where ((`ocum`.`optionchainvalue`.`domainObject_id` = `inventoryaggregate`.`objid`) and (`ocum`.`optionchainvalue`.`optionName` = 'threshold.aggrOvercommitted') and (`ocum`.`optionchainvalue`.`domainObject_type` = 'inventory.ontap.storage.Aggregate'))) AS `overcommittedThreshold`,(select `ocum`.`optionchainvalue`.`optionValue` from `ocum`.`optionchainvalue` where ((`ocum`.`optionchainvalue`.`domainObject_id` = `inventoryaggregate`.`objid`) and (`ocum`.`optionchainvalue`.`optionName` = 'threshold.aggrNearlyOvercommitted') and (`ocum`.`optionchainvalue`.`domainObject_type` = 'inventory.ontap.storage.Aggregate'))) AS `nearlyOvercommittedThreshold`,(select `ocum`.`optionchainvalue`.`optionValue` from `ocum`.`optionchainvalue` where ((`ocum`.`optionchainvalue`.`domainObject_id` = `inventoryaggregate`.`objid`) and (`ocum`.`optionchainvalue`.`optionName` = 'threshold.aggrDaysUntilFull') and (`ocum`.`optionchainvalue`.`domainObject_type` = 'inventory.ontap.storage.Aggregate'))) AS `daysUntilFullThreshold`,(select `ocum`.`optionchainvalue`.`optionValue` from `ocum`.`optionchainvalue` where ((`ocum`.`optionchainvalue`.`domainObject_id` = `inventoryaggregate`.`objid`) and (`ocum`.`optionchainvalue`.`optionName` = 'threshold.aggrSnapshotFull') and (`ocum`.`optionchainvalue`.`domainObject_type` = 'inventory.ontap.storage.Aggregate'))) AS `snapshotFullThreshold`,(case when (`inventoryaggregate`.`isSnapLock` = 0) then 'non_snaplock' else `inventoryaggregate`.`snapLockTypeRaw` end) AS `snapLockType` from ((`netapp_model_view`.`aggregate` `inventoryaggregate` left join `ocum_report_birt`.`hapairview` `hapair` on(((`inventoryaggregate`.`nodeId` = `hapair`.`node1Id`) or (`inventoryaggregate`.`nodeId` = `hapair`.`node2Id`)))) left join `ocum`.`aggregateregressioninfo` on((`inventoryaggregate`.`objid` = `ocum`.`aggregateregressioninfo`.`aggregate_id`)))

If you connect to the OCUM database their is a table view called "aggregatecapacityutilization" within the "ocum_report_birt" database

/Matt

Re: Query export of built in ocum reports.

ijustwantthemodule — Fri, 09 Feb 2018 12:38:27 GMT

Thanks for the help. Is there a way to get access to the ocum_report_birt database on version 7.1? I have access to ocum_report but I'm getting access denied to the other.

Dan

Re: Query export of built in ocum reports.

mbeattie — Sun, 11 Feb 2018 22:24:13 GMT

Hi Dan,

Yes it is possible to access the "ocum_report_birt" database however you need to connect to MySQL as the root user as access to the views in the database are restricted by default.

I'd recommend using HeidiSQL (free). Assuming you are using the Windows installer, download the X64 .exe here:

https://www.heidisql.com/download.php

Copy the .exe to:

C:\Program Files\MySQL\MySQL Server 5.6\lib

Send a shortcut to your desktop and open a connection as the root MySQL user. Hope that helps

/Matt

Re: Query export of built in ocum reports.

mbeattie — Sun, 11 Feb 2018 22:54:24 GMT

Hi Dan,

A more efficient method of getting the the SQL definitions for each view in the in 'ocum_report_view' database is to query the MySQL information schema:

SELECT TABLE_NAME, VIEW_DEFINITION FROM information_schema.VIEWS WHERE TABLE_SCHEMA='ocum_report_birt'

That will give the SQL for all views that OCUM reports are based on, alternately add an AND clause to filter by report name. EG:

SELECT TABLE_NAME, VIEW_DEFINITION FROM information_schema.VIEWS WHERE TABLE_SCHEMA='ocum_report_birt' AND TABLE_NAME = 'aggregatecapacityutilization'

/Matt

Re: Query export of built in ocum reports.

ijustwantthemodule — Mon, 12 Feb 2018 13:21:34 GMT

Unfortunately it appears the root account is not accessible prior to version 7.2. We are do for an upgrade this month.

Dan

topic Re: Query export of built in ocum reports. in Active IQ Unified Manager Discussions

Query export of built in ocum reports.

Re: Query export of built in ocum reports.

Re: Query export of built in ocum reports.

Re: Query export of built in ocum reports.

Re: Query export of built in ocum reports.

Re: Query export of built in ocum reports.