Subversion Repositories computer_asset_manager_v2

Rev

Rev 34 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 34 Rev 40
Line 40... Line 40...
40 
   and device.removed_date is null
 40 
   and device.removed_date is null
41 
   and site.removed_date is null
 41 
   and site.removed_date is null
42 
   and client.removed_date is null
 42 
   and client.removed_date is null
43 
;
 43 
;
44 
 
 44 
 
- 
 
 45 
 
- 
 
 46 
/*
- 
 
 47 
  uuid and serial number are stored A) in the table and B) in the attrib_device table.
- 
 
 48 
  We are doing a kludge. If the value is 36 characters long, we assume it is a UUID. If
- 
 
 49 
  it is not, we assume it is a serial number
- 
 
 50 
*/
- 
 
 51 
 
- 
 
 52 
alter table temp add device_uuid varchar(36);
- 
 
 53 
 
- 
 
 54 
update temp set device_uuid = trim(device_uuid);
- 
 
 55 
update temp set device_serial = trim(device_serial);
- 
 
 56 
 
- 
 
 57 
update temp set device_uuid = null where length( device_uuid ) = 0;
- 
 
 58 
update temp set device_serial = null where length( device_serial ) = 0;
- 
 
 59 
 
- 
 
 60 
update temp set device_uuid = device_serial where length(device_serial) = 36;
- 
 
 61 
update temp set device_serial=null where length(device_serial) = 36;
- 
 
 62 
 
- 
 
 63 
update temp a join camp.attrib_device b on (a.old_device_id = b.device_id and b.attrib_id = 23 )
- 
 
 64 
  set
- 
 
 65 
     a.device_uuid = b.value
- 
 
 66 
   where a.device_uuid is null;
- 
 
 67
- 
 
 68 
update temp a join camp.attrib_device b on (a.old_device_id = b.device_id and b.attrib_id = 3 )
- 
 
 69 
  set
- 
 
 70 
     a.device_serial = b.value
- 
 
 71 
   where a.device_serial is null
- 
 
 72 
      and length( b.value ) <> 36;
- 
 
 73 
 
- 
 
 74 
update temp a join camp.attrib_device b on (a.old_device_id = b.device_id and b.attrib_id = 3 )
- 
 
 75 
  set
- 
 
 76 
     a.device_uuid = b.value
- 
 
 77 
   where a.device_uuid is null
- 
 
 78 
      and length( b.value ) = 36;
- 
 
 79 
 
- 
 
 80 
update temp set device_uuid = trim(device_uuid);
- 
 
 81 
update temp set device_serial = trim(device_serial);
- 
 
 82 
 
- 
 
 83 
update temp set device_uuid = null where length( device_uuid ) = 0;
- 
 
 84 
update temp set device_serial = null where length( device_serial ) = 0;
- 
 
 85 
 
- 
 
 86 
 
45 
/*
 87 
/*
46 
   in the old system, there was no deliniation between site and client, so we created new 'sites' with 'NOC' at the end
 88 
   in the old system, there was no deliniation between site and client, so we created new 'sites' with 'NOC' at the end
47 
   so we could have ownership and locations. So, fivestar equipment located at the NOC would have been put at the fictitious
 89 
   so we could have ownership and locations. So, fivestar equipment located at the NOC would have been put at the fictitious
48 
   site 'fivestar NOC'. Modified original data so every NOC location has NOC in the name.
 90 
   site 'fivestar NOC'. Modified original data so every NOC location has NOC in the name.
49 
   Following query puts them all in 'Colocation NOC' (Daily Data's site) without losing ownership information
 91 
   Following query puts them all in 'Colocation NOC' (Daily Data's site) without losing ownership information
Line 117... Line 159...
117 
where
 159 
where
118 
   show_as_system = 'Y';
 160 
   show_as_system = 'Y';
119 
 
 161 
 
120 
/* Now, we're ready to get some devices */
 162 
/* Now, we're ready to get some devices */
121 
truncate table device;
 163 
truncate table device;
122 
insert into device( uuid,serial,name,device_type_id,created,removed)
 164 
insert into device( uuid,serial,name,created,removed)
123 
   select
165 
   select
124 
      null,
 166 
      device_uuid,
125 
      device_serial,
 167 
      device_serial,
126 
      device_name,
 168 
      device_name,
127 
      device_type_id,
 - 
 
128 
      device_added,
 169 
      device_added,
129 
      device_removed
 170 
      device_removed
130 
   from
 171 
   from
131 
      temp
 172 
      temp
132 
      join device_type on ( temp.device_type = device_type.name )
 173 
      join device_type on ( temp.device_type = device_type.name );
- 
 
 174 
 
- 
 
 175 
 
- 
 
 176 
/* get all the device types set up */
- 
 
 177 
 
- 
 
 178 
truncate table device_device_type;
- 
 
 179 
insert into device_device_type (device_id,device_type_id )
- 
 
 180 
   select
- 
 
 181 
      device.device_id,
- 
 
 182 
      device_type_id
133 
   ;
 183 
   from
- 
 
 184 
      temp
- 
 
 185 
      join device on (temp.device_name = device.name)
- 
 
 186 
      join device_type on (temp.device_type = device_type.name);
- 
 
 187 
 
134 
 
 188 
 
135 
/* link a client owner to a site */
 189 
/* link a client owner to a site */
136 
truncate table client_site;
 190 
truncate table client_site;
137 
insert into client_site ( client_id,site_id,created, removed )
 191 
insert into client_site ( client_id,site_id,created, removed )
138 
   select
192 
   select
Line 215... Line 269...
215 
         from temp dev join temp parent on ( dev.part_of = parent.old_device_id )
 269 
         from temp dev join temp parent on ( dev.part_of = parent.old_device_id )
216 
         where dev.part_of is not null
270 
         where dev.part_of is not null
217 
         ) oldTable
 271 
         ) oldTable
218 
      join device on ( device.name = oldTable.device_name )
 272 
      join device on ( device.name = oldTable.device_name )
219 
      join device parent_device on ( parent_device.name = oldTable.parent );
 273 
      join device parent_device on ( parent_device.name = oldTable.parent );
- 
 
 274
220 
 
 275 
 
221 
/* following dumps all the data */
 - 
 
222 
/*
 - 
 
223 
select
 - 
 
224 
   site.name site,
 - 
 
225 
   site_own.name site_owner,
 - 
 
226 
   client.name device_owned_by,
 - 
 
227 
   device_type.name device_type,
 - 
 
228 
   device.name device,
 - 
 
229 
   parent.parent_name part_of
 - 
 
230 
from
 - 
 
231 
   device
 - 
 
232 
   join device_type using (device_type_id)
 - 
 
233 
   join client_device using (device_id)
 - 
 
234 
   join client using (client_id)
 - 
 
235 
   join site_device using (device_id)
 - 
 
236 
   join site using (site_id )
 - 
 
237 
   join client_site using (site_id)
 - 
 
238 
   join client site_own on (client_site.client_id = site_own.client_id)
 - 
 
239 
   left outer join (
 - 
 
240 
      select
 - 
 
241 
         device.device_id device_id,
 - 
 
242 
         device.name device_name,
 - 
 
243 
         parent.device_id parent_id,
 - 
 
244 
         parent.name parent_name
 - 
 
245 
      from
- 
 
246 
         device
- 
 
247 
         join device_device using (device_id)
- 
 
248 
         join device parent on (parent.device_id = device_device.parent_id)
 - 
 
249 
   ) parent using ( device_id )
 - 
 
250 
order by
 - 
 
251 
   client.name,
 - 
 
252 
   site.name,
 - 
 
253 
   device_type.name,
 - 
 
254 
   device.name
 - 
 
255 
;
 - 
 
256 
*/
 - 
 
257 
 
 - 
 
258 
/* now, load the attributes */
 - 
 
259 
 
 - 
 
260 
/*
 - 
 
261 
   this is a big kludge, but since it is only run once, I did not bother optimizing it.
 - 
 
262 
   basically, get all the attributes which are used by devices which are systems
 - 
 
263 
   and add them to the attribute table
 - 
 
264 
*/
 - 
 
265 
insert into attribute ( name,attribute_category_id,added,removed)
 - 
 
266 
select
 - 
 
267 
   camp.attrib.name,
 - 
 
268 
   attribute_category.attribute_category_id,
 - 
 
269 
   date(camp.attrib.added_date),
 - 
 
270 
   date(camp.attrib.removed_date)
 - 
 
271 
from
 - 
 
272 
   camp.attrib
 - 
 
273 
   join camp2.attribute_category
 - 
 
274 
where
 - 
 
275 
   attribute_category.name = 'device'
 - 
 
276 
   and camp.attrib.attrib_id in (
 - 
 
277 
      select distinct
 - 
 
278 
         attrib_id
 - 
 
279 
      from
- 
 
280 
         camp.device_attrib
 - 
 
281 
      where
- 
 
282 
         camp.device_attrib.device_id in
 - 
 
283 
         (
 - 
 
284 
            select
- 
 
285 
               device_id
- 
 
286 
            from
- 
 
287 
               camp.device
- 
 
288 
               join camp.device_type using (device_type_id)
- 
 
289 
            where
- 
 
290 
               device_type.show_as_system = 'Y'
 - 
 
291 
         )
 - 
 
292 
   )
 - 
 
293 
   and camp.attrib.name not in (
 - 
 
294 
      select
- 
 
295 
         attribute.name
- 
 
296 
      from
- 
 
297 
         attribute
- 
 
298 
         join attribute_category using (attribute_category_id)
- 
 
299 
      where
- 
 
300 
         attribute_category.name = 'device'
 - 
 
301 
   );
 - 
 
302 
 
 - 
 
303 
/*
 - 
 
304 
   now, load the values for the devices. NOTE: we should clean up data coming in since sysinfo
 - 
 
305 
   brings in values which are only slightly different for memory and cpu speed.
 - 
 
306 
*/
 - 
 
307 
 
 - 
 
308 
insert into attribute_value( attribute_id,table_id,value,added,removed)
 - 
 
309 
select
- 
 
310 
   camp2.attribute.attribute_id,
 - 
 
311 
   camp.device_attrib.device_id,
 - 
 
312 
   camp.device_attrib.value,
 - 
 
313 
   date( camp.device_attrib.added_date ) added,
 - 
 
314 
   date( camp.device_attrib.removed_date ) removed
 - 
 
315 
from
 - 
 
316 
   camp.device_attrib
 - 
 
317 
   join camp.attrib on (camp.device_attrib.attrib_id = camp.attrib.attrib_id)
 - 
 
318 
   join camp2.attribute on (camp.attrib.name = camp2.attribute.name)
 - 
 
319 
   join camp2.temp on ( camp.device_attrib.device_id = camp2.temp.old_device_id )
 - 
 
320 
   join camp2.device on (camp2.device.name = camp2.temp.device_name)
 - 
 
321 
;
 - 
 
322 
 
 - 
 
323 
/*
 - 
 
324 
   select * from view_attribute_device order by device,attribute,added limit 10;
 - 
 
325 
*/
 -