SQL to find phones with Firmware or Directories overrides

Version 1
    This document was generated from CDN thread

    Created by: Keith Lunn on 05-04-2010 07:44:31 PM
    Hello Folks!
    Env: CUCM 6.13
    I need to write a SQL to get a list of phone devices that have a firmware override coded or and override in the External Data Locations Information: Directory URL field.
    It appears that firmware load informaiton is coded in device.specialloadinformation and now I am trying to find out which table the Directory URL override is stored in.
    In the Telecaster table, I see that there are two directories URL fields so I don't know if I am looking in the right table or if this is the right table, which of the two fields do I look in? or even - why are there 2 fields for Directories URL? - in CUCM admin - you only see one field to code a Directories URL override in.
    The Data Dictionary does not provide any further information.
    This is what I have so far:
    SELECT d.pkid, d.name, d.description, d.specialloadinformation  
    FROM device AS d
    LEFT OUTER JOIN XXX
       WHERE d.tkclass = '1' AND (d.specialloadinformation != '' OR XXX != '' );
     
    I am looking for table.field (XXX) to complete my SQL.

    Subject: RE: SQL to find phones with Firmware or Directories overrides
    Replied by: Keith Lunn on 08-04-2010 06:32:56 PM
    Solution:
     
    SELECT d.pkid, d.name, d.description, p.name AS type, d.specialloadinformation, t.directoryservicesurl2
       FROM device AS d
      INNER JOIN TypeProduct AS p ON d.tkProduct = p.enum
       LEFT OUTER JOIN telecaster as t ON d.pkid = t.fkdevice
       WHERE d.tkclass = '1' AND (d.specialloadinformation != '' OR t.directoryservicesurl2 != '' );

    Subject: RE: SQL to find phones with Firmware or Directories overrides
    Replied by: David Staudt on 08-04-2010 08:04:52 PM
    Nice!  Thanks for posting the solution.
     
    One possible caveat to keep in mind, the underlying database - Informix IDS - appears to be pretty slow in processing statements with 'JOIN' called out, especially 'OUTER JOIN's.  Since the device table can be very large, please keep a close eye on the performance impact of this query.  I suspect since the number of phones with custom stuff in telecaster is typically very small, the impact may be ok.

    Subject: RE: SQL to find phones with Firmware or Directories overrides
    Replied by: Keith Lunn on 08-04-2010 08:42:42 PM
    Yes! So far it has been tested on a smaller cluster, next I will test on our largest cluster which has 13,000+ devices.
    Normally, no phone devices should have overrides (unless it's me experimenting or another engineer...heheheh); hince this report will be used to detect mis-configured phones.