Need help with SQL query to get phone device count per device pool

[Jeff Levensailor]

admin:run sql select count(d.name,), dp.name as DevicePool from Device as d inner join DevicePool as dp on d.fkDevicePool=dp.pkid group by dp.name

I used the above to print out a table of devices per device pool, it works great, but I really need device count PER type...

Any help would be great!

Device Type    Device Count    Device Pool

==========  ==========    ==========

7942                     1454             HQ-1

7962                     3383             HQ-1

7942                     3343             SB-1

[Florian Kroessbacher]

Hy,

u have to join the typeproduct table on d.tkproduct= type.enum

and then select the column name, them group this by dp, name

i think this should do what u want

cheers Floh

[Jeff Levensailor]

For a non sql admin who doesn't want to pull too many records and lock up a database with 12k devices, could you tell me the exact syntax?

I would much appreciate this!

[Florian Kroessbacher]

Hy

i will check tomorrow morning for your query

on our lab

cheers

[vishnupratheek]

Hi Guys,

Is there a script for pulling out the list of only Registered Phones? I have a request where i need to pull the list of registered phones with model numbers and Device Pool information. Can you Please help me ?

Thanks in advance,
Vishnupratheek

[Florian Kroessbacher]

Hello

you can get only Registered Devices from

Real-time Information Port (RiSPort)

https://developer.cisco.com/site/sxml/discover/overview/risport/

An read all Phones from AXL.

Then Join the 2 results

cheers Floh

[vishnupratheek]

Thank You Floh. I will try this.

Regards,
VishnuPratheek

[eros200]

Vishnupratheek, did you ever get this to work?
I have SSH'd into the server and done various forms of this command to get the Registered status of the phones, but it's not by device pool.  Is there a way to join the risdb output with the sql output to get the exact report that Mr. Gupta had done BUT only counted REGISTERED (or phones with an IP Address if that's simpler since I can infer it's registered for my purposes)....

show risdb query devicepool phone 

show risdb query phoneextn phone

I just can't tell if the output key is something I can then cross-reference in the sql tables.

[Aditya Gupta · Accepted solution]

Hi Jeff

Following query should give you the desired results.

run sql select count(Device.name) Device_count, DevicePool.name Device_Pool, typemodel.name Device_Type from Device inner join DevicePool on Device.fkDevicePool=DevicePool.pkid inner join typemodel on device.tkmodel=typemodel.enum group by DevicePool.name,typemodel.name

Since the Model type information resides in the name column of typemodel table , so I have modified the query to join the typemodel table .

Try and let me know.

Regards

Aditya Gupta