03-08-2010 12:58 PM - edited 03-15-2019 09:41 PM
Hi, It has been rquested I run a query in query analyzer to produce all "in-use" phone lines at one of our remote facility's.
Problem is, it's been so long since I've done this, I cannot remember the query statement to produce the .csv dump.
Can anybody help?
thank you
Solved! Go to Solution.
03-09-2010 10:12 AM
Gotcha. If that is the case then you don't need to go to the CDRs except if you want to check if a line/DN has received or placed calls in X period of time. If you want a distribution of your directory numbers (as they are assigned to phones), then a query like this would work.
Again, you didn't specify version so I assume you are on a pre-5x release. In SQL Query Analyzer attach to the latest CCM database (i.e. CCM0300) and run a query like this:
select d.name, d.description, n.dnorpattern, dmap.numplanindex
from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid
inner join numplan as n on dmap.fknumplan=n.pkid
order by d.name,dmap.numplanindex
The above query will give you a list of devices with their associated directory numbers. The "dmap.numplanindex" corresponds to the line appearance on the phone. This will also show directory numbers multiple times if they are shared line appearances OR if you had identical DNs in different partitions (the old school method for rolling lines for example). If you want to list just unique appearances of lines you could try this:
(now this gets hairy so pay close attention):
select n.DNOrPattern as 'DN', r.name as 'partition', d.name as 'devname', d.description as 'devdescription',dmap.E164Mask
from NumPlan as n
inner join DeviceNumPlanMap as dmap on dmap.fkNumPlan = n.pkid
inner join Device as d on dmap.fkDevice = d.pkid
inner join RoutePartition as r on r.pkid = n.fkRoutePartition
where ((n.tkPatternUsage = 2 and d.name not like ('%ADP%')) and (1 =(select count(*)
from NumPlan as ndup
inner join RoutePartition as rdup on rdup.pkid=ndup.fkRoutePartition
inner join DeviceNumPlanMap as dmapdup on dmapdup.fkNumPlan = ndup.pkid
inner join Device as ddup on ddup.pkid=dmapdup.fkDevice
where ((ndup.dNOrPattern = n.dNOrPattern) and (rdup.name = r.name) and (ddup.name not like 'ADP%')))))
order by n.DNOrPattern,r.name
Basically, you will get patterns that are not shared line appearances (shared line DNs are not displayed). If you want just shared line DNs, then you can use the following query:
select distinct n.DNOrPattern,r.name
from NumPlan as n
inner join DeviceNumPlanMap as dmap on dmap.fkNumPlan = n.pkid
inner join Device as d on dmap.fkDevice = d.pkid
inner join RoutePartition as r on r.pkid = n.fkRoutePartition
where ((n.tkPatternUsage = 2 and d.name not like ('%ADP%')) and (1 != (select count(*)
from NumPlan as ndup
inner join RoutePartition as rdup on rdup.pkid=ndup.fkRoutePartition
inner join DeviceNumPlanMap as dmapdup on dmapdup.fkNumPlan = ndup.pkid
inner join Device as ddup on ddup.pkid=dmapdup.fkDevice
where ((ndup.dNOrPattern = n.dNOrPattern) and (rdup.name = r.name) and (ddup.name not like '%ADP%')))))
order by n.DNOrPattern,r.name
Again, another hairy one.
Of course, you could always dump a route plan report to get directory numbers assigned to devices, translations, meetme lines, etc. No queries needed, you can pull this from the CCMAdmin pages directly.
OK. I am exhausted after writing up those queries. Have fun while I go take a nap ;-)
HTH.
Regards,
Bill
Please remember to rate helpful posts.
Please remember to rate helpful responses and identify
03-09-2010 07:22 AM
Hi, if this is in the wrong forum please move.
If not, is anybody able to help with the query I should run against ART or CDR in order to get a database dump?
Thank you
03-09-2010 09:48 AM
No, it is the right forum. Since you are referring to "ART" I assume you have a Cisco CM version prior to 5.x, is that accurate? In either case, here are some quick pointers.
By "in use", if you are referring to active calls at the time of your query then you need to look at RTMT to get that information. I won't go into that here because "ART" is not related to real time information, so I assume your "in use" criteria is a station or line that has placed or received calls within "X" period of time.
So, with pre-5.x you can use the SQL query analyzer and use a query like this:
select cdr.origDeviceName,cdr.callingPartyNumber,cdr.originalCalledPartyNumber, cdr.dateTimeOrigination, cdr.duration
from CallDetailRecord as cdr
where cdr.origDevicename like 'SEP%'
order by cdr.origDeviceName,cdr.dateTimeOrigination
The above query will list out calls placed by phones sorted by device name and date time origination. If you prefer counts then you could probably do something like this:
select Count(cdr.dateTimeOrigination) as callcount, cdr.origDeviceName
from CallDetailRecord as cdr
where cdr.origDevicename like 'SEP%'
group by cdr.origDeviceName
This should give you a list of device names and a count for the number of calls originated by said device.
For calls received by phones you could use the same basic query syntax but substitue cdr.origDevicename with cdr.destDevicename.
The above queries are going directly to the CDR. There is no value in going to the ART/CAR database to query this data when you can access the CDRs directly. On versions after CUCM 5.x, that is a different story since the CDR files themselves are flat files and are not placed in a database. In this case, you can use the CAR database to run similar queries.
So, in the appliance model you can retrieve data from the CAR billing table from the command line using syntax like this:
admin:run sql select origdevicename,callingpartynumber,orignalcalledpartynumber,datetimeorigination,duration from car:tbl_billing_data where origdevicename like 'SEP%'
OR
admin:run sql select origdevicename, count(datetimeorigination) as callcount from car:tbl_billing_data where origdevicename like 'SEP%' group by origdevicename
In all of these examples you are getting a list of phones that are active. You would then need to do some sorting, etc. to determine what stations are not active.
HTH.
Regards,
Bill
Please remember to rate helpful posts.
Please remember to rate helpful responses and identify
03-09-2010 09:54 AM
Bill,
Thank you for your reply.
To be precise. I'm looking to produce a dump of assigned DID's to subscribers.
The reason is, we have several blocks of DID's totalling approx 1000. We downsized significantly. Therefore there are hundreds of numbers which are no longer of use to us.
We'd like to identify which DID's are currently assigned and which are not. This way we can get rid of them instead of continuing to pay for numbers not in use.
That's it. Thanks
03-09-2010 10:12 AM
Gotcha. If that is the case then you don't need to go to the CDRs except if you want to check if a line/DN has received or placed calls in X period of time. If you want a distribution of your directory numbers (as they are assigned to phones), then a query like this would work.
Again, you didn't specify version so I assume you are on a pre-5x release. In SQL Query Analyzer attach to the latest CCM database (i.e. CCM0300) and run a query like this:
select d.name, d.description, n.dnorpattern, dmap.numplanindex
from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid
inner join numplan as n on dmap.fknumplan=n.pkid
order by d.name,dmap.numplanindex
The above query will give you a list of devices with their associated directory numbers. The "dmap.numplanindex" corresponds to the line appearance on the phone. This will also show directory numbers multiple times if they are shared line appearances OR if you had identical DNs in different partitions (the old school method for rolling lines for example). If you want to list just unique appearances of lines you could try this:
(now this gets hairy so pay close attention):
select n.DNOrPattern as 'DN', r.name as 'partition', d.name as 'devname', d.description as 'devdescription',dmap.E164Mask
from NumPlan as n
inner join DeviceNumPlanMap as dmap on dmap.fkNumPlan = n.pkid
inner join Device as d on dmap.fkDevice = d.pkid
inner join RoutePartition as r on r.pkid = n.fkRoutePartition
where ((n.tkPatternUsage = 2 and d.name not like ('%ADP%')) and (1 =(select count(*)
from NumPlan as ndup
inner join RoutePartition as rdup on rdup.pkid=ndup.fkRoutePartition
inner join DeviceNumPlanMap as dmapdup on dmapdup.fkNumPlan = ndup.pkid
inner join Device as ddup on ddup.pkid=dmapdup.fkDevice
where ((ndup.dNOrPattern = n.dNOrPattern) and (rdup.name = r.name) and (ddup.name not like 'ADP%')))))
order by n.DNOrPattern,r.name
Basically, you will get patterns that are not shared line appearances (shared line DNs are not displayed). If you want just shared line DNs, then you can use the following query:
select distinct n.DNOrPattern,r.name
from NumPlan as n
inner join DeviceNumPlanMap as dmap on dmap.fkNumPlan = n.pkid
inner join Device as d on dmap.fkDevice = d.pkid
inner join RoutePartition as r on r.pkid = n.fkRoutePartition
where ((n.tkPatternUsage = 2 and d.name not like ('%ADP%')) and (1 != (select count(*)
from NumPlan as ndup
inner join RoutePartition as rdup on rdup.pkid=ndup.fkRoutePartition
inner join DeviceNumPlanMap as dmapdup on dmapdup.fkNumPlan = ndup.pkid
inner join Device as ddup on ddup.pkid=dmapdup.fkDevice
where ((ndup.dNOrPattern = n.dNOrPattern) and (rdup.name = r.name) and (ddup.name not like '%ADP%')))))
order by n.DNOrPattern,r.name
Again, another hairy one.
Of course, you could always dump a route plan report to get directory numbers assigned to devices, translations, meetme lines, etc. No queries needed, you can pull this from the CCMAdmin pages directly.
OK. I am exhausted after writing up those queries. Have fun while I go take a nap ;-)
HTH.
Regards,
Bill
Please remember to rate helpful posts.
Please remember to rate helpful responses and identify
03-09-2010 10:28 AM
Brilliant! You the man Bill. Thank you sir
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