SQL – Adventures in IT

Bulk changing incorrect css for unassigned DNs

Today I was cleaning up some CSSs for a client. I came across a particular css that had been erroneously assigned as the line css for a bunch of unassigned DNs (they were precreated to show they were already in use). Of course, I went to BAT first to see if I could just update the line css of the lines, but I discovered that I couldn’t affect the unassigned DNs (even though there is an option for searching unassigned dns…)

Anyway, as you can guess, I jumped into SQL to see what I could do.

1) Determine the pkid of the erroneous CSS


admin:run sql select pkid from callingsearchspace where name="Long-Distance_CSS"
pkid
====================================
77f5f4de-f98c-1a13-728c-402623cb9ca0

2) Determine which phones are using the CSS (can compare against dependency records) using a select statement. I already know the css name is “Long-Distance_CSS”


admin:run sql select dnorpattern as dn, name as line_css from numplan inner join callingsearchspace on numplan.fkcallingsearchspace_sharedlineappear=callingsearchspace.pkid where callingsearchspace.name='Long-Distance_CSS'

This returned 34 results which matched up with the dependency records.
3) Determine the pkid of the line CSS that SHOULD be used. The name is “line_long-distance_css”


admin:run sql select pkid from callingsearchspace where name="line-long_distance-css"
pkid
====================================
6d5ae38b-bcb3-1453-a3b0-ce328d8c3699

4) update these to be the proper pkid. We know from above that the erroneous PKID is 77f5f4de-f98c-1a13-728c-402623cb9ca0 and that the correct PKID is 6d5ae38b-bcb3-1453-a3b0-ce328d8c3699


admin:run sql update numplan set fkcallingsearchspace_sharedlineappear='6d5ae38b-bcb3-1453-a3b0-ce328d8c3699' where fkcallingsearchspace_sharedlineappear='77f5f4de-f98c-1a13-728c-402623cb9ca0'
Rows: 34

As you can see, our update statement affected 34 rows, which matches the number of results from step 2. Now we verify that the erroneous CSS isn’t in use.


0 Record(s) are using Calling Search Space: Long-Distance_CSS

Looks good! This was a short example here, but SQL saved me a ton of time from manually changing 34 records. I probably could’ve exported the numbers, filtered the excel, and then import/update, but using SQL was MUCH faster.

Using SQL to clean up Call Manager pt3

See Parts 1 and 2

If you’ve been following me in this 3 part series, you know we started off with around 700 dependencies on a CSS that no longer fits our standard. It was in use by various things and we leveraged SQL to quickly, efficiently, and safely remove it from use. When we finished part 2, the only things still referencing our css were directory numbers. Well, we actually have 2 CSSs we’re going to clean up today.

As usual, we’ll begin with a count.

Continue reading “Using SQL to clean up Call Manager pt3”

Using SQL to clean up Call Manager pt2

See Part1: http://longoconsulting.eoreality.com/wp/2017/04/29/using-sql-to-clean-up-cisco-call-manager/

Previously we had around 700 dependencies on a CSS that no longer fits our standard. This CSS was in use by lines, devices, and users throughout the system. In part 1 we removed this css from the users, now we need to do the same for the devices.

Continue reading “Using SQL to clean up Call Manager pt2”

Using SQL to clean up Call Manager pt1

So, at a client of mine that we will call PRO, we had a Device-CSS which seemed to be the default css for pretty much everything, including presence subscription, even though PRO-Subscribe-CSS exists. Well the PRO-Device-css also used a lot of legacy stuff from the PRI days. I wanted to clean it up and swap things to follow the standards we had implemented, which required naming based on the line of business, location, and use.

First I modified all the templates to remove the PRO-Device-CSS usage and replace it with the appropriate css.
Next I check dependencies, assuming there couldn’t be that many things…

Continue reading “Using SQL to clean up Call Manager pt1”

CUCM Licensed Users (9.x+) using SQL

A client was facing some licensing issues with shared devices. After a discussion, we decided to create a local user account for this particular location. Using this local user account, we would assign all generic shared devices (waiting room, lobby, hallway) to this user and save a bunch of enhanced licenses by properly utilizing CUWLs.

I provided some guidance and a local admin began the process of clicking a phone, setting the user, saving and picking the next. Obviously this is pretty painful and slow. I decided to see what we could do to speed the process up.

Continue reading “CUCM Licensed Users (9.x+) using SQL”