View opm.workload_view | Generated by SchemaSpy |
View Definition:
select `wpm`.`id` AS `id`,`wpm`.`workloadKey` AS `workloadKey`,`wpm`.`workloadType` AS `elementType`,(case when (`wpm`.`workloadType` = 13) then 0 when (`wpm`.`workloadType` = 6) then 1 else 2 end) AS `workloadType`,`wpm`.`nonConformingData` AS `nonConformingData`,`wpm`.`iops95th` AS `iops95th`,`wpm`.`iopsCustom` AS `iopsAvg`,`wpm`.`iopsMedian` AS `iopsMedian`,`wpm`.`iopsMax` AS `iopsMax`,`wpm`.`budgetedIops` AS `iopsBudgeted`,`wpm`.`latency95th` AS `latency95th`,`wpm`.`latencyCustom` AS `latencyAvg`,`wpm`.`latencyMedian` AS `latencyMedian`,`wpm`.`latencyMax` AS `latencyMax`,`wpm`.`capUsed95th` AS `capUsed95th`,`wpm`.`capUsedCustom` AS `capUsedAvg`,`wpm`.`capUsedMedian` AS `capUsedMedian`,`wpm`.`capUsedMax` AS `capUsedMax`,`wpm`.`storageServiceLevelStatus` AS `storageServiceLevelStatus`,`opm`.`conformance_policy`.`latencyRiskThresholdPercent` AS `latencyRiskThresholdPercent`,`opm`.`conformance_policy`.`latencyObservationWindow` AS `latencyObservationDuration`,`assigned_ssl`.`expectedLatency` AS `targetLatency`,`netapp_model`.`cluster`.`name` AS `clusterName`,`netapp_model`.`cluster`.`resourceKey` AS `clusterKey`,`ocum`.`datasource`.`communicationStatus` AS `clusterCommStatus`,`assigned_ssl`.`uuid` AS `assignedSslUuid`,(case when isnull(`assigned_ssl`.`name`) then 'Unassigned' else `assigned_ssl`.`name` end) AS `assignedSslName`,`assigned_ssl`.`peakIopsPerTB` AS `assignedPeakIopsPerTB`,`assigned_ssl`.`absoluteMinIops` AS `assignedAbsoluteMinIops`,`assigned_ssl`.`expectedIopsPerTB` AS `assignedExpectedIopsPerTB`,`suggested_ssl`.`uuid` AS `suggestedSslUuid`,(case when isnull(`suggested_ssl`.`name`) then 'Learning' else `suggested_ssl`.`name` end) AS `suggestedSslName`,(case when (((`assigned_ssl`.`uuid` is not null) and (`suggested_ssl`.`uuid` is not null) and (`assigned_ssl`.`uuid` = `suggested_ssl`.`uuid`)) or isnull(`suggested_ssl`.`uuid`)) then 1 else 0 end) AS `matchedWithSuggestedSsl`,`suggested_ssl`.`peakIopsPerTB` AS `suggestedPeakIopsPerTB`,`suggested_ssl`.`absoluteMinIops` AS `suggestedAbsoluteMinIops`,`suggested_ssl`.`expectedIopsPerTB` AS `suggestedExpectedIopsPerTB`,`opm`.`provisioning_policy`.`uuid` AS `provisioningPolicyUuid`,(case when isnull(`opm`.`provisioning_policy`.`name`) then 'Unassigned' else `opm`.`provisioning_policy`.`name` end) AS `provisioningPolicyName`,(case when (`cw`.`name` is not null) then `cw`.`name` when (`wpm`.`workloadType` = 13) then `vol`.`name` else `netapp_model`.`lun`.`path` end) AS `workloadName`,(case when (`cw`.`svmName` is not null) then `cw`.`svmName` when (`wpm`.`workloadType` = 13) then `vol_svm`.`name` else `lun_svm`.`name` end) AS `workloadSvmName`,(case when (`cw`.`svmKey` is not null) then `cw`.`svmKey` when (`wpm`.`workloadType` = 13) then convert(`vol_svm`.`resourceKey` using utf8mb4) else convert(`lun_svm`.`resourceKey` using utf8mb4) end) AS `workloadSvmKey`,(case when (`cw`.`volumeName` is not null) then `cw`.`volumeName` when (`wpm`.`workloadType` = 13) then `vol`.`name` else `lun_vol`.`name` end) AS `workloadVolumeName`,(case when (`cw`.`volumeKey` is not null) then `cw`.`volumeKey` when (`wpm`.`workloadType` = 13) then `vol`.`resourceKey` else `lun_vol`.`resourceKey` end) AS `workloadVolumeKey`,(case when (`wpm`.`workloadType` = 13) then (`vol`.`state` = 'ONLINE') else (`netapp_model`.`lun`.`isOnline` <> 0) end) AS `isOnline`,(case when (`cw`.`size` is not null) then `cw`.`size` when (`wpm`.`workloadType` = 13) then `vol`.`size` else `netapp_model`.`lun`.`size` end) AS `workloadCapacity`,coalesce(`netapp_model`.`lun`.`sizeUsed`,`vol`.`sizeUsed`,0) AS `workloadCapacityUsed`,(case when isnull(`assigned_ssl`.`uuid`) then 1 when (((`wpm`.`workloadType` = 13) and (`vol`.`state` <> 'ONLINE')) or ((`wpm`.`workloadType` = 6) and (`netapp_model`.`lun`.`isOnline` = 0)) or (`ocum`.`datasource`.`communicationStatus` <> 0)) then 0 when ((`assigned_ssl`.`uuid` is not null) and isnull(`wpm`.`nonConformingData`)) then 3 else 2 end) AS `conformanceStatus`,(case when (`wpm`.`workloadType` = 13) then `vol_aggr`.`name` else `lun_vol_aggr`.`name` end) AS `workloadAggregateName`,(case when (`wpm`.`workloadType` = 13) then `vol_aggr`.`uuid` else `lun_vol_aggr`.`uuid` end) AS `workloadAggregateUuid`,coalesce(round(greatest(((`assigned_ssl`.`expectedIopsPerTB` * (select `workloadCapacity`)) / 1099511627776),`assigned_ssl`.`absoluteMinIops`),0),`assigned_ssl`.`absoluteMinIops`) AS `assignedExpectedIops`,coalesce(round(greatest(((`assigned_ssl`.`peakIopsPerTB` * coalesce(`wpm`.`capUsedCustom`,(select `workloadCapacityUsed`))) / 1099511627776),(select `assignedExpectedIops`)),0),(select `assignedExpectedIops`),`assigned_ssl`.`absoluteMinIops`) AS `assignedPeakIops`,coalesce(round(greatest(((`suggested_ssl`.`expectedIopsPerTB` * (select `workloadCapacity`)) / 1099511627776),`suggested_ssl`.`absoluteMinIops`),0),`suggested_ssl`.`absoluteMinIops`) AS `suggestedExpectedIops`,coalesce(round(greatest(((`suggested_ssl`.`peakIopsPerTB` * coalesce(`wpm`.`capUsedCustom`,(select `workloadCapacityUsed`))) / 1099511627776),(select `suggestedExpectedIops`)),0),(select `suggestedExpectedIops`),`suggested_ssl`.`absoluteMinIops`) AS `suggestedPeakIops` from ((((((((((((((`opm`.`workload_provision_mapping` `wpm` left join `netapp_model`.`cluster` on((`wpm`.`clusterId` = `netapp_model`.`cluster`.`objid`))) left join `ocum`.`datasource` on((`netapp_model`.`cluster`.`datasourceId` = `ocum`.`datasource`.`id`))) left join `opm`.`storage_service_level` `assigned_ssl` on((`wpm`.`storageServiceLevelId` = `assigned_ssl`.`id`))) left join `opm`.`storage_service_level` `suggested_ssl` on((`wpm`.`suggestedStorageServiceLevelId` = `suggested_ssl`.`id`))) left join `opm`.`conformance_policy` on((`assigned_ssl`.`conformancePolicyUuid` = `opm`.`conformance_policy`.`uuid`))) left join `opm`.`provisioning_policy` on((`opm`.`provisioning_policy`.`id` = `wpm`.`provisioningPolicyId`))) left join `netapp_model`.`volume` `vol` on(((`vol`.`objid` = `wpm`.`workloadId`) and (`wpm`.`workloadType` = 13)))) left join `netapp_model`.`vserver` `vol_svm` on((`vol_svm`.`objid` = `vol`.`vserverId`))) left join `netapp_model`.`lun` on(((`netapp_model`.`lun`.`objid` = `wpm`.`workloadId`) and (`wpm`.`workloadType` = 6)))) left join `netapp_model`.`vserver` `lun_svm` on((`lun_svm`.`objid` = `netapp_model`.`lun`.`vserverId`))) left join `netapp_model`.`volume` `lun_vol` on((`lun_vol`.`objid` = `netapp_model`.`lun`.`volumeId`))) left join `netapp_model`.`aggregate` `vol_aggr` on((`vol`.`aggregateId` = `vol_aggr`.`objid`))) left join `netapp_model`.`aggregate` `lun_vol_aggr` on((`lun_vol`.`aggregateId` = `lun_vol_aggr`.`objid`))) left join `opm`.`coherence_workload` `cw` on((`cw`.`key` = `wpm`.`workloadKey`))) where (`wpm`.`workloadKey` is not null)
Possibly Referenced Tables/Views: