I'm working on an 8.6 CUCM cluster where the publisher is hosed and I cannot run BAT. Backup and restore from subscriber is not an option (don't ask). I need to be able export all phone details from SQL/CLI so I can import into a new CUCM cluster. I know there are a ton of posts and blogs out there already about pulling data using SQL commands but I'm having trouble understanding how to correctly use inner join to reference data between tables. Does anyone have a SQL query I can run that will allow me to export all phone details similar to what the all details in BAT would give me?
Joining data in tables is probably a bit subjective, and there's going to be more than one way to solve it.
I personally only use LEFT joins. It just works for the way I think. I look at table relationships from left to right, where my tables on the left have foreign keys to primary keys in my tables on the right.
My sql statement would then be:
run sql select d.name as devicename, d.description, n.dnorpattern, p.name as partition from devicenumplanmap as m left join device as d on m.fkdevice = d.pkid left join numplan as n on m.fknumplan = n.pkid left join routepartition as p on n.fkroutepartition = p.pkid
And now I can just keep adding tables on to the right side. For example, if I wanted to know the device pool name.
Table devicepool's pkid is referenced by table device's fkdevicepool column, and since I already have device in the list of tables, from the left, this would work just fine by amending my sql statement as such:
run sql select d.name as devicename, d.description, dp,name as devicepool, n.dnorpattern, p.name as partition from devicenumplanmap as m left join device as d on m.fkdevice = d.pkid left join numplan as n on m.fknumplan = n.pkid left join routepartition as p on n.fkroutepartition = p.pkid left join devicepool as dp on d.fkdevicepool = dp.pkid
Performance aside, you could keep doing this forever.
One trick I learned over the years is that if you have two settings on the web page, which are of the same type (E.g., Your Directory number page has several Calling Search Spaces to set for all of the forwarding options), you have to join the same table again and again, but aliased differently each time.
run sql select n.dnorpattern, p.name as partition, \
d.cfavoicemailenabled as cfa_vm, d.cfadestination, css_cfa.name as css_cfa, css_scfa.name as css_scfa \
from numplan as n \
left join routepartition as p on n.fkroutepartition = p.pkid \
left join callforwarddynamic as d on n.pkid = d.fknumplan \
left join callingsearchspace as css_cfa on d.fkcallingsearchspace_cfa = css_cfa.pkid \
left join callingsearchspace as css_scfa on d.fkcallingsearchspace_scfa = css_scfa.pkid
This isn't a very common thing I have to do when query the database, but if you're thinking about performing a bulk export of data, then you'll for sure need to know this.
So, what you may want to do is, export a phone on the new system, to get the column headers, and then write a SQL query that can export all of those columns. If there is a column you do not want to pull data on (leave blank) or if there's one you want to statically assign, you can use this trick:
run sql select col1, col2, "", "t", col5 from mytable
This will produce an output where col3 is always blank, and col4 is always 't'. If you don't do this, that's fine, you'll have to then just manipulate the data in Excel as you move it around.
Thanks for the reply Anthony. Maybe I'm just being lazy, but it would seem to me that BAT must be doing a SQL query behind the scenes to get the data anyway and there must be a query I can run to get all phone details via CLI.
Is there any other way to get the data without access to the pub?