Third Party Data Processor
IMPORTANT: This DEX pack makes use of a third-party API from DB-IP: https://db-ip.com/. The script will send public IP address information (considered Personally Identifiable Information or PII) from the subject system to obtain ISP registration and location information for said IP address. DB-IP should be considered as a third-party data processor.
Notes
After this DEX Pack is installed, and components correctly configured, it may take up to 24 hours for data to appear.
Import Kit
To use this DEX Pack, the corresponding Kit must be imported to SysTrack.
If you have already imported the DEX Pack directly from the Kits page, the Import Kit step is complete. You may move on to the next step.
If you are viewing this DEX Pack in the Customer Gateway, follow these steps to import this DEX Pack Kit:
On the DEX Pack page, download the DEX Pack ZIP file
In SysTrack, open Kits
Under Local, click Select Kit File
Select the DEX Pack ZIP File
ZoomQualityCheck Action
Enable Collection Extensions
Verify you have unlocked the tenant next to your username
In Configure, click Automations and Collection Extensions on the left.
Select Action Governance
Find the ZoomQualityCheck action in the list and check the box on the left, in the first column in the table.
Click the Bulk Actions button above the table and select Enable.
In the window that appears, click the Yes button and the action will be enabled.
Assign ZoomNetCon View
The Zoom Quality Dashboard requires you to make a View for the SF_Zoom Quality Check Role.
To create the new View:
Navigate to Configure > Views
Click the padlock icon in the upper-right to enable editing
Click the plus icon to add a new View
Fill in the Settings as follows:
View Name: ZOOMNETCON
Existing Category: Custom
Expires in: 30 days
When Expired: Append Data
Refresh: Daily, Inside, 24x7
Copy this SQL query, and paste it into the box under SQL Selection > Generic
Click Test SQL. A Test Success message should appear. If the test is not successful, the query may have been copied incorrectly
Click Create View at the top-right
To assign the new View:
In Configure, go to System Configuration > Roles.
Select the SF_Zoom Quality Check Role.
At the bottom of the screen, select the Views tab.
Scroll to ZOOMNETCON and check the box next to it.
Click Save Changes.
ZOOMNETCON SQL Query
SELECT
T0.WGUID,
GETUTCDATE() AS VWTIME,
T0.probe_time,
T0.probe_count,
T0.avg_jitter,
T0.avg_latency,
T0.avg_loss_rate,
T0.is_proxy,
T0.max_jitter,
T0.max_latency,
T0.max_loss_rate,
T0.primary_adapter_string AS primary_adapter,
T2.STRVALUE AS primary_ASN,
T3.STRVALUE AS primary_CIDR,
T4.STRVALUE AS primary_city,
T5.STRVALUE AS primary_country,
T6.STRVALUE AS primary_egress_ip,
T7.STRVALUE AS primary_ISP,
T8.STRVALUE AS primary_lat,
T9.STRVALUE AS primary_lon,
T10.STRVALUE AS target_ip,
T11.STRVALUE AS zoom_adapter,
T12.STRVALUE AS zoom_ASN,
T13.STRVALUE AS zoom_CIDR,
T14.STRVALUE AS zoom_city,
T15.STRVALUE AS zoom_country,
T16.STRVALUE AS zoom_egress_ip,
T17.STRVALUE AS zoom_ISP,
T18.STRVALUE AS zoom_lat,
T19.STRVALUE AS zoom_lon,
T0.NET_UTIL,
T0.FRAME_RATE,
T0.BYTE_RATE,
T0.BCAST_RATE,
T0.OUTQLEN,
T0.REXMIT,
T0.BANDWIDTH,
T0.WIFI_SIGNALQ,
T0.WIFI_TRANS_RATE,
T0.WIFI_RECV_RATE
FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY RECID ORDER BY MINS_DIFF, WTYPE) AS ROWNUM
FROM (
SELECT
T0.*,
T1.NET_UTIL,
T1.FRAME_RATE,
T1.BYTE_RATE,
T1.BCAST_RATE,
T1.OUTQLEN,
T1.REXMIT,
T1.BANDWIDTH,
T1.FRAME_RATE_SD,
T1.BYTE_RATE_SD,
T1.WIFI_SIGNALQ,
T1.WIFI_TRANS_RATE,
T1.WIFI_RECV_RATE,
T1.WIFI_CHANNEL,
T1.WIFI_BAND,
T1.WIFI_FREQ,
ABS(DATEDIFF(N,T0.probe_time,T1.WTIME)) AS MINS_DIFF,
T1.WTYPE,
S0.STRVALUE AS primary_adapter_string
FROM DYNI_ZoomNetworkConnection AS T0
LEFT JOIN SASTR_DYN AS S0 ON T0.primary_adapter = S0.STRINGID
LEFT JOIN (
SELECT
T0.*,
S0.STRVALUE,
T1.ViewName
FROM SANET AS T0
INNER JOIN SASTRDEVICE AS S0 ON T0.DEVICE_ID = S0.STRINGID
INNER JOIN (
SELECT DISTINCT
Name1 AS ViewName,
FIRST_VALUE(Name2) OVER (PARTITION BY Name1 ORDER BY NumRows DESC) AS NetName
FROM (
SELECT
S0.STRVALUE AS Name1,
S1.STRVALUE AS Name2,
COUNT(*) AS NumRows
FROM SANET_CONN_HISTORY AS T0
INNER JOIN SASTRDEVICE AS S0 ON T0.ADPT_DESC_ID = S0.STRINGID
INNER JOIN SASTRDEVICE AS S1 ON T0.DEVICE_ID = S1.STRINGID
WHERE S1.STRVALUE > ''
GROUP BY
S0.STRVALUE,
S1.STRVALUE
) AS T0
) AS T1 ON S0.STRVALUE = T1.NetName
WHERE T0.WTYPE IN (0,1)
) AS T1 ON T0.WGUID = T1.WGUID AND T1.ViewName = S0.STRVALUE
) AS T0
) AS T0
LEFT JOIN SASTR_DYN AS T2 ON T0.primary_ASN = T2.STRINGID
LEFT JOIN SASTR_DYN AS T3 ON T0.primary_CIDR = T3.STRINGID
LEFT JOIN SASTR_DYN AS T4 ON T0.primary_city = T4.STRINGID
LEFT JOIN SASTR_DYN AS T5 ON T0.primary_country = T5.STRINGID
LEFT JOIN SASTR_DYN AS T6 ON T0.primary_egress_ip = T6.STRINGID
LEFT JOIN SASTR_DYN AS T7 ON T0.primary_ISP = T7.STRINGID
LEFT JOIN SASTR_DYN AS T8 ON T0.primary_lat = T8.STRINGID
LEFT JOIN SASTR_DYN AS T9 ON T0.primary_lon = T9.STRINGID
LEFT JOIN SASTR_DYN AS T10 ON T0.target_ip = T10.STRINGID
LEFT JOIN SASTR_DYN AS T11 ON T0.zoom_adapter = T11.STRINGID
LEFT JOIN SASTR_DYN AS T12 ON T0.zoom_ASN = T12.STRINGID
LEFT JOIN SASTR_DYN AS T13 ON T0.zoom_CIDR = T13.STRINGID
LEFT JOIN SASTR_DYN AS T14 ON T0.zoom_city = T14.STRINGID
LEFT JOIN SASTR_DYN AS T15 ON T0.zoom_country = T15.STRINGID
LEFT JOIN SASTR_DYN AS T16 ON T0.zoom_egress_ip = T16.STRINGID
LEFT JOIN SASTR_DYN AS T17 ON T0.zoom_ISP = T17.STRINGID
LEFT JOIN SASTR_DYN AS T18 ON T0.zoom_lat = T18.STRINGID
LEFT JOIN SASTR_DYN AS T19 ON T0.zoom_lon = T19.STRINGID
WHERE
ROWNUM = 1
AND (
MINS_DIFF IS NULL
OR MINS_DIFF <= 10
)
AND probe_time > <LASTREFRESHTIME>Zoom Quality Check Role
This DEX Pack requires you to assign the SF_Zoom Quality Check Role to the relevant Configurations assigned to systems where you would like the data to be collected:
Navigate to Configure > Configurations
Click the padlock icon in the upper-right to enable editing
Use the drop-down at the top to select a relevant Configuration, or create a new Configuration
Assign the SF_Zoom Quality Check Role to the Configuration by dragging it from Available Roles to Assigned Roles
Click Save Changes at the top-right
Repeat this process for any other relevant Configurations
NOTE: The Zoom Quality Role is assigning the Tool Schedule to periodically run a collection extension.