Home / server / Questions / 582130
Accepted
driz
Asked: 2014-03-15 07:36:17 +0800 CST

Nested/Joined SQL Query to Extract Phone Data From Cisco UCM Database

  • 772

I need to query numbers of phones per site for Analog, and IP phones separately. I can do it with these two queries.

IP Phones:

select count(d.name) as IP_Phones, dp.name as DevicePool 
from Device as d 
inner join DevicePool as dp on d.fkDevicePool=dp.pkid 
inner join typemodel as tm on tm.enum=d.tkmodel 
where (tm.name != 'Analog Phone' and tm.name != 'Conference Bridge'
    and tm.name != 'CTI Route Point' and tm.name != 'CTI Port'
    and tm.name != 'MGCP Station' and tm.name != 'Route List'
    and tm.name != 'H.323 Gateway' 
    and tm.name != 'Music On Hold' 
    and tm.name != 'Media Termination Point' 
    and tm.name != 'Tone Announcement Player'
    and tm.name != 'Cisco IOS Conference Bridge (HDV2)'
    and tm.name != 'Cisco IOS Software Media Termination Point (HDV2)' 
    and tm.name != 'Cisco IOS Media Termination Point (HDV2)' 
    and tm.name != 'SIP Trunk' and dp.name like '%PH%') 
group by dp.name
order by dp.name

which results in

ip_phones devicepool
========= ================
815       Site1-DP
43        Site2-DP
32        Site3-DP
890       Site4-DP

Analog Phones:

select count(d.name) as Analog_Phones, dp.name as DevicePool
from Device as d 
inner join DevicePool as dp on d.fkDevicePool=dp.pkid 
inner join typemodel as tm on tm.enum=d.tkmodel 
where (tm.name = 'Analog Phone' and dp.name like '%PH%') 
group by dp.name 
order by dp.name

which results in

analog_phones devicepool ============= ============== 12 Site1-DP 14 Site2-DP 1 Site3-DP 4 Site4-DP

What I'm looking for is a single query that results in something like this:

ip_phones analog_phones devicepool
========= ============= ==========
815       12            Site1-DP
43        14            Site2-DP
32        1             Site3-DP
890       4             Site4-DP
query

1 Answers

  1. Best Answer

    That should do it. The idea is to take both queries, union them together and then group them on device pool so you have one row per pool.

    SELECT sum(analog_phones) as analog_phones,
       sum(ip_phones) as ip_phones,
       devicepool
FROM
  (SELECT 0 AS analog_phones,
                  count(d.name) AS IP_Phones,
                  dp.name AS DevicePool
   FROM Device AS d
   INNER JOIN DevicePool AS dp ON d.fkDevicePool=dp.pkid
   INNER JOIN typemodel AS tm ON tm.enum=d.tkmodel
   WHERE (tm.name != 'Analog Phone'
          AND tm.name != 'Conference Bridge'
          AND tm.name != 'CTI Route Point'
          AND tm.name != 'CTI Port'
          AND tm.name != 'MGCP Station'
          AND tm.name != 'Route List'
          AND tm.name != 'H.323 Gateway'
          AND tm.name != 'Music On Hold'
          AND tm.name != 'Media Termination Point'
          AND tm.name != 'Tone Announcement Player'
          AND tm.name != 'Cisco IOS Conference Bridge (HDV2)'
          AND tm.name != 'Cisco IOS Software Media Termination Point (HDV2)'
          AND tm.name != 'Cisco IOS Media Termination Point (HDV2)'
          AND tm.name != 'SIP Trunk'
          AND dp.name LIKE '%PH%')
   GROUP BY dp.name
   UNION ALL SELECT count(d.name) AS Analog_Phones,
                    0 AS ip_phones,
                            dp.name AS DevicePool
   FROM Device AS d
   INNER JOIN DevicePool AS dp ON d.fkDevicePool=dp.pkid
   INNER JOIN typemodel AS tm ON tm.enum=d.tkmodel
   WHERE (tm.name = 'Analog Phone'
          AND dp.name LIKE '%PH%')
   GROUP BY dp.name) a
GROUP BY devicepool
ORDER BY devicepool
    • 1