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.
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
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?
