0 Replies Latest reply on Jun 19, 2015 12:06 PM by jbiggley

    Report for Duplicate Names - Case Dependent?!

    jbiggley

      We recently built a custom SQL report and displayed the results using the web-based reporter in Orion 2014.2 (NPM 11.0.1).  The report was intended to find possible duplicate nodes by isolating the node name where the nodes could be just the node name or FQDN.  We started off with this SQL logic.

       

      ANd

       

      SELECT SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END) AS NodeName,

             SUBSTRING(caption, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption)+1 ELSE CHARINDEX('.', caption)+1 END, 1000) AS DomainName,

                Caption,

                IP_Address,

                OwnerGroup,

                Comments

      FROM Nodes WITH (NOLOCK)

      WHERE SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END) IN (

             SELECT SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END)

             FROM Nodes WITH (NOLOCK) GROUP BY SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END)

             HAVING COUNT(*) > 1)

      AND SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END) NOT BETWEEN '1' AND '255'

      ORDER BY Caption


      And we configured the table to Group results by NodeName from the query above.  The resulting data looked like the following (minus the redacted part) -- notice that the nodes are not distinctly grouped.


      2015-06-19 12_46_16-Web-based Reporting - Case Matters - Message (HTML)v2.png


      Puzzled, I decided to force the capitalization on the NodeName to allow lowercase.  SQL isn't case dependent but maybe the grouping on the web-based reports was!


      SELECT LOWER(SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END)) AS NodeName,

             SUBSTRING(caption, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption)+1 ELSE CHARINDEX('.', caption)+1 END, 1000) AS DomainName,

                Caption,

                IP_Address,

                OwnerGroup,

                Comments

      FROM Nodes WITH (NOLOCK)

      WHERE LOWER(SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END)) IN (

             SELECT LOWER(SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END))

             FROM Nodes WITH (NOLOCK) GROUP BY LOWER(SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END))

             HAVING COUNT(*) > 1)

      AND LOWER(SUBSTRING(caption, 1, CASE CHARINDEX('.', caption) WHEN 0 THEN LEN(caption) ELSE CHARINDEX('.', caption)-1 END)) NOT BETWEEN '1' AND '255'

      ORDER BY Caption


      2015-06-19 12_47_19-Lost Sheep - Nodes - Possible Duplicatesv2.png

      Notice that the nodes are now grouped together by the common NodeName.  The change was the LOWER() function in the query.


      Has anyone else run into this before?  Does the newer version of Orion and/or NPM resolve this issue?