Dear Thwack
I have tried to fetch list of all those clients along with SSID name, who are connected to an Access point as maximum number of clients for particular time period ie. for one hour or last 24 hours.
I have found there are three types tables on SWQL
1. Accesspoint History Table
2. Interface History Table
3. Client History Table
AP history Table is connected to interface history table by Interface ID, and Interface History Table is connected with Client History Table by Interface ID. thus, there is no direct connection between Accesspoint history table and Client history table.
So, while i use group by query in Accesspoint history table to find max client connected, I am able to find it and from the time they are connected, I am able to find interfaces details from interface history table, but some client connections are missing in history table for that particular time period.
Please find below SWQL query I have created.
I am able to find current client connections and SSID name
I have pasted that query below
AP MAX CLIENT ALONG WITH SSID CLIENT DETAILS - FROM CURRENT TABLES
(This query gives result correctly)
============================================
SELECT APClients.AccessPointID, AP.DisplayName, max(Ap.clients) as MaxClients, APClients.SSID, count(distinct(mac)) as TotalClients
FROM Orion.Wireless.Clients APClients
INNER JOIN Orion.Wireless.AccessPoints AP
on APClients.AccessPointID = AP.ID
where APClients.lastupdate >= (ADDHOUR(-24, GETUTCDATE()))
and APClients.AccessPointID in (select top 10 id FROM Orion.Wireless.AccessPoints order by clients desc)
GROUP by APClients.AccessPointID, AP.DisplayName, APClients.SSID
order by AP.DisplayName, APClients.SSID, TotalClients desc
AP MAX CLIENT ALONG WITH SSID CLIENT DETAILS - FROM HISTORY TABLES
(This query gives result incorrect)
============================================
select MaxClientSSID.MaxClientSSID_APID, MaxClientSSID.MaxClientSSID_APName, HistClients.SSID, count(DISTINCT(HistClients.mac)) as SSIDClients, Max(MaxClientSSID.MaxClientSSID_Date) as MxDt
FROM Orion.Wireless.HistoricalClients HistClients
inner JOIN
(
Select APHistInt.ID as MaxClientSSID_IntID, ApMaxDate.APMaxDateID as MaxClientSSID_APID, APMaxDate.MaxClients as MaxClientSSID_MaxClient, ApMaxDate.APName as MaxClientSSID_APName, ApMaxDate.MaxClientDate as MaxClientSSID_Date
From Orion.Wireless.HistoricalInterfaces APHistInt
INNER JOIN
(
select t2.ID as APMaxDateID,t2.DisplayName as APName, MAX(t2.LastUpdate) AS MaxClientDate, max(t2.Clients) as MaxClients
from Orion.Wireless.HistoricalAccessPoints T2
INNER JOIN
(select max(Aphist.Clients) as MaxValue, Aphist.ID as APID
from Orion.Wireless.HistoricalAccessPoints ApHist
where aphist.LastUpdate >= ADDHOUR(-24, GETUTCDATE())
group by Aphist.ID) T1
on T2.ID = T1.APID and T2.Clients = T1.MaxValue and t2.LastUpdate>= ADDHOUR(-24, GETUTCDATE())
group by T2.ID, t2.DisplayName) APMaxDate
on APHistInt.AccessPointID = APMaxDate.APMaxDateID and APHistInt.LastUpdate = APMaxDate.MaxClientDate
where ApHistInt.LastUpdate >= ADDHOUR(-24, GETUTCDATE())
)MaxClientSSID
ON HistClients.InterfaceID = MaxClientSSID_IntID
where HistClients.LastUpdate >= ADDHOUR(-24, GETUTCDATE())
group by MaxClientSSID.MaxClientSSID_APID, MaxClientSSID.MaxClientSSID_APName,HistClients.SSID
Thanks and Regards
Balvant
