Thread Info
  • Replies 0 replies
  • Subscribers 5 subscribers
  • Views 20725 views
  • Users 0 members are here
Options
Suggested

ZTNA Queries using Live Discover

Karl_Ackerman

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
Unfiltered HTML