Subscribe

Query export of built in ocum reports.

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.

[ Edited ]

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

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

Re: Query export of built in ocum reports.

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.

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

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

Re: Query export of built in ocum reports.

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

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

Re: Query export of built in ocum reports.

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

 

Dan