Getting Device and Device Pool information

[AR7]

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

[Mohammed Khan]

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

[AR7]

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.

[Mohammed Khan · Accepted solution]

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                     

[AR7]

Thanks for the quick reply.It's working