wfa query

venkate_SAN · ‎2017-04-17

Anyone can tell me how to add query for listing raid group name,

i am creating aggr expansion workflow so need to list the existing aggr group.

Shalini · ‎2017-04-18

Hi Venkat,

Follow the below steps, it will help you to pull the raid details of existing aggregates.

In dictionary tab
1. Clone the dictionary ‘Aggregate’ in the storage scheme
2. Add new row to the cloned dictionary with the below details
Name : raidType
Type : string
Sting length : 128

Use the below query in Cache query

SELECT
    aggr.aggrId AS id,
    obj.objName AS name,
    aggr.dfKBytesTotal/1024 AS total_size_mb,
    aggr.dfKBytesUsed/1024 AS used_size_mb,
    aggr.dfKBytesAvail/1024 AS available_size_mb,
    COUNT (vol.volId) AS volume_count,
    aggr.aggrStatus AS STATUS,
    aggr.hostId AS array_id,
    aggr.aggrBlkType as block_type,
    aggr.aggrState as state,
    (IF (aggr.isFlashPoolEnabled='Yes') THEN 1
    ELSE 0 ENDIF) AS is_hybrid,
    aggr.raidType as raidType
FROM
    dfm.aggrs aggr
LEFT JOIN
    (
        SELECT
            vol.volId,
            vol.aggrId
        FROM
            dfm.volumes vol
        JOIN
            dfm.objects
                ON vol.volId = objects.objId
        WHERE
            objects.objDeleted IS NULL
    ) as vol
        ON vol.aggrId = aggr.aggrId
JOIN
    dfm.objects obj
        ON obj.objId = aggr.aggrId
WHERE
    aggr.snapshotsourceid = 0
    AND obj.objDeleted IS NULL
    AND (
        (
            obj.objFlags & 0x20000000000000
        ) = 0
    )
GROUP BY
    aggr.aggrid,
    name,
    status,
    total_size_mb,
    used_size_mb,
    available_size_mb,
    array_id,
    block_type,
    state,
    is_hybrid,
    raidType

venkate_SAN · ‎2017-04-19

Shalini,

Thank you for the update, i have added new entry in dictionary and cache query ,can you help me to list rg name in workflow for a aggr.

Shalini · ‎2017-04-20

Hi Venkat,

Please modify the user input query in your workflow referring to the new table in the wfa database ( table created using new dictionary entry ) which has raid type column in it.

venkate_SAN · ‎2017-04-20

Shalini,

i could bring Raid type by using default dictionary, i need the RG details of the aggr like rg0,rg1,rg2,rg3.

SELECT
aggregate_copy.name AS aggr,
aggregate_copy.raidType AS RG
FROM
storage.aggregate_copy as aggregate_copy,
storage.array as array
Where
array.id = aggregate_copy.array_id
AND array.name = '${FilerName}'
ORDER BY
aggregate_copy.name

Shalini · ‎2017-04-24

Hi Venkat,

To get the raid Group details, we need to create the new dictionary.
Please create the dictionary and cache query with below details and use this dicitonary in your workflow.

Dictionary name: Raid_Groups
Rows : id
       name
    plexid
    aggrid
    aggrname

Cache query :
SELECT
    raidGroups.rgId AS id,
    raidGroups.rgName AS name,
    raidGroups.plexId AS plexid,
    aggrs.aggrId AS aggrid,
    obj.objName AS aggrname
FROM
    dfm.raidGroups,
    dfm.aggrs,
    dfm.plexes,
    dfm.objects
WHERE
    raidGroups.plexId = plexes.plexId
     AND
    plexes.aggrId = aggrs.aggrId
     AND
    aggrs.aggrId = objects.objId