ZTNA Queries using Live Discover

With ZTNA deployed we will be releasing a number of queries to help administrators explore the data.

Prior to that query pack being published by Sophos you can add these to the Threat Analysis Center directly by creating a new query.

DataLake queries

Top users based on usage

-- USAGE
-- VARIABLE $$User$$                           STRING
-- VARIABLE $$Application$$                    STRING
-- VARIABLE $$Gateway$$                        STRING
-- VARIABLE $$Order by Sent, Received, Total$$ STRING
SELECT
  ARRAY_JOIN(ARRAY_AGG(DISTINCT application_name),CHR(10)) app_list,
  ARRAY_JOIN(ARRAY_AGG(DISTINCT user_name),CHR(10)) user_name,
  SUM(bytes_in) bytes_received,
  SUM(bytes_out) bytes_sent,
  (SUM(COALESCE(bytes_in, 0)) + SUM(COALESCE("bytes_out", 0))) total_bytes,
  ARRAY_JOIN(ARRAY_AGG(DISTINCT gateway_name),CHR(10)) gateway_name
FROM  ztna_data
WHERE "component" = 'application' 
   AND LOWER(application_name) LIKE LOWER('%$$Application$$%')
   AND LOWER(user_name) LIKE LOWER('%$$User$$%')
   AND LOWER(gateway_name) LIKE LOWER('%$$Gateway$$%')
GROUP BY (CASE WHEN '$$User$$' = CAST('%' AS VARCHAR) THEN user_name ELSE '1' END), 
         (CASE WHEN '$$Application$$' = CAST('%' AS VARCHAR) THEN application_name ELSE '1' END),
         (CASE WHEN '$$Gateway$$' = CAST('%' AS VARCHAR) THEN gateway_name ELSE '1' END)
ORDER BY (CASE 
            WHEN LOWER('$$Order by Sent, Received, Total$$') LIKE '%s%' THEN bytes_sent 
            WHEN LOWER('$$Order by Sent, Received, Total$$') LIKE '%r%' THEN bytes_received 
            ELSE total_bytes
         END)
         DESC NULLS LAST
LIMIT 10000

Denied access events

SELECT
  COUNT(*) instances,
  ARRAY_JOIN(ARRAY_AGG(DISTINCT sync_sec_health_status),CHR(10)) sync_sec_health_status, 
  MIN(timestamp) Earliest_Denied_Access,
  MAX(timestamp) Last_Denied_Access,
  ARRAY_JOIN(ARRAY_AGG(timestamp),CHR(10)) Timestamp_list, 
  ARRAY_JOIN(ARRAY_AGG(DISTINCT application_name),CHR(10)) application_name_list, 
  ARRAY_JOIN(ARRAY_AGG(DISTINCT user_name),CHR(10)) user_name_list, 
  ARRAY_JOIN(ARRAY_AGG(DISTINCT operating_system),CHR(10)) operating_system_list,
  ARRAY_JOIN(ARRAY_AGG(DISTINCT deny_reason),CHR(10)) deny_reason_list,
  ARRAY_JOIN(ARRAY_AGG(DISTINCT agent_version),CHR(10)) agent_version_list,
  ARRAY_JOIN(ARRAY_AGG(DISTINCT gateway_name),CHR(10)) gateway_list
FROM  ztna_data
WHERE component = 'policy' 
   AND policy_status = 'DENY'
   AND LOWER(application_name) LIKE LOWER('%$$Application$$%')
   AND LOWER(user_name) LIKE LOWER('%$$User$$%')
   AND LOWER(gateway_name) LIKE LOWER('%$$Gateway$$%')
GROUP BY (CASE WHEN '$$User$$' = CAST('%' AS VARCHAR) THEN user_name ELSE '1' END), 
         (CASE WHEN '$$Application$$' = CAST('%' AS VARCHAR) THEN application_name ELSE '1' END),
         (CASE WHEN '$$Gateway$$' = CAST('%' AS VARCHAR) THEN gateway_name ELSE '1' END)
ORDER BY instances DESC
LIMIT 10000

List of denied access attempt by user based on denied count threshold

SELECT

count(*) as denied_count, application_name

FROM

ztna_data

WHERE ((("component" = 'policy') AND ("policy_status" = 'DENY')) AND ("user_name" IN ('<user_name>')))

GROUP BY application_name HAVING count(*) > 5

ORDER BY denied_count DESC

List of denied access attempt by application based on denied count threshold

SELECT

count(*) as denied_count, user_name

FROM

ztna_data

WHERE ((("component" = 'policy') AND ("policy_status" = 'DENY')) AND ("application_name" IN ('<app_name>')))

GROUP BY user_name HAVING count(*) > 1

ORDER BY denied_count DESC

List of all applications and their last accessed date

SELECT application_name,

timestamp

FROM (

SELECT application_name,

timestamp,

RANK() OVER (

PARTITION BY application_name

ORDER BY timestamp DESC

) rank_no

FROM symlink_ztna_data

WHERE "component" = 'application'

) ztna_data

WHERE ztna_data.rank_no = 1

order by ztna_data.rank_no desc

Data Counts

SELECT

gateway_name,

instance_name,

container_name,

pod_name,

component,

count(*) instances

FROM ztna_data

GROUP BY gateway_name, instance_name, container_name, pod_name, component

ORDER BY gateway_name, instance_name, container_name, pod_name, component, instances