Custom widget: Show AP join/leave events from WLC in node view

duck1

Hi everyone,

I'm trying to create a custom widget that shows a log of AP (Access Point) state changes when viewing the node details page. We monitor our APs as ICMP-only nodes so we can draw them on network maps.

In SQL, I managed to correlate the necessary tables to track when APs connect or disconnect from WLCs. Here's a simplified version of the query I use:

WITH CS_StateChanges AS (
    SELECT
        CS.[Timestamp],
        CS.Available,
        LAG(CS.Available) OVER (PARTITION BY AP.[Index] ORDER BY CS.[Timestamp]) AS PrevAvailable,
        CS.Weight,
        AP.Name       AS AP_Name,
        AP.IPAddress  AS AP_IPAddress,
        WLC.Name      AS WLC_Name,
        WLC.IPAddress AS WLC_IPAddress
    FROM Wireless_AccessPoints_CS_cur AS CS
    JOIN Wireless_AccessPoints          AS AP  ON AP.[Index] = CS.[Index]
    LEFT JOIN solarwindsorion.dbo.Wireless_Controllers AS WLC ON CS.NodeID = WLC.NodeID
    WHERE
        AP.IPAddress IN (
            SELECT n.IP_Address
            FROM solarwindsorion.dbo.NodesData AS n
            INNER JOIN solarwindsorion.dbo.NodesCustomProperties AS cp 
                ON n.NodeID = cp.NodeID
            WHERE cp.Servicio = 'AP'
        )
        -- AND CS.[Timestamp] >= DATEADD(DAY, -7, GETDATE())
        AND CS.Weight = 300
)
SELECT TOP 300 *
FROM   CS_StateChanges
WHERE  Available <> PrevAvailable
ORDER  BY [Timestamp] DESC;

The goal is to display only the logs related to the AP node currently being viewed. I tried the following filters in SQL:

Option 1:

WHERE
        AP.IPAddress = (
            SELECT n.IP_Address
            FROM   solarwindsorion.dbo.NodesData n
            WHERE  n.NodeID = ${NodeID}
          )

Option 2:

WHERE
        AP.IPAddress = ${IP_Address}

However, this doesn’t work when I try to use it in a SWQL-based widget, and I realize now that the issue is twofold:

1. SWQL doesn’t support WITH or LAG() functions.

2. The tables used in SQL (Wireless_AccessPoints_CS_cur) don’t exist in SWQL — the entity structure is completely different.

I checked the SDK and tried to find an equivalent to Wireless_AccessPoints_CS_cur, but I can’t locate the same dataset. I suspect it might be under something like Orion.Packages.Wireless.HistoricalAccessPoints, but I’m not sure if it fully matches the data from SQL. I'm particularly trying to track changes in the Available field, which seems to reflect whether the AP is currently enrolled or not in a WLC.

Has anyone successfully recreated this type of AP controller association log in SWQL?
I’d really appreciate any help mapping these tables or finding a way to replicate the LAG logic in SWQL.

Thanks in advance!