Finding available LUN ID

[hmarko]

Hi.

My customer uses one VMFS datastore lun within a volume and the naming convention logic includes the LUN ID used to map the lun to the ESX cluster:

ex:

/vol/PRD_Datastore_23/prd_datastore_23/prd_datastore_23.lun will be mapped to all igroups starting with (EsxpX) usign LUN ID 23.

I created the following select statement to provide a list of all available LUN id from 0-254 that the operator can chose from, just need your help to see if there is more elegant way to achieve it.

select

    num

from

    (

    select

        0 as num

    union

    select

        1 as num

    union

    select

        2 as num

    union

    select

        3 as num

    union

    select

        4 as num

    union

    select

        5 as num

    union

    select

        6 as num

    union

    select

        7 as num

    union

    select

        8 as num

    union

    select

        9 as num

    union

    select

        10 as num

    union

    select

        11 as num

    union

    select

        12 as num

    union

    select

        13 as num

    union

    select

        14 as num

    union

    select

        15 as num

    union

    select

        16 as num

    union

    select

        17 as num

    union

    select

        18 as num

    union

    select

        19 as num

    union

    select

        20 as num

    union

    select

        21 as num

    union

    select

        22 as num

    union

    select

        23 as num

    union

    select

        24 as num

    union

    select

        25 as num

    union

    select

        26 as num

    union

    select

        27 as num

    union

    select

        28 as num

    union

    select

        29 as num

    union

    select

        30 as num

    union

    select

        31 as num

    union

    select

        32 as num

    union

    select

        33 as num

    union

    select

        34 as num

    union

    select

        35 as num

    union

    select

        36 as num

    union

    select

        37 as num

    union

    select

        38 as num

    union

    select

        39 as num

    union

    select

        40 as num

    union

    select

        41 as num

    union

    select

        42 as num

    union

    select

        43 as num

    union

    select

        44 as num

    union

    select

        45 as num

    union

    select

        46 as num

    union

    select

        47 as num

    union

    select

        48 as num

    union

    select

        49 as num

    union

    select

        50 as num

    union

    select

        51 as num

    union

    select

        52 as num

    union

    select

        53 as num

    union

    select

        54 as num

    union

    select

        55 as num

    union

    select

        56 as num

    union

    select

        57 as num

    union

    select

        58 as num

    union

    select

        59 as num

    union

    select

        60 as num

    union

    select

        61 as num

    union

    select

        62 as num

    union

    select

        63 as num

    union

    select

        64 as num

    union

    select

        65 as num

    union

    select

        66 as num

    union

    select

        67 as num

    union

    select

        68 as num

    union

    select

        69 as num

    union

    select

        70 as num

    union

    select

        71 as num

    union

    select

        72 as num

    union

    select

        73 as num

    union

    select

        74 as num

    union

    select

        75 as num

    union

    select

        76 as num

    union

    select

        77 as num

    union

    select

        78 as num

    union

    select

        79 as num

    union

    select

        80 as num

    union

    select

        81 as num

    union

    select

        82 as num

    union

    select

        83 as num

    union

    select

        84 as num

    union

    select

        85 as num

    union

    select

        86 as num

    union

    select

        87 as num

    union

    select

        88 as num

    union

    select

        89 as num

    union

    select

        90 as num

    union

    select

        91 as num

    union

    select

        92 as num

    union

    select

        93 as num

    union

    select

        94 as num

    union

    select

        95 as num

    union

    select

        96 as num

    union

    select

        97 as num

    union

    select

        98 as num

    union

    select

        99 as num

    union

    select

        100 as num

    union

    select

        101 as num

    union

    select

        102 as num

    union

    select

        103 as num

    union

    select

        104 as num

    union

    select

        105 as num

    union

    select

        106 as num

    union

    select

        107 as num

    union

    select

        108 as num

    union

    select

        109 as num

    union

    select

        110 as num

    union

    select

        111 as num

    union

    select

        112 as num

    union

    select

        113 as num

    union

    select

        114 as num

    union

    select

        115 as num

    union

    select

        116 as num

    union

    select

        117 as num

    union

    select

        118 as num

    union

    select

        119 as num

    union

    select

        120 as num

    union

    select

        121 as num

    union

    select

        122 as num

    union

    select

        123 as num

    union

    select

        124 as num

    union

    select

        125 as num

    union

    select

        126 as num

    union

    select

        127 as num

    union

    select

        128 as num

    union

    select

        129 as num

    union

    select

        130 as num

    union

    select

        131 as num

    union

    select

        132 as num

    union

    select

        133 as num

    union

    select

        134 as num

    union

    select

        135 as num

    union

    select

        136 as num

    union

    select

        137 as num

    union

    select

        138 as num

    union

    select

        139 as num

    union

    select

        140 as num

    union

    select

        141 as num

    union

    select

        142 as num

    union

    select

        143 as num

    union

    select

        144 as num

    union

    select

        145 as num

    union

    select

        146 as num

    union

    select

        147 as num

    union

    select

        148 as num

    union

    select

        149 as num

    union

    select

        150 as num

    union

    select

        151 as num

    union

    select

        152 as num

    union

    select

        153 as num

    union

    select

        154 as num

    union

    select

        155 as num

    union

    select

        156 as num

    union

    select

        157 as num

    union

    select

        158 as num

    union

    select

        159 as num

    union

    select

        160 as num

    union

    select

        161 as num

    union

    select

        162 as num

    union

    select

        163 as num

    union

    select

        164 as num

    union

    select

        165 as num

    union

    select

        166 as num

    union

    select

        167 as num

    union

    select

        168 as num

    union

    select

        169 as num

    union

    select

        170 as num

    union

    select

        171 as num

    union

    select

        172 as num

    union

    select

        173 as num

    union

    select

        174 as num

    union

    select

        175 as num

    union

    select

        176 as num

    union

    select

        177 as num

    union

    select

        178 as num

    union

    select

        179 as num

    union

    select

        180 as num

    union

    select

        181 as num

    union

    select

        182 as num

    union

    select

        183 as num

    union

    select

        184 as num

    union

    select

        185 as num

    union

    select

        186 as num

    union

    select

        187 as num

    union

    select

        188 as num

    union

    select

        189 as num

    union

    select

        190 as num

    union

    select

        191 as num

    union

    select

        192 as num

    union

    select

        193 as num

    union

    select

        194 as num

    union

    select

        195 as num

    union

    select

        196 as num

    union

    select

        197 as num

    union

    select

        198 as num

    union

    select

        199 as num

    union

    select

        200 as num

    union

    select

        201 as num

    union

    select

        202 as num

    union

    select

        203 as num

    union

    select

        204 as num

    union

    select

        205 as num

    union

    select

        206 as num

    union

    select

        207 as num

    union

    select

        208 as num

    union

    select

        209 as num

    union

    select

        210 as num

    union

    select

        211 as num

    union

    select

        212 as num

    union

    select

        213 as num

    union

    select

        214 as num

    union

    select

        215 as num

    union

    select

        216 as num

    union

    select

        217 as num

    union

    select

        218 as num

    union

    select

        219 as num

    union

    select

        220 as num

    union

    select

        221 as num

    union

    select

        222 as num

    union

    select

        223 as num

    union

    select

        224 as num

    union

    select

        225 as num

    union

    select

        226 as num

    union

    select

        227 as num

    union

    select

        228 as num

    union

    select

        229 as num

    union

    select

        230 as num

    union

    select

        231 as num

    union

    select

        232 as num

    union

    select

        233 as num

    union

    select

        234 as num

    union

    select

        235 as num

    union

    select

        236 as num

    union

    select

        237 as num

    union

    select

        238 as num

    union

    select

        239 as num

    union

    select

        240 as num

    union

    select

        241 as num

    union

    select

        242 as num

    union

    select

        243 as num

    union

    select

        244 as num

    union

    select

        245 as num

    union

    select

        246 as num

    union

    select

        247 as num

    union

    select

        248 as num

    union

    select

        249 as num

    union

    select

        250 as num

    union

    select

        251 as num

    union

    select

        252 as num

    union

    select

        253 as num

    union

    select

        254 as num

) ids

where

ids.num not in (

    select

        lunmap.lun_map_value

    from

        cm_storage.lunmap,

        cm_storage.igroup

    where

        igroup.id=lunmap.igroup_id

        and igroup.name REGEXP '${ESXCluster}[0-9]+'

)

[paragp]

I don't have a more elegant solution for you but here are a couple of solutions that have shorter queries

1.

SELECT

    num

FROM

    (

    SELECT @ROW := @ROW + 1 AS num FROM

(SELECT 0 UNION SELECT 1 ) t,

(SELECT 0 UNION SELECT 1 ) t1,

(SELECT 0 UNION SELECT 1 ) t2,

(SELECT 0 UNION SELECT 1 ) t3,

(SELECT 0 UNION SELECT 1 ) t4,

(SELECT 0 UNION SELECT 1 ) t5,

(SELECT 0 UNION SELECT 1 ) t6,

(SELECT 0 UNION SELECT 1 ) t7,

(SELECT @ROW:=-1) r

) ids

WHERE

ids.num <> 255

AND

ids.num NOT IN (

    SELECT

        lunmap.lun_map_value

    FROM

        cm_storage.lunmap,

        cm_storage.igroup

    WHERE

        igroup.id=lunmap.igroup_id

        AND igroup.name REGEXP '${ESXCluster}[0-9]+'

)

2. Use your inner query to create a table called ids in the playground database that comes with WFA installation

so your user input query becomes

SELECT

    num

FROM

   playground.ids

WHERE

ids.num NOT IN (

    SELECT

        lunmap.lun_map_value

    FROM

        cm_storage.lunmap,

        cm_storage.igroup

    WHERE

        igroup.id=lunmap.igroup_id

        AND igroup.name REGEXP '${ESXCluster}[0-9]+'

)

[dhruvd]

Hi hmarko,

You could also refer to the following post from a couple of months back as well:

https://communities.netapp.com/thread/34582

Hope this helps.

-Dhruv