I'm trying to pull in interface description info, break apart into individual components, and load to columns in the custom table. I basically have the following ifDesc string…
Down;SITE1-R123-R7-A^192.168.1.10@na!na#na&
…and I'm using the following query…
SELECT
SUBSTRING(InterfaceAlias, 1,
CHARINDEX(';', InterfaceAlias)) AS Status,
SUBSTRING(InterfaceAlias,
CHARINDEX(';',InterfaceAlias)+1,
CHARINDEX('^',InterfaceAlias)-CHARINDEX(';',InterfaceAlias)) AS Hostname,
SUBSTRING(InterfaceAlias,
CHARINDEX('^',InterfaceAlias)+1,
CHARINDEX('@' ,InterfaceAlias)-CHARINDEX('^',InterfaceAlias)) AS 'IP Address',
SUBSTRING(InterfaceAlias,
CHARINDEX('@' ,InterfaceAlias)+1,
CHARINDEX('!',InterfaceAlias)-CHARINDEX('@' ,InterfaceAlias)) AS 'RT Delay',
SUBSTRING(InterfaceAlias,
CHARINDEX('!',InterfaceAlias)+1,
CHARINDEX('#',InterfaceAlias)-CHARINDEX('!',InterfaceAlias)) AS 'RT Jitter',
SUBSTRING(InterfaceAlias,
CHARINDEX('#',InterfaceAlias)+1,
CHARINDEX('&',InterfaceAlias)-CHARINDEX('#',InterfaceAlias)) AS 'Packet Loss'
FROM Interfaces WHERE NodeID = 2;
This works in Solarwinds, but leaves in the ending delimiter char. If I add a valid "-1" to the range portion "
CHARINDEX('&',InterfaceAlias)-CHARINDEX('#',InterfaceAlias)-2
Solarwinds Database Manager and the preview function for the custom table error out and say the query is invalid when it is not.
Is there a known workaround for this type of issue?
