I'm attempting to create a custom table using a custom SWQL query. The query runs fine in SWQL Studio but then fails when trying to create the database resource for the custom table.
I checked C:\ProgramData\SolarWinds\InformationService\v3.0\Orion.InformationService.log to find out that it appears to be an issue where the report writer is trying to evaluate out sections of my "As" statements.
I found a work around to be to give the As definitions something generic like [Column 1] etc... and then rename them in the column properties of the table. This may be the more effective way to create the report anyways, i was just wondering if there was some way to escape out whatever text was in the As statement so that the custom table generator would not try to evaluate it as something other than a text string.
Here is the query:
SELECT Top 1000
CASE WHEN CharIndex('.',n.Caption)-1 < 5 THEN n.Caption ELSE SUBSTRING(n.Caption,1,charindex('.',n.Caption)-1) END AS [Name:String(32):Required],
n.CustomProperties.City AS [Description:String(256)],
CONCAT(n.IPAddress,'/32') AS [IP Address:Subnets(a.b.c.d/m#....):Required],
n.HardwareHealthInfos.Model AS [Model Name:String(32)],
NULL AS [Software Version:String(32)],
CONCAT(
CASE WHEN n.CustomProperties.Device_Type LIKE 'Network Security Device' THEN 'IPSEC#Is IPSEC Device#Yes |' ELSE 'IPSEC#Is IPSEC Device#No |' END,
CASE WHEN (n.Caption LIKE '%-brns%' or n.Caption LIKE '%-cors%') THEN 'Device Type#All Device Types#Wired#TermServers|'
WHEN (n.Caption LIKE '%-tsvc%') THEN 'Device Type#All Device Types#Wired#BranchSwitch|'
WHEN (n.Caption LIKE '%-brnr%') THEN 'Device Type#All Device Types#Wired#BranchRouter|'
ELSE 'add more devices' END )
--need more stuff here
as [Network Device Groups:String(100)(Type#Root Name#Name|...):Required],
CASE WHEN (n.Caption LIKE '%-brns%' or n.Caption LIKE '%-cors%' or n.Caption LIKE '%-secs%') THEN 'Radius' ELSE NULL END AS [Authentication:Protocol:String(6)],
CASE WHEN (n.Caption LIKE '%-brns%' or n.Caption LIKE '%-cors%') THEN 'xxxxxx' ELSE NULL END AS [Authentication:Shared Secret:String(128)],
CASE WHEN (n.Caption LIKE '%-brns%' or n.Caption LIKE '%-cors%') THEN 'FALSE' ELSE NULL END AS [EnableKeyWrap:Boolean(true|false)],
NULL AS [EncryptionKey:String(ascii:16|hexa:32)],
NULL AS [AuthenticationKey:String(ascii:20|hexa:40)],
NULL AS [InputFormat:String(32)],
NULL AS [SNMP:Version:Enumeration(1|2c|3)],
NULL AS [SNMP:RO Community:String(32)],
NULL AS [SNMP:Username:String(32)],
NULL AS [SNMP:Security Level:Enumeration(Auth|No Auth|Priv)],
NULL AS [SNMP:Authentication Protocol:Enumeration(MD5|SHA)],
NULL AS [SNMP:Authentication Password:String(32)],
NULL AS [SNMP:Privacy Protocol:Enumeration(DES|AES128|AES192|AES256|3DES)],
NULL AS [SNMP:Privacy Password:String(32)],
NULL AS [SNMP:Polling Interval:Integer:600-86400 seconds]
FROM Orion.Nodes n
