Recently we upgraded to 2019.4 and everything has been working well. The only issue we have is a query we used to be able to run no longer works. This is a query that predates me and was apparently taken from the Thwack forums although I cannot locate it. When I take the query over to SWQL , it says: Invalid column name 'C19'. Our local SQL guy says a table most likely changed/moved in the update. Below is the query and the error information. Any help is greatly appreciated. It appears the issue is in the secondary select section because when I delete the following info, it returns info but not the correct date:time : WHERE Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.TotalPackets <> 0 ORDER BY InterfaceTraffic.DateTime desc) AS [Last Seen]. I made the section I think the problem is in bold.
Query:
SELECT
Interfaces.InterfaceName as InterfaceName
, Interfaces.detailsurl as [_LinkFor_InterfaceName]
, '/Orion/images/StatusIcons/Small-' + Interfaces.StatusIcon AS [_IconFor_InterfaceName]
, Interfaces.InterfaceAlias as Caption
, Interfaces.detailsurl as [_LinkFor_Caption]
, s1.ShortDescription AS [Admin Status]
, s2.ShortDescription AS [Operational Status]
--, Interfaces.unpluggable as [Unpluggable]
--, interfaces.customproperties.Alert_on_down
, NodePortInterfaceMap.VlanID
--, PortToEndpointCurrent.VlanID
--, IPAddressCurrent.Endpoint.MACAddress
--, IPAddressCurrent.IPAddress
--, IPAddressCurrent.DNSNames.DNSName
, (SELECT TOP 1 InterfaceTraffic.DateTime AS [ColumnA] FROM Orion.NPM.InterfaceTraffic WHERE Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.TotalPackets <> 0 ORDER BY InterfaceTraffic.DateTime desc) AS [Last Seen]
FROM Orion.NPM.Interfaces
LEFT OUTER JOIN Orion.StatusInfo s1 on Interfaces.AdminStatus = s1.StatusID
LEFT OUTER JOIN Orion.StatusInfo s2 on Interfaces.OperStatus = s2.StatusID
LEFT OUTER JOIN Orion.UDT.Port on Port.NodeID = Interfaces.NodeID AND Port.Name = Interfaces.InterfaceName
LEFT OUTER JOIN Orion.UDT.PortToEndpointCurrent on Port.PortID = PortToEndpointCurrent.PortID AND ConnectionType = 1
LEFT OUTER JOIN Orion.NodePortInterfaceMap on Interfaces.InterfaceIndex = NodePortInterfaceMap.IfIndex AND NodePortInterfaceMap.PortType = 1 AND NodePortInterfaceMap.NodeID = ${NodeID}
LEFT OUTER JOIN Orion.UDT.IPAddressCurrent on PortToEndpointCurrent.EndpointID = IPAddressCurrent.EndpointID
WHERE Interfaces.NodeID = ${NodeID}
ORDER BY Interfaces.Index
SWQL error message:
2020-03-09 14:49:51,328 [103] ERROR SolarWinds.InformationService.DataProviders.SqlQueryRelation - SWQL Studio (null) An SqlException occurred.
Message: Invalid column name 'C19'.
Query:
SET DATEFIRST 7;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT [T1].[C14] AS C1, [T1].[C16] AS C2, '/Orion/images/StatusIcons/Small-' + [T1].[C17] AS C3, [T1].[C18] AS C4, [T1].[C16] AS C5, [T2].[ShortDescription] AS C6, [T3].[ShortDescription] AS C7, [T4].[VlanId] AS C8,
(
(SELECT TOP (1) DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T5].[DateTime]) AS C10
FROM
(
SELECT it.*
,CASE WHEN ISNULL(i.InBandwidth,0) = 0 THEN 0 ELSE ISNULL(it.In_Averagebps,0) / i.InBandwidth * 100 END InPercentUtil
,CASE WHEN ISNULL(i.OutBandwidth,0) = 0 THEN 0 ELSE ISNULL(it.Out_Averagebps,0) / i.OutBandwidth * 100 END OutPercentUtil
FROM [dbo].[InterfaceTraffic] it
LEFT JOIN [dbo].[Interfaces] i ON it.InterfaceID = i.InterfaceID
) AS T5
WHERE [T1].[C19] = [T5].[InterfaceID] AND [T5].[In_TotalPkts] + [T5].[Out_TotalPkts] <> 0
ORDER BY [T5].[DateTime] DESC)
) AS C9
FROM (SELECT [T6].[AdminStatus] AS C11, [T6].[OperStatus] AS C12, [T6].[NodeID] AS C13, [T6].[InterfaceName] AS C14, [T6].[InterfaceIndex] AS C15, '/Orion/View.aspx?NetObject=I:' + CONVERT(nvarchar(max),[T6].[InterfaceID]) AS C16, [T6].[StatusLED] AS C17, [T6].[InterfaceAlias] AS C18, [T6].[InterfaceIndex] AS C20
FROM dbo.Interfaces AS T6
LEFT JOIN dbo.UDT_Ports AS T7 ON [T7].[NodeID] = [T6].[NodeID] AND [T7].[Name] = [T6].[InterfaceName]
LEFT JOIN dbo.UDT_PortToEndpointCurrent AS T8 ON [T7].[PortID] = [T8].[PortID] AND [T8].[ConnectionType] = 1
LEFT JOIN dbo.UDT_IPAddressCurrent AS T9 ON [T8].[EndpointID] = [T9].[EndpointID]
WHERE [T6].[NodeID] = [T6].[NodeID]) AS T1
LEFT JOIN dbo.StatusInfo AS T2 ON [T1].[C11] = [T2].[StatusId]
LEFT JOIN dbo.StatusInfo AS T3 ON [T1].[C12] = [T3].[StatusId]
LEFT JOIN dbo.NodePortInterfaceMap AS T4 ON [T1].[C15] = [T4].[IfIndex] AND [T4].[PortType] = 1 AND [T4].[NodeID] = [T1].[C13]
ORDER BY [T1].[C20] ASC
2020-03-09 14:49:51,328 [103] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception running query:
SELECT
Interfaces.InterfaceName as InterfaceName
, Interfaces.detailsurl as [_LinkFor_InterfaceName]
, '/Orion/images/StatusIcons/Small-' + Interfaces.StatusIcon AS [_IconFor_InterfaceName]
, Interfaces.InterfaceAlias as Caption
, Interfaces.detailsurl as [_LinkFor_Caption]
, s1.ShortDescription AS [Admin Status]
, s2.ShortDescription AS [Operational Status]
--, Interfaces.unpluggable as [Unpluggable]
--, interfaces.customproperties.Alert_on_down
, NodePortInterfaceMap.VlanID
--, PortToEndpointCurrent.VlanID
--, IPAddressCurrent.Endpoint.MACAddress
--, IPAddressCurrent.IPAddress
--, IPAddressCurrent.DNSNames.DNSName
, (SELECT TOP 1 InterfaceTraffic.DateTime AS [ColumnA] FROM Orion.NPM.InterfaceTraffic WHERE Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.TotalPackets <> 0 ORDER BY InterfaceTraffic.DateTime desc) AS [Last Seen]
FROM Orion.NPM.Interfaces
LEFT OUTER JOIN Orion.StatusInfo s1 on Interfaces.AdminStatus = s1.StatusID
LEFT OUTER JOIN Orion.StatusInfo s2 on Interfaces.OperStatus = s2.StatusID
LEFT OUTER JOIN Orion.UDT.Port on Port.NodeID = Interfaces.NodeID AND Port.Name = Interfaces.InterfaceName
LEFT OUTER JOIN Orion.UDT.PortToEndpointCurrent on Port.PortID = PortToEndpointCurrent.PortID AND ConnectionType = 1
LEFT OUTER JOIN Orion.NodePortInterfaceMap on Interfaces.InterfaceIndex = NodePortInterfaceMap.IfIndex AND NodePortInterfaceMap.PortType = 1 AND NodePortInterfaceMap.NodeID = ${NodeID}
LEFT OUTER JOIN Orion.UDT.IPAddressCurrent on PortToEndpointCurrent.EndpointID = IPAddressCurrent.EndpointID
WHERE Interfaces.NodeID = ${NodeID}
ORDER BY Interfaces.Index
RETURN XML RAW
2020-03-09 14:49:51,328 [103] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception for Operation: <s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">
<s:Header>
<a:Action s:mustUnderstand="1">schemas.solarwinds.com/.../a:Action>
<a:MessageID>urn:uuid:b9905a33-29d1-4db0-afd2-a4bcf298d745</a:MessageID>
<a:ReplyTo>
<a:Address>www.w3.org/.../a:Address>
</a:ReplyTo>
<i18n xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<Culture>en-US</Culture>
</i18n>
<SwisSettings xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<AppendErrors>true</AppendErrors>
<ApplicationTag>SWQL Studio</ApplicationTag>
<DataProviderTimeout>PT30S</DataProviderTimeout>
</SwisSettings>
<SwisProtocolVersion xmlns="schemas.solarwinds.com/.../SwisProtocolVersion>
<IsBase64EncodingAccepted xmlns="schemas.solarwinds.com/.../IsBase64EncodingAccepted>
<a:To s:mustUnderstand="1">net.tcp://swpoll1.ad.wayne.edu:17777/SolarWinds/InformationService/v3/Orion/ad</a:To>
</s:Header>
<s:Body>
<QueryXml xmlns="http://schemas.solarwinds.com/2007/08/informationservice">
<query>SELECT
Interfaces.InterfaceName as InterfaceName
, Interfaces.detailsurl as [_LinkFor_InterfaceName]
, '/Orion/images/StatusIcons/Small-' + Interfaces.StatusIcon AS [_IconFor_InterfaceName]
, Interfaces.InterfaceAlias as Caption
, Interfaces.detailsurl as [_LinkFor_Caption]
, s1.ShortDescription AS [Admin Status]
, s2.ShortDescription AS [Operational Status]
--, Interfaces.unpluggable as [Unpluggable]
--, interfaces.customproperties.Alert_on_down
, NodePortInterfaceMap.VlanID
--, PortToEndpointCurrent.VlanID
--, IPAddressCurrent.Endpoint.MACAddress
--, IPAddressCurrent.IPAddress
--, IPAddressCurrent.DNSNames.DNSName
, (SELECT TOP 1 InterfaceTraffic.DateTime AS [ColumnA] FROM Orion.NPM.InterfaceTraffic WHERE Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.TotalPackets <> 0 ORDER BY InterfaceTraffic.DateTime desc) AS [Last Seen]
FROM Orion.NPM.Interfaces
LEFT OUTER JOIN Orion.StatusInfo s1 on Interfaces.AdminStatus = s1.StatusID
LEFT OUTER JOIN Orion.StatusInfo s2 on Interfaces.OperStatus = s2.StatusID
LEFT OUTER JOIN Orion.UDT.Port on Port.NodeID = Interfaces.NodeID AND Port.Name = Interfaces.InterfaceName
LEFT OUTER JOIN Orion.UDT.PortToEndpointCurrent on Port.PortID = PortToEndpointCurrent.PortID AND ConnectionType = 1
LEFT OUTER JOIN Orion.NodePortInterfaceMap on Interfaces.InterfaceIndex = NodePortInterfaceMap.IfIndex AND NodePortInterfaceMap.PortType = 1 AND NodePortInterfaceMap.NodeID = ${NodeID}
LEFT OUTER JOIN Orion.UDT.IPAddressCurrent on PortToEndpointCurrent.EndpointID = IPAddressCurrent.EndpointID
WHERE Interfaces.NodeID = ${NodeID}
ORDER BY Interfaces.Index
RETURN XML RAW</query>
<parameters>
</parameters>
</QueryXml>
</s:Body>
</s:Envelope>
2020-03-09 14:49:51,328 [103] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'C19'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at SolarWinds.InformationService.DataProviders.SqlQueryRelation.<GetEnumerator>d__8.MoveNext()
at SolarWinds.Data.Query.PhysicalQueryPlan.ProviderPassThroughScanOp.<GetEnumeratorInternal>d__5.MoveNext()
at SolarWinds.Data.Query.QueryStatisticsEnumerator.MoveNext()
at SolarWinds.Data.Query.PhysicalQueryPlan.ProjectOp.<GetEnumeratorInternal>d__7.MoveNext()
at SolarWinds.Data.Query.QueryStatisticsEnumerator.MoveNext()
at SolarWinds.Data.Query.PhysicalQueryPlan.PhysicalQueryPlan.<GetEnumerator>d__4.MoveNext()
at SolarWinds.InformationService.Core.InformationService.RowsCountingQueryResultReader.MoveNext()
at SolarWinds.InformationService.Serialization.XmlResponseSerializer..ctor(IQueryResultReader reader, IQueryExecutionPlan queryExecutionPlan, Boolean includeQueryPlan, IQueryStatisticsContext statistics, I18n i18N)
at SolarWinds.InformationService.Serialization.XmlSerializer.Serialize(String query, IQueryExecutionPlan queryExecutionPlan, IQueryResultReader reader, Boolean isBase64EncodingAccepted, IMessageFormatterStrategy messageFormatterStrategy)
at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer, IMessageFormatterStrategy messageFormatterStrategy)
ClientConnectionId:c9c3dd8e-b885-4574-9b8d-e969994106e1
Error Number:207,State:1,Class:16
Pop-Up error:
Thanks in advance. The techs use this to see the last time a port was used to see what they can unplug to make a new port active.
