Here a few queries I wrote
select num.DNOrPattern Ext, dev.description Owner, right(dev.name, 12) MAC, typ.name Model, loc.name Location
from dbo.device dev
inner join dbo.devicenumplanmap dvn
on dev.pkid = dvn.fkDevice
inner join dbo.Location loc
on loc.pkid = dev.fkLocation
inner join dbo.typemodel typ
on dev.tkmodel = typ.enum
inner join dbo.numplan num
on dvn.fknumplan = num.pkid
where PATINDEX('%SEP%',dev.name)=1
order by Ext
and
select right(dev.name, 12) MAC, count(dev.description) as Lines_on_mac_address
from dbo.device dev
inner join dbo.devicenumplanmap dvn
on dev.pkid = dvn.fkDevice
inner join dbo.typemodel typ
on dev.tkmodel = typ.enum
inner join dbo.numplan num
on dvn.fknumplan = num.pkid
where PATINDEX('%SEP%',dev.name)=1
group by dev.name
Enjoy......
