4 Replies Latest reply on Feb 24, 2016 8:06 AM by ironclad757

    Converting uppercase to lowercase hostnames

    mr.e

      We have quite a bit of nodes that are in UPPER CASE and also a lot of nodes in LOWER CASE  in our Orion site.  We would like to convert the case of these, to standardize the look in our reports.  This is really cosmetic, but some of our users don't care for the mix of cases.  So, is there a safe SQL script o change the case on our hostnames from upper to lower?  Thanks in advance

        • Re: Converting uppercase to lowercase hostnames
          Lawrence Garvin

          This can be done with a single SQL statement on the appropriate column(s).

           

          The statement would be:

          UPDATE columnName SET columnName = LOWER(columnName)

           

          I would suggest using

          SELECT columnName, LOWER(columnName) AS LowerCaseName

          before launching the UPDATE so you can visually confirm that you're getting what you want.

           

          You can also do it with PowerShell and the Orion SDK if you need more granularity or aren't comfortable working direct with SQL and the database table(s).

           

          Also, something to be aware of. The mixed case scenario is typically an artifact of either the actual device name configured on the device and/or configuration of the device name in the reverse DNS lookup tables. Verifying that naming conventions on devices and DNS all use lower case is important also, if that's the preferred presentation.

          1 of 1 people found this helpful
            • Re: Converting uppercase to lowercase hostnames
              mr.e

              Lawrence,

               

              Many thanks for your advice on how to get this corrected.  By the way, do the SQL commands you provided need to be run only against the Nodes table?  Or do these need to be run against other tables as well.  I thought I'd ask before taking the plunge.    Again, many thanks!!!

                • Re: Converting uppercase to lowercase hostnames
                  Lawrence Garvin

                  That I don't know off the top of my head, but I did notice that my dbo.Nodes table has more than one column with a name value in it, so at a minimum you may have multiple columns in dbo.Nodes that require remediation. I see CAPTION, DNS, and SYSNAME. Depends on how radical you want to get.

                   

                  By all rights, name attributes should not be stored in any other tables (the data would be redundant, and name is not a key field).

                   

                  Another way to consider this, if the issue is merely presentation, the display name uses the CAPTION column as its source. (This is what allows you to edit the display name without changing the stored canonical name.) As a demonstration, go to an Edit Node screen. The textbox for the name that is editable is the CAPTION column. Change that and save. Go back to the Node Details page and look in the Node Details resource. Note the individual values for "DNS", "System Name", and compare that to the (now changed) display name at the top of the page.

                   

                  I'd suggest starting by only changing the CAPTION column, and see if that resolves the grumbling.

                • Re: Converting uppercase to lowercase hostnames
                  ironclad757

                  I had issues with the UPDATE statement.  The correct use is

                   

                  UPDATE [dbo].[Nodes]

                  SET columnName = UPPER(columnName)

                   

                  Thanks again for the answer.