Need help converting SQL query to SWQL

AaidenVIII

Hello,

I am trying to convert the following SQL query into SWQL. Any assistance would be appreciated. There should be 4 columns named Last Up, Node Name, Note Date, and Note. It should only show one line for each node with the most recent note. 

SELECT InterfaceLastChange as [Change Time], REPLACE(NODE.CAPTION, '-F', '') AS [Node], Note, NodeNote.Timestamp as [Note Time], 
CONCAT('https://orion/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:', NODE.NODEID) AS [DetailsUrl]

FROM Nodes Node

INNER JOIN Interfaces NodeIf
ON Node.NodeID = NodeIf.NodeID

OUTER APPLY (
  SELECT TOP 1 * FROM [NodeNotes] WHERE NODE.NODEID = NODEID ORDER BY NodeNoteID DESC 
) NodeNote

WHERE Node.Caption LIKE 'SUS%-F' AND IfName = 'GS_DA2-P1' AND NodeIf.Status = '2'
ORDER BY InterfaceLastChange ASC

wluther

@AaidenVIII I don't have much time to work this right now, but I'll try to circle back to it tomorrow if possible.

While not exactly what you are asking, this should be a decent start for now, until I can circle back around or someone else can provide you a better response.

SELECT
n.Caption AS [Node]
,n.DetailsUrl AS [_LinkFor_Node]
,n.Interfaces.LastChange AS [Change Time]
,Notes.TimeStamp AS [Note Time]
,Notes.Note
FROM Orion.Nodes AS n

JOIN (SELECT NodeNoteID, Note, NodeID, AccountID, TimeStamp FROM Orion.NodeNotes) AS Notes ON n.NodeID=Notes.NodeID

The SWQL query above should have taken the high level parts of your SQL query and, at the very least, get you results somewhat in the right direction, hopefully.

Thank you,

-Will