Hi everyone, I hope this SWQL query gets helpful to you. You can modify it as you wish and need.
For example, you can use TOP n clause,
or the ORDER BY n clause, or remove Properties from SELECT
or you can add a WHERE clauses, for example WHERE (ConnectionStatus) = 2
SELECT AgentId, Nodes.NodeName, Nodes.DetailsUrl AS [_LinkFor_NodeName], Agent.Uri AS [_LinkFor_AgentID], Agent.NodeId, DNSName, Agent.IP, ConnectionStatus, ConnectionStatusMessage, ConnectionStatusTimestamp, AgentStatus, AgentStatusMessage, AgentStatusTimestamp, IsActiveAgent, Mode, AgentVersion, AutoUpdateEnabled, Agent.OrionIdColumn, PassiveAgentHostname, PassiveAgentPort, RegisteredOn, Agent.ResponseTime, OSType, OSVersion, OSLabel, NetFrameworkRelease
FROM Orion.AgentManagement.Agent AS Agent --You can use another name, but I named Agent, to know that all the info that have 'Agent.property' is related to this Entity, for example: Agent.NodeID, Agent.Uri
INNER join Orion.Nodes AS Nodes --This is a property (also an Entity) inside the Entity Orion.AgentManagement.Agent, I used to extract or grab to this custom query the NodeName from Orion.Nodes Entity, to specify that came from another entity, I named Nodes, examples: Nodes.NodeName, Nodes.DetailsUrl
on Agent.NodeID = Nodes.NodeID
ORDER BY (RegisteredOn) DESC
Nodes.DetailsUrl AS [_LinkFor_NodeName] means like in the screenshot (Table Widget Example), the query will create a link for the NodeName in the table to redirect you to manage that node that you select or click using the Nodes.DetailsUrl, the color text will be changed to blue to let you know that is a link.
The INNER JOIN is the UNION of related entities (chain icon)
In the Entity Orion.AgentManagement.Agent there is a Property (chain icon) named Node (Orion.Nodes) that is also another Entity and, both are related.
Table Widget Example
Let me know if it helps.