topic Re: Getting Device and Device Pool information in Management

Getting Device and Device Pool information

AR7 — Mon, 13 Aug 2018 08:05:45 GMT

I need help with SQL query to get the Device and Device Pool information. What I need is Device Name, Model name, Description, Product and Pool name. I am using the following query but it's returning only device and device pool name.

select a.name as device, b.name as pool from device as a left join devicepool as b on a.fkdevicepool = b.pkid

Re: Getting Device and Device Pool information

Mohammed Khan — Mon, 13 Aug 2018 10:55:59 GMT

Try below SQL Query

admin:run sql select d.name,d.description,dp.name as devicepool,m.name as devicemodel,p.name as product from device as d inner join devicepool as dp on d.fkdevicepool = dp.pkid inner join typemodel as m on m.enum = d.tkmodel inner join typeproduct as p on p.tkmodel = d.tkmodel
name                                           description                                   devicepool  devicemodel                             product                                 
============================================== ============================================= =========== ======================================= ======================================= 
Sample Device Template with TAG usage examples #FirstName# #LastName# (#Product# #Protocol#) Default     Universal Device Template               Universal Device Template               
Auto-registration Template                     #FirstName# #LastName# (#Product# #Protocol#) Default     Universal Device Template               Universal Device Template

Re: Getting Device and Device Pool information

AR7 — Mon, 13 Aug 2018 12:49:55 GMT

Thanks, the query worked for me. Just want one quick addition, I also need the Class_type information, but not able to figure out the table name.

Re: Getting Device and Device Pool information

Mohammed Khan — Tue, 14 Aug 2018 00:19:37 GMT

Here you go

admin:run sql select d.name,d.description,dp.name as devicepool,m.name as devicemodel,p.name as product,c.name as class from device as d inner join devicepool as dp on d.fkdevicepool = dp.pkid inner join typemodel as m on m.enum = d.tkmodel inner join typeproduct as p on p.tkmodel = d.tkmodel inner join typeclass as c on c.enum = d.tkclass
name                                           description                                   devicepool  devicemodel                             product                                 class                      
============================================== ============================================= =========== ======================================= ======================================= ========================== 
Sample Device Template with TAG usage examples #FirstName# #LastName# (#Product# #Protocol#) Default     Universal Device Template               Universal Device Template               Phone                      
Auto-registration Template                     #FirstName# #LastName# (#Product# #Protocol#) Default     Universal Device Template               Universal Device Template               Phone

Re: Getting Device and Device Pool information

AR7 — Tue, 14 Aug 2018 04:38:06 GMT

Thanks for the quick reply.It's working