Hello Everyone,
We're trying to build a resource that will show the last note (orion.nodenotes) left on a node. I can get all the filtering for status/custom properties required the problem I'm running into is when it returns the results if I aggregate the data for the notes (it shows all notes by default) then I can only get it to show the first or last note entry by alphabetical order. If I sort it via timestamp it doesn't consider the notes field as part of it as its a separate column. Query is below would appreciate any input/ideas.
SELECT [data].[DisplayName] AS [DisplayName],[data].[InstanceSiteId] AS [InstanceSiteId],[data].[Status],[data].[Interfaces].[FullName],[data].[CustomProperties].[A], [data].IPAddress, [data].[CustomProperties].[B], [data].[CustomProperties].[C], AddHour(-7,[data].LastSystemUpTimePollUtc) as [LastSystemUpTimePoll], [data].ChildStatus, [data].DetailsUrl, n.Note, n.NodeID, AddHour(-7,n.TimeStamp) as [LastNoteTime]
FROM orion.nodes AS data
LEFT JOIN orion.NodeNotes n ON data.NodeID = n.NodeID
WHERE
((([data].[Status]) = (12)) OR (([data].[Status]) = (0)) OR (([data].[Status]) = (9)) OR (([data].[Status]) = (11)) OR (([data].[Status]) = (2)) AND (([data].[CustomProperties].[A]) = ('X')))
GROUP BY [data].[DisplayName],[data].[InstanceSiteId],[data].[Status],[data].[Interfaces].[FullName],[data].[CustomProperties].[A], [data].IPAddress, [data].[CustomProperties].[B], [data].[CustomProperties].[C], [data].LastSystemUpTimePollUtc, [data].ChildStatus, [data].DetailsUrl, n.Note, n.NodeID, n.TimeStamp
