<div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr">Don't forget the Alerting Name field. This is also Caller ID, but like in reverse.<div><br></div><div>Never having had to perform this action before, I would be curious as to how many records are "out of sync".<br></div><div><br></div><div>Try running the following, or a similar SQL query to see who isn't lining up correctly:</div><div><br></div><div><font face="monospace, monospace"><b style="">run sql select \</b></font></div><div><font color="#ff0000" face="monospace, monospace"><b>limit 25 \</b></font></div><div><font face="monospace, monospace"><b>u.telephonenumber, u.firstname, u.lastname, dn.dnorpattern, dn.alertingname, dn.alertingnameascii, la.display, la.displayascii, la.label \</b></font></div><div><font face="monospace, monospace"><b>from devicenumplanmap as la \</b></font></div><div><font face="monospace, monospace"><b>left join numplan as dn on la.fknumplan = dn.pkid \</b></font></div><div><font face="monospace, monospace"><b>left join enduser as u on right(dn.dnorpattern, 12) = u.telephonenumber \</b></font></div><div><font face="monospace, monospace"><b>where \</b></font></div><div><font face="monospace, monospace"><b>(u.telephonenumber is not null and u.firstname is not null and u.lastname is not null) and \</b></font></div><div><font color="#9900ff" face="monospace, monospace"><b>(concat(concat(u.firstname, ' '), lastname) <> dn.alertingname or concat(concat(u.firstname, ' '), lastname) <> la.display) \</b></font></div><div><font face="monospace, monospace"><b style="">order by u.lastname, u.firstname </b></font></div><div><br></div><div><b>Legend</b></div><div><font color="#ff0000">Limit</font> - Feel free to increase/decrease/remove the limit command</div><div><font color="#9900ff">Offending Records</font> - Feel free to add/remove/modify the offending record finder. I.e., This filter only matches the AD first + last against alerting and display, and also it assumes your DNs are \+1[2-9]XX[2-9]XXXXXX formatted, and your AD telephoneNumbers are +1[2-9]XX[2-9]XXXXXX formatted, so it removes the \ on the DN for lining up records with the right function, 12 characters in.</div><div><br></div><div>*The funky looking concat commands are because of two reasons: 1) the CLI parser wont let me use pipes to concat, and 2) the concat function only can concat two fields at once, so we have to run it twice to insert the space between names. Else, you might be able to pull the displayname field instead. In my case this is Lastname, Firstname, so concatenating the first and last on my own was better.</div><div><br></div><div>The output will look something like this:</div><div><br></div><div><div><font face="monospace, monospace"><b><font color="#6aa84f" style="">telephonenumber firstname lastname</font> <font color="#f1c232" style="">dnorpattern alertingname alertingnameascii</font> <font color="#0b5394" style="">display displayascii label</font></b></font></div><div><font face="monospace, monospace"><b><font color="#6aa84f">=============== ========= ================</font> <font color="#f1c232">============= ========================== ==========================</font> <font color="#0b5394">========================== ========================== ===========================</font></b></font></div></div><div><font face="monospace, monospace"><b><font color="#6aa84f" style="">+16125551212 Anthony Holloway</font> <font color="#f1c232" style="">\+16125551212 Tony Holloway Tony Holloway</font> <font color="#0b5394" style="">Tony Holloway Tony Holloway Tony Holloway - 1212</font></b></font><br></div><div><br></div><div><b>Legend</b></div><div><font color="#6aa84f">AD Fields (Once per account)</font></div><div><font color="#f1c232">DN Fields (Once per DN)</font></div><div><font color="#0b5394">Line Appearance Fields (Can exist multiple times for every appearance this DN exists on)</font></div><div><br></div><div>From there, I would bring the data into MS Excel, filter, sort, fix, etc., and then I would use the AXL SQL Toolkit to submit each update individually. Once for the DN and once for the line, per correction. This will automatically reset phones, just like a BAT operation would.<br></div><div><br></div><div>E.g.,</div><div><br></div><div><b>Update the DN fields</b></div><div><font face="monospace, monospace"><b>update numplan set alertingname = 'Anthony Holloway', alertingnameascii = 'Anthony Holloway' where dnorpattern = '\+16125551212'</b></font></div><div><br></div><div><b>Update the LA fields</b></div><div><font face="monospace, monospace"><b>update devicenumplanmap set display = 'Anthony Holloway', displayascii = 'Anthony Holloway' where fknumplan = (select pkid from numplan where dnorpattern = '\+16125551212')</b></font></div><div><br></div><div>With any approach you take, there are going to be one-off cases you'll likely need to manually handle, and not too mention, your environment may have sticking points which completely prevent you from being able to use bulk tools. E.g., Your fields are all jacked up, your DNs don't line up with your AD telephoneNumbers, etc.</div><div><br></div><div>If you think you'll have to perform this activity more than once, then I would suggest writing a program to perform the sync operation on a schedule. I think Adam was just talking about doing this exact task, with Pete's PowerShell thingy.</div><div><br></div><div><a href="https://cisco-voip.markmail.org/thread/uajz3af37ptg5cyy">https://cisco-voip.markmail.org/thread/uajz3af37ptg5cyy</a><br></div><div><br></div><div>Good luck!</div></div></div></div></div></div></div></div></div></div><br><div class="gmail_quote"><div dir="ltr">On Mon, Nov 26, 2018 at 4:33 PM Ryan Huff <<a href="mailto:ryanhuff@outlook.com">ryanhuff@outlook.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">
<div id="m_-7430044180907500588divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Helvetica,sans-serif" dir="ltr">
<p style="margin-top:0;margin-bottom:0">Hi Sam,</p>
<p style="margin-top:0;margin-bottom:0"><br>
</p>
<p style="margin-top:0;margin-bottom:0">I'm assuming (since you said Caller ID), that you are referring to "Display (Caller ID)" and the older, "ASCII Display (Caller ID)" fields. You can bulk update these fields by first, exporting the phones through, "Bulk
Administration > Phones > Export Phones > All Details". After selecting that menu navigation, you'll have the familiar GUI based query tool that will allow you to specify search limit and qualifier clauses, for the number of phones you wish to export.</p>
<p style="margin-top:0;margin-bottom:0"><br>
</p>
<p style="margin-top:0;margin-bottom:0">Once you have the resulting export file downloaded, I would typically use MS Excel (or some other type of comma deliminated parser) to open the file. Once opened, find the Display (Caller ID) field and adjust as desired
for each phone. If you wish to concatenate the the FN and LN of the user into this field, you'll need to use some "script magic" (Python, PHP, Perl, Bash ... etc) or good old fashioned copy/paste elbow grease to compile this info from elsewhere in the spreadsheet;
assuming such data exists for the line on another column within the same row; phone description, line description ... etc.</p>
<p style="margin-top:0;margin-bottom:0"><br>
</p>
<p style="margin-top:0;margin-bottom:0">All this to bring me to a closing thought, which is to verify your understanding that this would only affect internal (onnet) Caller ID and not what is presented to a called party over the PSTN. Caller ID (ANI) presented
on the PSTN is the job of a), your carrier to enter the correct data into the ANI ALI database and B), the called party's carrier to perform the appropriate CNAM dip into the ANI ALI database and ultimately present the characters to the called party's device
in the appropriate facility / contact header.<br>
</p>
<p style="margin-top:0;margin-bottom:0"><br>
</p>
<div id="m_-7430044180907500588Signature">
<div></div>
<div id="m_-7430044180907500588divtagdefaultwrapper" style="font-size:12pt;color:rgb(0,0,0);background-color:rgb(255,255,255);font-family:Calibri,Arial,Helvetica,sans-serif,"EmojiFont","Apple Color Emoji","Segoe UI Emoji",NotoColorEmoji,"Segoe UI Symbol","Android Emoji",EmojiSymbols">
Thanks,</div>
<div style="font-size:12pt;color:#000000;background-color:#ffffff;font-family:Calibri,Arial,Helvetica,sans-serif">
<br>
</div>
<div style="font-size:12pt;color:#000000;background-color:#ffffff;font-family:Calibri,Arial,Helvetica,sans-serif">
Ryan Huff, CCDP, CCNP<br>
</div>
<div style="font-size:12pt;color:#000000;background-color:#ffffff;font-family:Calibri,Arial,Helvetica,sans-serif">
Cisco Certified Network and Design Professional</div>
</div>
<br>
<br>
<div style="color:rgb(0,0,0)">
<hr style="display:inline-block;width:98%">
<div id="m_-7430044180907500588divRplyFwdMsg" dir="ltr"><font style="font-size:11pt" face="Calibri, sans-serif" color="#000000"><b>From:</b> cisco-voip <<a href="mailto:cisco-voip-bounces@puck.nether.net" target="_blank">cisco-voip-bounces@puck.nether.net</a>> on behalf of Sam Jones <<a href="mailto:jonesamu@isu.edu" target="_blank">jonesamu@isu.edu</a>><br>
<b>Sent:</b> Monday, November 26, 2018 5:08 PM<br>
<b>To:</b> <a href="mailto:cisco-voip@puck.nether.net" target="_blank">cisco-voip@puck.nether.net</a><br>
<b>Subject:</b> [cisco-voip] Best Practice or Way to bulk update Caller ID</font>
<div> </div>
</div>
<div>
<div dir="ltr">
<div class="m_-7430044180907500588x_gmail_default" style="font-family:monospace,monospace;color:#000000">
<br>
</div>
<div class="m_-7430044180907500588x_gmail_default" style="font-family:monospace,monospace;color:#000000">
I am looking for the fastest way to update the Caller ID across all our directory numbers with the first and last name of users synchronized from Active Directory.</div>
<div class="m_-7430044180907500588x_gmail_default" style="font-family:monospace,monospace;color:#000000">
I cannot find a how to do this: </div>
<div class="m_-7430044180907500588x_gmail_default" style="font-family:monospace,monospace;color:#000000">
automagically with a synchronization from Active Directory</div>
<div class="m_-7430044180907500588x_gmail_default" style="font-family:monospace,monospace;color:#000000">
Or</div>
<div class="m_-7430044180907500588x_gmail_default" style="font-family:monospace,monospace;color:#000000">
through Bulk Administration</div>
<div class="m_-7430044180907500588x_gmail_default" style="font-family:monospace,monospace;color:#000000">
<br>
</div>
<div class="m_-7430044180907500588x_gmail_default" style="font-family:monospace,monospace;color:#000000">
Any guidance to how I can accomplish this is appreciated.</div>
<div class="m_-7430044180907500588x_gmail_default" style="font-family:monospace,monospace;color:#000000">
<br>
</div>
<div>
<div dir="ltr" class="m_-7430044180907500588x_gmail_signature">
<div dir="ltr"><i></i>
<p><b>Sam Jones</b></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
_______________________________________________<br>
cisco-voip mailing list<br>
<a href="mailto:cisco-voip@puck.nether.net" target="_blank">cisco-voip@puck.nether.net</a><br>
<a href="https://puck.nether.net/mailman/listinfo/cisco-voip" rel="noreferrer" target="_blank">https://puck.nether.net/mailman/listinfo/cisco-voip</a><br>
</blockquote></div>