[SOLVED] Cisco Unified Call Manager SQL query

Cisco Unified Call Manager SQL query

I am trying to query call manager 8.5+ directly from the cli (eventually i will put this into axl)

currently my query looks like this

run sql select dp.name as Site, tm.name as Model, count(tm.name) as Total 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') group by dp.name, tm.name order by dp.name

this results in this

site           model                             total
============== ================================= =====
SITE1-NUANCE-DP Third-party SIP Device (Advanced) 1
SITE1-PHONES-DP Cisco 8945                        351
SITE1-PHONES-DP Cisco 6941                        25
SITE1-PHONES-DP Cisco 7925                        310
SITE1-PHONES-DP Cisco 7937                        3
SITE1-PHONES-DP Cisco 8961                        293
SITE1-PHONES-DP Cisco IP Communicator             1
SITE2-PHSRST-DP Cisco 7937                        1
SITE2-PHSRST-DP Cisco 6941                        1
SITE2-PHSRST-DP Cisco 8961                        143
SITE2-PHSRST-DP Cisco 8945                        21

What i really want to see though is something like this

site           total
============== =====
SITE1-PHONES-DP 300
SITE2-PHONES-DP 350

I'll be upfront here, i learned the little bit of sql i know yesterday from web searches. I dont know if you can do string manipulation or anything, because really i would like to drop the -phones-dp portion under site but it's not critical. I just need to have the query allow people bad at math to get a number. in the current state, they have to add everything up which could be DISASTROUS! Any help is greatly appreciated! thanks!

Solution

Based on what you have stated, I would try something like this:

select dp.name as Site
,count(tm.name) as Total 
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'
    ) 
group by dp.name
order by dp.name