SWQL Query Issue for dashboard – Custom Poller Data (Available Licenses)

Hi SolarWinds Professionals,

I'm seeking some guidance on a newly created SWQL query for a Modern Dashboard, where I aim to display Custom Poller Data. Query as mentioned below.

SELECT
n.NodeID,
n.Caption,
n.IP_Address,
MAX(CASE WHEN AssignmentName LIKE '%avAesTsapiLicenseTotal%' THEN CurrentValue END) AS TotalLicenses,
MAX(CASE WHEN AssignmentName LIKE '%avAesTsapiLicenseAcquired%' THEN CurrentValue END) AS UtilizedLicenses,

CASE
WHEN MAX(CASE WHEN AssignmentName LIKE '%avAesTsapiLicenseTotal%' THEN CurrentValue END) = 0 THEN 0
ELSE
ROUND(
MAX(CASE WHEN AssignmentName LIKE '%avAesTsapiLicenseAcquired%' THEN CurrentValue END) * 100.0 /
MAX(CASE WHEN AssignmentName LIKE '%avAesTsapiLicenseTotal%' THEN CurrentValue END), 2
)
END AS UtilizationPercentag
FROM Orion.NPM.CustomPollerAssignment CP
join Orion.Nodes n on n.NodeID = CP.NodeID
where n.NodeID in (2850)
group by n.NodeID,n.Caption,n.IP_Address

The current query is working as expected for most parts. However, I'm facing a challenge in calculating and displaying the available licenses, which should be derived using the formula:
Available Licenses = TotalLicenses - UtilizedLicenses. The screenshot below shows the result based on the current query mentioned above. I would like to add one more column to display the available licenses.

Could anyone please assist me with how to implement this logic in SWQL? I'm struggling to find the correct approach.

Thanks in advance!

Find more posts tagged with

support

orion

dashboard

report_writer

SWQL

certified

nta

Accepted answers

mesverrum

To force swql to treat a string as an integer you can try

totallicenses*1 - utilizedlicenses*1

Might need to experiment with it but that sort of thing tips it if that it's dealing with numbers.

All comments

mesverrum

I would nest this all into a subquery and then you can do the math operations against the various columns.

Daran

chatgpt suggested something like this...

SELECT
  n.NodeID,
  n.Caption,
  n.IP_Address,

  MAX(CASE WHEN AssignmentName LIKE '%avAesTsapiLicenseTotal%' THEN CurrentValue END) AS TotalLicenses,
  MAX(CASE WHEN AssignmentName LIKE '%avAesTsapiLicenseAcquired%' THEN CurrentValue END) AS UtilizedLicenses,

  -- Newly added available licenses
  MAX(CASE WHEN AssignmentName LIKE '%avAesTsapiLicenseTotal%' THEN CurrentValue END)
  -
  MAX(CASE WHEN AssignmentName LIKE '%avAesTsapiLicenseAcquired%' THEN CurrentValue END)
  AS AvailableLicenses,

  -- Utilization Percentage
  CASE
    WHEN MAX(CASE WHEN AssignmentName LIKE '%avAesTsapiLicenseTotal%' THEN CurrentValue END) = 0 THEN 0
    ELSE
      ROUND(
        MAX(CASE WHEN AssignmentName LIKE '%avAesTsapiLicenseAcquired%' THEN CurrentValue END) * 100.0 /
        MAX(CASE WHEN AssignmentName LIKE '%avAesTsapiLicenseTotal%' THEN CurrentValue END), 2
      )
  END AS UtilizationPercentage

FROM Orion.NPM.CustomPollerAssignment CP
JOIN Orion.Nodes n ON n.NodeID = CP.NodeID
WHERE n.NodeID IN (2850)
GROUP BY n.NodeID, n.Caption, n.IP_Address

WindsOfNishat

Hi @Daran

I’ve already tried multiple approaches, including using ChatGPT, Gemini, DeepSeek, and even consulting with my DB team, but I haven’t received any workable solution so far. Unfortunately, the query you shared isn’t working either.

WindsOfNishat

Thanks @mesverrum, I will try to explore this way.

Daran

yes unfortunately I tend to have the same experience with chatgpt - it is good as a starting point but then you wast a day of two going down blind alleys trying queries to non-existent tables/fields... apologies but it was worth a shot.

WindsOfNishat

No need to apologize, atleast you have tried and I truly appreciate you taking the time to try.

mesverrum

Should be similar to

select

nodeid, caption, ip_address, totallicenses, utilizedlicenses, utilizationpercentag, (totallicenses - utilizedlicenses) as remaininglcenses

from

(

)

WindsOfNishat

Hi @mesverrum

I tried your provided query but it's giving me error for minus operation as shown below screenshot.