Re: Finding available LUN ID

hmarko · ‎2014-06-10

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 · ‎2014-06-11

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 · ‎2014-06-11

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

Finding available LUN ID

Get ready to power on