12-29-2016 07:49 AM - edited 03-17-2019 09:03 AM
Morning, I need some assistance with an SQL Query. We are trying to determine what FXS ports have not been used and can possibly be re-purposed for other analog devices.
I see in RTMT the counters for Calls Completed, but I am trying to get this information through a SQL Query. Thanks.
Solved! Go to Solution.
12-30-2016 02:54 PM
Here is a query that will list all calls to/from an analog MGCP port on a gateway whose name matches "HQ".
run sql car select datetimestamporigination, callingpartynumber, origdevicename, originalcalledpartynumber, destdevicename, duration from tbl_billing_data where origdevicename like 'AALN%HQ%' or destdevicename like 'AALN%HQ%'
12-29-2016 08:04 AM
Forgot to mention running UCM 10.5.2.10000-5.
12-29-2016 09:15 AM
I don't believe there's any query that will give you that, CUCM doesn't keep track of whether you use a phone, or something else, in a table (I believe only EM does that). That might be something to weed out from CAR/CDR reports, see how many ports you have, and for which ones you DO see calls, for the ones you don't see calls, are likely the ones that are not being used.
12-30-2016 07:53 AM
Thanks Jaime for the quick response.
12-30-2016 02:54 PM
Here is a query that will list all calls to/from an analog MGCP port on a gateway whose name matches "HQ".
run sql car select datetimestamporigination, callingpartynumber, origdevicename, originalcalledpartynumber, destdevicename, duration from tbl_billing_data where origdevicename like 'AALN%HQ%' or destdevicename like 'AALN%HQ%'
01-03-2017 06:55 AM
Thanks Anthony. That definitely helps determine if any calls have gone through a specific port or group of ports.
I can continue to use RTMT, but for any ports that report "0" Call Completed I can use the query above. Since resetting a FXS port causes the counter to for Calls Completed go to zero, this will help verify if that indeed is the case.
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide