I need assistance editing a Wireless report that provides the following fields in a single table:
- SSID (We deploy multiple SSID's per AP, I need to be able to focus on a single SSID).
- Number of client connections (Total unique daily)
- Unique count of client connections (Max unique at one time, reset daily).
- Total Traffic Inbound
- Total Traffic Outbound
- Total Combined Traffic
I found a report that had been built previously, but not entirely sure how to edit this. I would like this report to focus on a single SSID of my choosing and disregard the rest. Also, would like to be able to change report timeframe and SSID.
Currently the report displays like this:
SELECT T1.SSID AS "SSID"
, MIN([# of Unique Sessions]) AS "Sessions"
, MIN([# of Unique Clients]) AS "Unique Clients"
, AVG([Session Duration in Minutes]) AS "Avg session duration (mins)"
, SUM([Client Bytes]) / 1048576 AS "Total traffic (MB)"
, SUM ([Client In Bytes]) / 1048576 AS "Total In (MB)"
, SUM([Client Out Bytes]) / 1048576 AS "Total Out (MB)"
, AVG([Session Bytes]) / 1048576 AS "Avg traffic per session (MB)"
, AVG([Session In Bytes]) / 1048576 AS "Avg In per session (MB)"
, AVG([Session Out Bytes]) / 1048576 AS "Avg Out per session (MB)"
, AVG([Client Bytes]) / 1048576 AS "Avg traffic per client (MB)"
, AVG([Client In Bytes]) / 1048576 AS "Avg In per client (MB)"
, AVG([Client Out Bytes]) / 1048576 AS "Avg Out per client (MB)"
, AVG([Client Average Bandwidth]) / 1024 AS "Avg bandwidth per client (KBps)"
, AVG([Average Signal]) AS "Avg signal"
FROM (SELECT SSID AS SSID
, COUNT(DISTINCT MACAddress) AS "# of Unique Clients"
, COUNT(DISTINCT SessionID) AS "# of Unique Sessions"
, AVG(SignalStrength) AS "Average Signal"
FROM Wireless_ClientsHistory
WHERE (FirstUpdate >= ${FromTime}
AND FirstUpdate < ${ToTime}) OR
(LastUpdate >= ${FromTime}
AND LastUpdate < ${ToTime})
GROUP BY SSID
) AS T1
JOIN (SELECT SUM(InBytes) AS "Session In Bytes"
, SUM(OutBytes) AS "Session Out Bytes"
, SUM(InBytes+OutBytes) AS "Session Bytes"
, SSID
, SessionID
, DATEDIFF(mi,min(FirstUpdate),max(LastUpdate)) AS "Session Duration in Minutes"
FROM Wireless_ClientsHistory
WHERE (FirstUpdate >= ${FromTime}
AND FirstUpdate < ${ToTime}) OR
(LastUpdate >= ${FromTime}
AND LastUpdate < ${ToTime})
GROUP BY SSID
, SessionID
) AS T2 ON (T1.SSID = T2.SSID)
JOIN (SELECT SUM(InBytes) AS "Client In Bytes"
, SUM(OutBytes) AS "Client Out Bytes"
, SUM(InBytes+OutBytes) AS "Client Bytes"
, SSID
, MACAddress
, DATEDIFF(mi,min(FirstUpdate),max(LastUpdate)) AS "Client Duration in Minutes"
, AVG(InBps+OutBps) AS "Client Average Bandwidth"
FROM Wireless_ClientsHistory
WHERE (FirstUpdate >= ${FromTime}
AND FirstUpdate < ${ToTime}) OR
(LastUpdate >= ${FromTime}
AND LastUpdate < ${ToTime})
GROUP BY SSID
, MACAddress
) AS T3 ON (T1.SSID = T3.SSID)
GROUP BY T1.SSID
