Generate report for Thin AP status

daraka

Hi All,

I have tested the following query from the one Twack post, and it gives a valid output for status DOWN AP counts.

SELECT [WLCs].DisplayName
     , [WLCs].ThinAPsCount
     , [WLCs].Status AS [ControllerStatus]
     , COUNT([DownThinAPs].ID) AS DownAPCount
FROM Orion.Wireless.Controllers AS [WLCs]
INNER JOIN Orion.Wireless.AccessPoints.Thin AS [DownThinAPs]
  ON [WLCs].ID = [DownThinAPs].ControllerID
 AND [DownThinAPs].Status = 2 
GROUP BY [WLCs].DisplayName
       , [WLCs].ThinAPsCount
       , [WLCs].Status

I have modified the above query to generate a report for DOWN APs with Thin AP details:

SELECT 
    [WLCs].DisplayName as WLC,
[WLCs].IPAddress as WLC_IP,
    [DownThinAPs].DisplayName as ThinAP,
 [DownThinAPs].Status as Status_down
FROM Orion.Wireless.Controllers AS [WLCs]
INNER JOIN Orion.Wireless.AccessPoints.Thin AS [DownThinAPs]
  ON [WLCs].ID = [DownThinAPs].ControllerID
 AND [DownThinAPs].Status = 2

Now, I would like to expand this to generate a report for nodes that have been DOWN for the last 5 days of the period.

Would it be valid to use the condition "LastUpdate >= ADDHOUR(-168, GETUTCDATE())"?

Thanks!

stuartd

That wouldn't work as 'LastUpdate' exists in both your tables so you'd need to specify which one to look at.

This would work:

WHERE [DownThinAPs].LastUpdate >= ADDHOUR(-168, GETUTCDATE())