思科统一呼叫管理器 SQL 查询

Question

我正在尝试直接从 cli 查询呼叫管理器 8.5+（最终我会将其放入 axl）

目前我的查询如下所示

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

这导致

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

我真正想看到的是这样的东西

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

我会在这里提前介绍，我昨天从网络搜索中学到了一点点 sql。我不知道您是否可以进行字符串操作或任何事情，因为我真的想将 -phones-dp 部分放在站点下，但这并不重要。我只需要让查询允许数学不好的人得到一个数字。在目前的状态下，他们必须把所有的东西加起来，这可能是灾难性的！ 任何帮助将不胜感激！谢谢！

Answer 1

根据你所说的，我会尝试这样的事情：

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