Need help on converting SQL query to SWQL

Need help on converting the SQL script below to SWQL.

I got this SQL query from Wireless AP Availability for last month report and I would like to use the data in modern dashboard table which only supports SWQL.

Thanks is advance!

-------------------------------------------------------------------------

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

-- dynamic time constraints
SET @StartDate = CAST (DATEADD(month, DATEDIFF(month,0,GETDATE()), 0) as datetime)
SET @EndDate = GetDate()

-- SQL query
SELECT CONVERT(DateTime, LTrim(MONTH(DateTime)) + '/01/' + LTrim(YEAR(DateTime)), 101) AS SummaryMonth, NPM_NV_WL_REPORT_APS_V.AP_Name AS Name, NPM_NV_WL_REPORT_APS_V.Controller AS Controller, Nodes.VendorIcon AS Vendor_Icon, NPM_NV_WL_REPORT_APS_V.IPAddress AS IP_Address, AVG((NPM_NV_WL_REPORT_APS_V.Available * 100)) AS AVERAGE_of_Avg_Availability, Nodes.NodeID AS NodeID, NPM_NV_WL_APS_V.RecordID AS RecordID FROM ( NPM_NV_WL_REPORT_APS_V INNER JOIN NPM_NV_WL_APS_V ON ( NPM_NV_WL_REPORT_APS_V.NodeID = NPM_NV_WL_APS_V.NodeID AND NPM_NV_WL_REPORT_APS_V.RecordID = NPM_NV_WL_APS_V.RecordID ) ) INNER JOIN Nodes ON ( NPM_NV_WL_APS_V.NodeID = Nodes.NodeID ) WHERE ( DateTime BETWEEN @StartDate AND @EndDate ) GROUP BY CONVERT(DateTime, LTrim(MONTH(DateTime)) + '/01/' + LTrim(YEAR(DateTime)), 101), NPM_NV_WL_REPORT_APS_V.AP_Name, NPM_NV_WL_REPORT_APS_V.Controller, Nodes.VendorIcon, NPM_NV_WL_REPORT_APS_V.IPAddress, Nodes.NodeID, NPM_NV_WL_APS_V.RecordID ORDER BY SummaryMonth ASC, 6 DESC

Find more posts tagged with

moderndashborads

SWQL

sdk

sql query

Accepted answers

All comments

donrobert5

I no longer have a Solarwinds Environment but give this a try. If that does not work, wait for other people to comment.

SELECT
CONVERT(DateTime, LTrim(MONTH(DateTime)) + '/01/' + LTrim(YEAR(DateTime)), 101) AS SummaryMonth,
NPM_NV_WL_REPORT_APS_V.AP_Name AS Name,
NPM_NV_WL_REPORT_APS_V.Controller AS Controller,
Nodes.VendorIcon AS Vendor_Icon,
NPM_NV_WL_REPORT_APS_V.IPAddress AS IP_Address,
AVG((NPM_NV_WL_REPORT_APS_V.Available * 100)) AS AVERAGE_of_Avg_Availability,
Nodes.NodeID AS NodeID,
NPM_NV_WL_APS_V.RecordID AS RecordID
FROM
NPM_NV_WL_REPORT_APS_V
INNER JOIN
NPM_NV_WL_APS_V ON (NPM_NV_WL_REPORT_APS_V.NodeID = NPM_NV_WL_APS_V.NodeID AND NPM_NV_WL_REPORT_APS_V.RecordID = NPM_NV_WL_APS_V.RecordID)
INNER JOIN
Nodes ON (NPM_NV_WL_APS_V.NodeID = Nodes.NodeID)
WHERE
DateTime BETWEEN DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AND GETDATE()
GROUP BY
CONVERT(DateTime, LTrim(MONTH(DateTime)) + '/01/' + LTrim(YEAR(DateTime)), 101),
NPM_NV_WL_REPORT_APS_V.AP_Name,
NPM_NV_WL_REPORT_APS_V.Controller,
Nodes.VendorIcon,
NPM_NV_WL_REPORT_APS_V.IPAddress,
Nodes.NodeID,
NPM_NV_WL_APS_V.RecordID
ORDER BY
SummaryMonth ASC,
6 DESC

jethro.valenzuela82

Thanks Jay. . It didn't work though.

bobmarley

Give this a try

SELECT 
  WLC.DisplayName AS ControllerName,
  WLC.ThinAPsCount AS TotalThinAPs,
  COUNT(AP.ID) AS DownAPCount,
  (1.0 * COUNT(AP.ID)) / WLC.ThinAPsCount AS DownPercent
FROM 
  Orion.Wireless.Controllers AS WLC
  INNER JOIN Orion.Wireless.AccessPoints.Thin AS AP 
    ON WLC.ID = AP.ControllerID
WHERE 
  AP.Status = 2 -- 'Down' status
GROUP BY 
  WLC.DisplayName, WLC.ThinAPsCount
ORDER BY 
  DownPercent DESC

jethro.valenzuela82

Thank you of looking into this. I am actually looking for Wireless AP 30 days availability. The Wireless AP's are polled using API from a controller. I know a workaround and this is to monitor AP's individually. However, I am also trying to see if there's a way to do it if AP's are polled using API. The script that I posted is from a canned report which is very helpful but I want to convert it to SWQL so I can use it in Modern Dashboard.