Create custom undp table

superfly

Hi

I'm trying to create a custom table for HSRP monitoring that I'd like to look something like this

HSRP address is polled via undp as is the state. Is anyone able to assist with SWQL code for this?

Thanks.

jeremymayfield

To build a custom HSRP status matrix table in SolarWinds Orion Platform using UNDP-polled HSRP address and state, you will need a SWQL query that:

1. Pulls the UNDP values for:
   • HSRP Virtual IP (HSRP address)
   • HSRP State (Active / Standby / Listen)
2. Groups by Virtual IP
3. Pivots router names into columns

Because SWQL does not support native PIVOT, we simulate it using conditional aggregation.

Assumptions: You have two UNDP custom pollers:

• HSRP_Address
• HSRP_State

And they are assigned at the Node level. If they are interface-level pollers, the query will require modification.

SWQL Query - HSRP Matrix Table:

SELECT
addr.CurrentValue AS [HSRP Address],

MAX(CASE WHEN n.Caption = 'router1' THEN state.CurrentValue END) AS [router1],

MAX(CASE WHEN n.Caption = 'router2' THEN state.CurrentValue END) AS [router2],

MAX(CASE WHEN n.Caption = 'router3' THEN state.CurrentValue END) AS [router3],

MAX(CASE WHEN n.Caption = 'router4' THEN state.CurrentValue END) AS [router4],

MAX(CASE WHEN n.Caption = 'router5' THEN state.CurrentValue END) AS [router5],

MAX(CASE WHEN n.Caption = 'router6' THEN state.CurrentValue END) AS [router6]

FROM Orion.Nodes n

JOIN Orion.NPM.CustomPollerStatistics addr
ON n.NodeID = addr.NodeID

JOIN Orion.NPM.CustomPollerStatistics state
ON n.NodeID = state.NodeID

JOIN Orion.NPM.CustomPollers addrDef
ON addr.CustomPollerID = addrDef.CustomPollerID

JOIN Orion.NPM.CustomPollers stateDef
ON state.CustomPollerID = stateDef.CustomPollerID

WHERE
addrDef.UniqueName = 'HSRP_Address'
AND stateDef.UniqueName = 'HSRP_State'

GROUP BY addr.CurrentValue

ORDER BY addr.CurrentValue

What this does:

• - Groups results by HSRP Virtual IP

• - Displays one row per VIP

• - Each router becomes a column

• - Displays its HSRP state (Active / Standby / Listen)

If Your Pollers Are Interface-Based

If UNDP is attached to interfaces instead of nodes, replace:

Orion.Nodes

with:

Orion.NPM.Interfaces

and join appropriately on InterfaceID.

How to Deploy

1. Go to:
   • Settings → All Settings → Manage Custom Queries
2. Create new SWQL Query
3. Test in SWQL Studio first (recommended)
4. Add to Modern Dashboard or Classic View

Optional Enhancements

1. Highlight Active Router

You can add HTML formatting:

CASE     WHEN state.CurrentValue = 'Active Router'    THEN CONCAT('<span style="color:green;font-weight:bold;">', state.CurrentValue, '</span>')    ELSE state.CurrentValueEND

(Enable “Allow HTML” in table resource.)

2. Make Routers Dynamic (Advanced)

Instead of hardcoding router1–router6, you can:

• Filter by Custom Property (e.g., HSRPCluster = 'Core')
• Keep vertical layout (one row per router per VIP)
• Use Modern Dashboard grouping instead of pivot

Important Limitation

SWQL cannot dynamically create columns.
Router columns must be explicitly defined.

If you want fully dynamic pivot behavior, that requires:

• External SQL view (not supported directly in Orion)
• Power BI
• API extraction

Strategic Recommendation

For operational clarity:

• Use this pivot approach if router set is fixed
• If router membership changes frequently, use vertical layout with grouping