Porting Custom 7m WFA filter over to cDOT

[dburklan]

Hello,

I have a customer who is actively trying to port their existing 7m WFA workflows over to cDOT. Due to the way they manage their environment they have many custom filters & finders that they have incorporated into their 7m workflows. I have been able to translate a few of them without issue however I am stuck with one in particular. The filter I am referring to allows an aggr to be selected based on the percentage of committed space which is defined below:

• (Sum of all Qtrees Quota / total Aggr size) * 100 and committed space is less than $commitment_threshold

I am having trouble porting this filter over as there is no quota table in the “cm_storage” database in WFA. Do you know of any alternative approaches that I could take to convert this filter? Here is the SQL query from the filter I have been referring to:

SELECT -- DISTINCT(ac.ip) AS 'ip'
    ac.ip AS 'ip',
    ac.commitment
FROM
    (
    select
        storage.aggregate.name as 'name',
        storage.array.ip ,
        storage.array.name AS 'array.name',
        COALESCE(((SUM(storage.quota.hard_disk_limit_mb) / storage.aggregate.total_size_mb) * 100),
        0) as 'commitment'
    from
        storage.quota,
        storage.volume,
        storage.array,
        storage.aggregate,
        storage.resource_group,
        storage.resource_group_member
    where
        storage.aggregate.array_id = storage.array.id
        and storage.volume.aggregate_id = storage.aggregate.id
        and storage.quota.volume_id = storage.volume.id
        AND storage.aggregate.id = resource_group_member.aggregate_id
        AND storage.resource_group.id = resource_group_member.group_id
        AND storage.resource_group.name = '${group_name}'
    group by
        storage.array.ip,
        storage.array.name,
        storage.aggregate.name ) ac
WHERE
    ac.commitment <= '${commitment_threshold}'
order BY
    ac.commitment

Any help would be greatly appreciated, thanks!

Dan

[adaikkap]

Hi Dan,

     What version of OCUM are you using as DataSource. IIRC OCUM 6.0 doesnt collect information regarding Quota.

Regards

adai

[dhruvd]

Hi Dan,

In the cm_storage schema, you can find the qtree quota limit in the cm_storage.qtree table.

cm_storage.qtree.disk_limit_mb would be the field you are looking for.

-Dhruv

[dburklan]

Thanks, I will give that a shot.

Dan

[dburklan]

In case anybody is interested here is the cDOT-compatible query I came up with:

SELECT
    ac.name,
    ac.commitment,
    node.name AS 'node.name',
    ac.primary_address AS 'node.cluster.primary_address'
FROM
    (
    SELECT
        aggr.name AS 'name',
        aggr.node_id,
        clus.primary_address,
        COALESCE(((SUM(qtree.disk_limit_mb) / aggr.size_mb) * 100),
        0) AS 'commitment'
    FROM
        cm_storage.aggregate aggr,
        cm_storage.cluster clus,
        cm_storage.qtree qtree,
        cm_storage.volume vol,
        cm_storage.vserver vserv
    WHERE
        clus.name = '${cluster_name}'
        AND vserv.name = '${vserver_name}'
        AND vserv.cluster_id = clus.id
        AND vol.vserver_id = vserv.id
        AND vol.aggregate_id = aggr.id
        AND qtree.volume_id = vol.id
        AND qtree.disk_limit_mb NOT LIKE '%NULL%'
    GROUP BY
        aggr.name ) ac,
    cm_storage.node node
WHERE
    ac.commitment <= '${commitment_threshold}'
    AND node.id = ac.node_id
ORDER BY
    ac.commitment

Dan