Solved: ART database dump

rossporubski · ‎03-08-2010

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

William Bell · ‎03-09-2010

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.

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

View solution in original post

rossporubski · ‎03-09-2010

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

William Bell · ‎03-09-2010

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.

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

rossporubski · ‎03-09-2010

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

William Bell · ‎03-09-2010

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.

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

rossporubski · ‎03-09-2010

Brilliant! You the man Bill. Thank you sir