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

Question

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

ETL · Accepted Answer · 2014-03-14T18:36:40.937

1

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

edited Mar 14 '14 at 18:36

answered Mar 14 '14 at 15:55

ETL

6,443
1
26
47

ETL, I tried your suggestion but I am getting 'A syntax error has occurred.' I tried running as is, changing the cases to all match, and i noticed an 'a group' at the end and tried deleting the a. all resulted in the same error. any thoughts? I am running the query on a Cisco Unified Communications Manager 9.2. Perhaps this is causing the issue... I'm researching the use of union in cisco right now. – driz Mar 14 '14 at 16:12
I made an edit which might solve it. SQL-wise, this query will give you what you want, from what I gather, CUCM uses Informix for database and I'm not familiar with what SQL standard they implement in there. – ETL Mar 14 '14 at 16:37
it looks like 'NULL' is the problem. tried it a couple ways and even tried "select name from device where (name != NULL)", which errors out aswell. – driz Mar 14 '14 at 17:48
Yeah, null keyword can't be used like that on Informix - http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_0192.htm -- see new edit. – ETL Mar 14 '14 at 18:13
definitely getting closer! i can't upvote anything yet, but this is the error now: Corresponding column types must be compatible for each UNION statement. – driz Mar 14 '14 at 18:19
silly me.. fixed it. of course you are doing a count, so it's a number, not a string. – ETL Mar 14 '14 at 18:37
that worked! marking as answer. thanks ETL!!! I feel like a schmuck for not noticing the string vs number thing but my excuse is no experience in SQL! thanks again! – driz Mar 14 '14 at 18:43

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

1 Answers1