tag:blogger.com,1999:blog-4421651675096124175.post2651330682752080622..comments2024-03-02T21:14:43.287-08:00Comments on Unified Communications Guerrilla: CUCM SQL Queries: Listing Call Forward All SettingsWilliam Bellhttp://www.blogger.com/profile/02559158040046322600noreply@blogger.comBlogger18125tag:blogger.com,1999:blog-4421651675096124175.post-33116114836667710632018-07-03T03:38:14.058-07:002018-07-03T03:38:14.058-07:00We've had a few instances where users complain...We've had a few instances where users complain that calls are diverting to their number. I've created the following query - which allows you to search for diverts to a given number, then display all diverts on that directory number:<br /><br />run sql select n.dnorpattern as number, n.description, cfd.cfadestination as cfa, n.cfbintdestination as cfbint, n.cfbdestination as cfbext, n.cfnaintdestination as cfnaint, n.cfnadestination as cfnaext, n.cfurintdestination as cfurint, n.cfurdestination as cfurext from numplan as n inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid where 'NUMBER' in (cfd.cfadestination, cfbintdestination, cfbdestination, cfnadestination, cfnaintdestination, cfurdestination, cfurintdestination) order by n.dnorpattern<br /><br />simply replace NUMBER with the required number, the only "gotcha" with this is you cannot include the "LIKE" command - so wildcards are not supported.Dan SJKhttps://www.blogger.com/profile/12442569522952382572noreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-14024311473516109652015-08-10T10:04:44.382-07:002015-08-10T10:04:44.382-07:00Dan,
Thanks for the catch. It was a typo. Fixed.
...Dan,<br /><br />Thanks for the catch. It was a typo. Fixed.<br /><br />-Bill (@ucguerrilla)UC Guerrillahttps://www.blogger.com/profile/14454391786438461256noreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-224975235935220352015-08-07T06:06:55.144-07:002015-08-07T06:06:55.144-07:00hi folks,
we run CUCM v9.1.2 here, when entering ...hi folks,<br /><br />we run CUCM v9.1.2 here, when entering the SQL command above (adjusting the number as required):<br /><br />select n.dnorpattern, cfd.cfadestination, cfd.cfavoicemailenabled<br />from device as d<br /> inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid<br />where cfd.cfadestination like '9011%'<br />order by n.dnorpattern<br /><br />results in an error:<br /><br />Column (n) not found in any table in the query (or SLV is undefined).<br /><br />I researched and referred to the CUCM data dictionary, finding that the query needs updating to the following:<br /><br />run sql select n.dnorpattern, cfd.cfadestination from numplan as n inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid where cfd.cfadestination like 'NUMBER' order by n.dnorpattern<br /><br />where NUMBER is the number required.<br /><br />We also use extension mobility and a lot of extensions have call forward busy, no answer and/or unregistered set to the same number (often a hunt group) - a query for this is as follows:<br /><br />run sql select n.dnorpattern, n.cfbdestination from numplan as n where cfbdestination like 'NUMBER' order by n.dnorpattern<br /><br />- again, replacing NUMBER with the number required. Hope this helps others ...<br /><br /><br />Dan SJK.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-6217011486846008982015-06-22T21:28:29.249-07:002015-06-22T21:28:29.249-07:00Arras,
The best application for something like th...Arras,<br /><br />The best application for something like this is using a script or app on a *nix or Windows host that leverages the AXL/SOAP API to collect and parse the data. You could look at the toolkit that comes with CUCM. <br /><br />That will easily get you the data in XML format. Then you just need to work on a way to parse it. There are probably tools that do this. I use my own scripts that I haven't published anyway (yet, anyway). <br /><br />HTH.<br /><br />-BillUC Guerrillahttps://www.blogger.com/profile/14454391786438461256noreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-39992596820736156062015-06-19T10:54:14.530-07:002015-06-19T10:54:14.530-07:00Bill,
Thanks for the query info. I have a reques...Bill,<br /><br />Thanks for the query info. I have a request from a user to try to automate a query/data pull for the current call forward information for a specific line. I am familiar with both running a job, and CLI Query to pull the information. What I'm not sure of is if there's a way to create some automation to pull the Call forward info and then dump to an excel file or alternative for an end user's retrieval. I was thinking there may be an API/SDK tool or something to automate this. Maybe I can set up a repeatable scheduled job, and then use Filezilla (ftp client) to log in automatically and download the job results file? Any idea or help would be greatly appreciated. We are currently running CUCM 9.1(2).<br /><br />Thanks in advance,<br /><br />ArrasAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-76230791062011487122015-04-02T12:47:52.503-07:002015-04-02T12:47:52.503-07:00I assume you are asking about the class of restric...I assume you are asking about the class of restriction (COR) assigned to the phone and/or directory number on the phone. In UCM, there are a couple of different ways to provision COR. The method you apply to determine what is actually provisioned will be determined by the dial plan design approach that intended to be applied.<br /><br />So, there isn't a one size-fits-all query here. It depends on your environment. If you are using an approach where "blocking" patterns are provisioned in CSS's that are applied to the line, then you need to query for data in the numplan table. <br /><br />If, on the other hand, you take the approach of including permissive partitions at a device level (i.e. implicitly restricting call behaviors) then you need to query the device table. <br /><br />Your queries must include some parameters based on your institutional knowledge. CSS naming conventions and the like.<br /><br />HTH<br /><br />-Bill (@ucguerrilla)UC Guerrillahttps://www.blogger.com/profile/14454391786438461256noreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-87358630593874704952015-03-23T06:33:43.282-07:002015-03-23T06:33:43.282-07:00Hello everybody,
I am new in SQL and in Call man...Hello everybody, <br /><br />I am new in SQL and in Call manager.<br /><br />I am asked to give a list of who and what permissions they have for the calls.<br />I have run on of the above SQL request and I can see the permissions for "Forward" Calls. I need the same thing for normal calls. <br />I know there is a dropdown menu for the permissions, but I dont know at all wich table to use and which field to use.<br /><br />+ I also have to give a list of users with Extension mobility<br />+ a list of users with a voice mailbox.<br /><br />anybody could help me with these requests ?<br /><br />Thank you in advanceAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-21266120825093353432014-04-01T15:26:19.555-07:002014-04-01T15:26:19.555-07:00http://www.cisco.com/c/dam/en/us/td/docs/voice_ip_...http://www.cisco.com/c/dam/en/us/td/docs/voice_ip_comm/cucm/datadict/9_1_1/datadictionary_911.pdfWillnoreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-54707687670008352462014-03-25T20:08:47.584-07:002014-03-25T20:08:47.584-07:00I have never seen a db diagram for the CUCM databa...I have never seen a db diagram for the CUCM database.<br /><br />-BillUC Guerrillahttps://www.blogger.com/profile/14454391786438461256noreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-2132535859520438422014-03-25T13:27:03.555-07:002014-03-25T13:27:03.555-07:00Great work. Do you know where I can find the call ...Great work. Do you know where I can find the call manager database diagram? Ipcipherhttps://www.blogger.com/profile/05240873663298392007noreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-10398410088750464822013-12-21T14:13:39.491-08:002013-12-21T14:13:39.491-08:00Do you have a query to set CFA a comma-seperated l...Do you have a query to set CFA a comma-seperated list of numbers to the same RP ?Claus Pedersenhttp://www.it-norden.dknoreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-28595308580660607562013-07-18T10:06:52.299-07:002013-07-18T10:06:52.299-07:00Hi,
I am having an issue deleting a CSS and while...Hi,<br /><br />I am having an issue deleting a CSS and while I have used the SQL queries above, I have been able to delete two of them, I still need to delete one more. I cannot find what is using this CSS and I think it is being referenced in one of these call forwarding options.<br /><br />Forward Busy Internal<br />Forward Busy External<br />Forward No Answer Internal<br />Forward No Answer External<br />Forward No Coverage Internal<br />Forward No Coverage External<br />Forward on CTI Failure<br />Forward No Unregistered Internal<br />Forward No Unregistered External<br /><br />Is there an SQL query that I can use to see where this CSS is being used up?<br /><br />Thanks<br />Anthony. <br />Fast Tonyhttps://www.blogger.com/profile/03094620639361306013noreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-71206401424624507902013-07-18T09:42:42.913-07:002013-07-18T09:42:42.913-07:00Hi,
I am having an issue deleting a CSS and while...Hi,<br /><br />I am having an issue deleting a CSS and while I have used the SQL queries above, I have been able to delete two of them, I still need to delete one more. I cannot find what is using this CSS and I think it is being referenced in one of these call forwarding options.<br /><br />Forward Busy Internal<br />Forward Busy External<br />Forward No Answer Internal<br />Forward No Answer External<br />Forward No Coverage Internal<br />Forward No Coverage External<br />Forward on CTI Failure<br />Forward No Unregistered Internal<br />Forward No Unregistered External<br /><br />Is there an SQL query that I can use to see where this CSS is being used up?<br /><br />Thanks<br />Anthony. <br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-84536668714681222112013-03-02T01:02:28.220-08:002013-03-02T01:02:28.220-08:00This is another side to Call manager which most ad...This is another side to Call manager which most administrators dont know about, thank you very much for introducing this to me.Unknownhttps://www.blogger.com/profile/14485480922687428351noreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-35210099240976437062013-02-28T14:03:52.085-08:002013-02-28T14:03:52.085-08:00Anon,
Sure. We'll put that in the queue. Than...Anon,<br /><br />Sure. We'll put that in the queue. Thanks for the feedback!<br /><br />-Bill (@ucguerrilla)UC Guerrillahttps://www.blogger.com/profile/14454391786438461256noreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-80035595699134959082013-02-26T07:37:12.766-08:002013-02-26T07:37:12.766-08:00Any idea how to show all of the call forward confi...Any idea how to show all of the call forward configurations for a directory number and filter based on specific directory number?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-44492334549001258192012-10-02T14:09:27.135-07:002012-10-02T14:09:27.135-07:00Stefan,
You are correct. There was a syntax error...Stefan,<br /><br />You are correct. There was a syntax error in the first two queries. I am correcting now. Thanks for catching it!<br /><br />-Bill (@ucguerrilla)William Bellhttps://www.blogger.com/profile/02559158040046322600noreply@blogger.comtag:blogger.com,1999:blog-4421651675096124175.post-36757697554948097142012-10-02T05:01:53.361-07:002012-10-02T05:01:53.361-07:00Great! Like this query a lot. Well all of this se...Great! Like this query a lot. Well all of this series are great. (Now I have to learn SQL to get the most out of it)<br /><br />I have one little correction and i know this is a bit picky.<br /><br />There is a small Syntax error in the first two queries.<br /><br />I think it should be "order by n.dnorpattern"<br /><br />As a I just get known with such queries it took me a little bit to figure out what I'm doing wrong.<br /><br />I'm looking forward to see more!<br /><br />Best Regards<br />StefanAnonymoushttps://www.blogger.com/profile/00589774025282060263noreply@blogger.com