LogoLogo
Knowledge BaseCommunityRelease NotesRequest Demo
  • Overview
  • Quick Start
    • Onboarding Guide
  • Data Sources & Transports
    • Supported Logs
      • 1Password Logs
      • Apache Logs
      • AppOmni Logs
      • Asana Logs
      • Atlassian Logs
      • Auditd Logs
      • Auth0 Logs
      • AWS Logs
        • AWS ALB
        • AWS Aurora
        • AWS CloudFront
        • AWS CloudTrail
        • AWS CloudWatch
        • AWS Config
        • AWS EKS
        • AWS GuardDuty
        • AWS Security Hub
        • Amazon Security Lake
        • AWS S3
        • AWS Transit Gateway
        • AWS VPC
        • AWS WAF
      • Azure Monitor Logs
      • Bitwarden Logs
      • Box Logs
      • Carbon Black Logs
      • Cisco Umbrella Logs
      • Cloudflare Logs
      • CrowdStrike Logs
        • CrowdStrike Falcon Data Replicator
        • CrowdStrike Event Streams
      • Docker Logs
      • Dropbox Logs
      • Duo Security Logs
      • Envoy Logs
      • Fastly Logs
      • Fluentd Logs
      • GCP Logs
      • GitHub Logs
      • GitLab Logs
      • Google Workspace Logs
      • Heroku Logs
      • Jamf Pro Logs
      • Juniper Logs
      • Lacework Logs
        • Lacework Alert Channel Webhook
        • Lacework Export
      • Material Security Logs
      • Microsoft 365 Logs
      • Microsoft Entra ID Audit Logs
      • Microsoft Graph Logs
      • MongoDB Atlas Logs
      • Netskope Logs
      • Nginx Logs
      • Notion Logs
      • Okta Logs
      • OneLogin Logs
      • Orca Security Logs (Beta)
      • Osquery Logs
      • OSSEC Logs
      • Proofpoint Logs
      • Push Security Logs
      • Rapid7 Logs
      • Salesforce Logs
      • SentinelOne Logs
      • Slack Logs
      • Snowflake Audit Logs (Beta)
      • Snyk Logs
      • Sophos Logs
      • Sublime Security Logs
      • Suricata Logs
      • Sysdig Logs
      • Syslog Logs
      • Tailscale Logs
      • Teleport Logs
      • Tenable Vulnerability Management Logs
      • Thinkst Canary Logs
      • Tines Logs
      • Tracebit Logs
      • Windows Event Logs
      • Wiz Logs
      • Zeek Logs
      • Zendesk Logs
      • Zoom Logs
      • Zscaler Logs
        • Zscaler ZIA
        • Zscaler ZPA
    • Custom Logs
      • Log Schema Reference
      • Transformations
      • Script Log Parser (Beta)
      • Fastmatch Log Parser
      • Regex Log Parser
      • CSV Log Parser
    • Data Transports
      • HTTP Source
      • AWS Sources
        • S3 Source
        • CloudWatch Logs Source
        • SQS Source
          • SNS Source
        • EventBridge
      • Google Cloud Sources
        • Cloud Storage (GCS) Source
        • Pub/Sub Source
      • Azure Blob Storage Source
    • Monitoring Log Sources
    • Ingestion Filters
      • Raw Event Filters
      • Normalized Event Filters (Beta)
    • Data Pipeline Tools
      • Chronosphere Onboarding Guide
      • Cribl Onboarding Guide
      • Fluent Bit Onboarding Guide
        • Fluent Bit Configuration Examples
      • Fluentd Onboarding Guide
        • General log forwarding via Fluentd
        • MacOS System Logs to S3 via Fluentd
        • Syslog to S3 via Fluentd
        • Windows Event Logs to S3 via Fluentd (Legacy)
        • GCP Audit to S3 via Fluentd
      • Observo Onboarding Guide
      • Tarsal Onboarding Guide
    • Tech Partner Log Source Integrations
  • Detections
    • Using Panther-managed Detections
      • Detection Packs
    • Rules and Scheduled Rules
      • Writing Python Detections
        • Python Rule Caching
        • Data Models
        • Global Helper Functions
      • Modifying Detections with Inline Filters (Beta)
      • Derived Detections (Beta)
        • Using Derived Detections to Avoid Merge Conflicts
      • Using the Simple Detection Builder
      • Writing Simple Detections
        • Simple Detection Match Expression Reference
        • Simple Detection Error Codes
    • Correlation Rules (Beta)
      • Correlation Rule Reference
    • PyPanther Detections (Beta)
      • Creating PyPanther Detections
      • Registering, Testing, and Uploading PyPanther Detections
      • Managing PyPanther Detections in the Panther Console
      • PyPanther Detections Style Guide
      • pypanther Library Reference
      • Using the pypanther Command Line Tool
    • Signals
    • Policies
    • Testing
      • Data Replay (Beta)
    • Framework Mapping and MITRE ATT&CK® Matrix
  • Cloud Security Scanning
    • Cloud Resource Attributes
      • AWS
        • ACM Certificate
        • CloudFormation Stack
        • CloudWatch Log Group
        • CloudTrail
        • CloudTrail Meta
        • Config Recorder
        • Config Recorder Meta
        • DynamoDB Table
        • EC2 AMI
        • EC2 Instance
        • EC2 Network ACL
        • EC2 SecurityGroup
        • EC2 Volume
        • EC2 VPC
        • ECS Cluster
        • EKS Cluster
        • ELBV2 Application Load Balancer
        • GuardDuty Detector
        • GuardDuty Detector Meta
        • IAM Group
        • IAM Policy
        • IAM Role
        • IAM Root User
        • IAM User
        • KMS Key
        • Lambda Function
        • Password Policy
        • RDS Instance
        • Redshift Cluster
        • Route 53 Domains
        • Route 53 Hosted Zone
        • S3 Bucket
        • WAF Web ACL
  • Alerts & Destinations
    • Alert Destinations
      • Amazon SNS Destination
      • Amazon SQS Destination
      • Asana Destination
      • Blink Ops Destination
      • Custom Webhook Destination
      • Discord Destination
      • GitHub Destination
      • Google Pub/Sub Destination (Beta)
      • Incident.io Destination
      • Jira Cloud Destination
      • Jira Data Center Destination (Beta)
      • Microsoft Teams Destination
      • Mindflow Destination
      • OpsGenie Destination
      • PagerDuty Destination
      • Rapid7 Destination
      • ServiceNow Destination (Custom Webhook)
      • Slack Bot Destination
      • Slack Destination (Webhook)
      • Splunk Destination (Beta)
      • Tines Destination
      • Torq Destination
    • Assigning and Managing Alerts
      • Managing Alerts in Slack
    • Alert Runbooks
      • Panther-managed Policies Runbooks
        • AWS CloudTrail Is Enabled In All Regions
        • AWS CloudTrail Sending To CloudWatch Logs
        • AWS KMS CMK Key Rotation Is Enabled
        • AWS Application Load Balancer Has Web ACL
        • AWS Access Keys Are Used Every 90 Days
        • AWS Access Keys are Rotated Every 90 Days
        • AWS ACM Certificate Is Not Expired
        • AWS Access Keys not Created During Account Creation
        • AWS CloudTrail Has Log Validation Enabled
        • AWS CloudTrail S3 Bucket Has Access Logging Enabled
        • AWS CloudTrail Logs S3 Bucket Not Publicly Accessible
        • AWS Config Is Enabled for Global Resources
        • AWS DynamoDB Table Has Autoscaling Targets Configured
        • AWS DynamoDB Table Has Autoscaling Enabled
        • AWS DynamoDB Table Has Encryption Enabled
        • AWS EC2 AMI Launched on Approved Host
        • AWS EC2 AMI Launched on Approved Instance Type
        • AWS EC2 AMI Launched With Approved Tenancy
        • AWS EC2 Instance Has Detailed Monitoring Enabled
        • AWS EC2 Instance Is EBS Optimized
        • AWS EC2 Instance Running on Approved AMI
        • AWS EC2 Instance Running on Approved Instance Type
        • AWS EC2 Instance Running in Approved VPC
        • AWS EC2 Instance Running On Approved Host
        • AWS EC2 Instance Running With Approved Tenancy
        • AWS EC2 Instance Volumes Are Encrypted
        • AWS EC2 Volume Is Encrypted
        • AWS GuardDuty is Logging to a Master Account
        • AWS GuardDuty Is Enabled
        • AWS IAM Group Has Users
        • AWS IAM Policy Blocklist Is Respected
        • AWS IAM Policy Does Not Grant Full Administrative Privileges
        • AWS IAM Policy Is Not Assigned Directly To User
        • AWS IAM Policy Role Mapping Is Respected
        • AWS IAM User Has MFA Enabled
        • AWS IAM Password Used Every 90 Days
        • AWS Password Policy Enforces Complexity Guidelines
        • AWS Password Policy Enforces Password Age Limit Of 90 Days Or Less
        • AWS Password Policy Prevents Password Reuse
        • AWS RDS Instance Is Not Publicly Accessible
        • AWS RDS Instance Snapshots Are Not Publicly Accessible
        • AWS RDS Instance Has Storage Encrypted
        • AWS RDS Instance Has Backups Enabled
        • AWS RDS Instance Has High Availability Configured
        • AWS Redshift Cluster Allows Version Upgrades
        • AWS Redshift Cluster Has Encryption Enabled
        • AWS Redshift Cluster Has Logging Enabled
        • AWS Redshift Cluster Has Correct Preferred Maintenance Window
        • AWS Redshift Cluster Has Sufficient Snapshot Retention Period
        • AWS Resource Has Minimum Number of Tags
        • AWS Resource Has Required Tags
        • AWS Root Account Has MFA Enabled
        • AWS Root Account Does Not Have Access Keys
        • AWS S3 Bucket Name Has No Periods
        • AWS S3 Bucket Not Publicly Readable
        • AWS S3 Bucket Not Publicly Writeable
        • AWS S3 Bucket Policy Does Not Use Allow With Not Principal
        • AWS S3 Bucket Policy Enforces Secure Access
        • AWS S3 Bucket Policy Restricts Allowed Actions
        • AWS S3 Bucket Policy Restricts Principal
        • AWS S3 Bucket Has Versioning Enabled
        • AWS S3 Bucket Has Encryption Enabled
        • AWS S3 Bucket Lifecycle Configuration Expires Data
        • AWS S3 Bucket Has Logging Enabled
        • AWS S3 Bucket Has MFA Delete Enabled
        • AWS S3 Bucket Has Public Access Block Enabled
        • AWS Security Group Restricts Ingress On Administrative Ports
        • AWS VPC Default Security Group Restricts All Traffic
        • AWS VPC Flow Logging Enabled
        • AWS WAF Has Correct Rule Ordering
        • AWS CloudTrail Logs Encrypted Using KMS CMK
      • Panther-managed Rules Runbooks
        • AWS CloudTrail Modified
        • AWS Config Service Modified
        • AWS Console Login Failed
        • AWS Console Login Without MFA
        • AWS EC2 Gateway Modified
        • AWS EC2 Network ACL Modified
        • AWS EC2 Route Table Modified
        • AWS EC2 SecurityGroup Modified
        • AWS EC2 VPC Modified
        • AWS IAM Policy Modified
        • AWS KMS CMK Loss
        • AWS Root Activity
        • AWS S3 Bucket Policy Modified
        • AWS Unauthorized API Call
    • Tech Partner Alert Destination Integrations
  • Investigations & Search
    • Search
      • Search Filter Operators
    • Data Explorer
      • Data Explorer SQL Search Examples
        • CloudTrail logs queries
        • GitHub Audit logs queries
        • GuardDuty logs queries
        • Nginx and ALB Access logs queries
        • Okta logs queries
        • S3 Access logs queries
        • VPC logs queries
    • Visualization and Dashboards
      • Custom Dashboards (Beta)
      • Panther-Managed Dashboards
    • Standard Fields
    • Saved and Scheduled Searches
      • Templated Searches
        • Behavioral Analytics and Anomaly Detection Template Macros (Beta)
      • Scheduled Search Examples
    • Search History
    • Data Lakes
      • Snowflake
        • Snowflake Configuration for Optimal Search Performance
      • Athena
  • PantherFlow (Beta)
    • PantherFlow Quick Reference
    • PantherFlow Statements
    • PantherFlow Operators
      • Datatable Operator
      • Extend Operator
      • Join Operator
      • Limit Operator
      • Project Operator
      • Range Operator
      • Sort Operator
      • Search Operator
      • Summarize Operator
      • Union Operator
      • Visualize Operator
      • Where Operator
    • PantherFlow Data Types
    • PantherFlow Expressions
    • PantherFlow Functions
      • Aggregation Functions
      • Date/time Functions
      • String Functions
      • Array Functions
      • Math Functions
      • Control Flow Functions
      • Regular Expression Functions
      • Snowflake Functions
      • Data Type Functions
      • Other Functions
    • PantherFlow Example Queries
      • PantherFlow Examples: Threat Hunting Scenarios
      • PantherFlow Examples: SOC Operations
      • PantherFlow Examples: Panther Audit Logs
  • Enrichment
    • Custom Lookup Tables
      • Creating a GreyNoise Lookup Table
      • Lookup Table Examples
        • Using Lookup Tables: 1Password UUIDs
      • Lookup Table Specification Reference
    • Identity Provider Profiles
      • Okta Profiles
      • Google Workspace Profiles
    • Anomali ThreatStream
    • IPinfo
    • Tor Exit Nodes
    • TrailDiscover (Beta)
  • Panther AI (Beta)
  • System Configuration
    • Role-Based Access Control
    • Identity & Access Integrations
      • Azure Active Directory SSO
      • Duo SSO
      • G Suite SSO
      • Okta SSO
        • Okta SCIM
      • OneLogin SSO
      • Generic SSO
    • Panther Audit Logs
      • Querying and Writing Detections for Panther Audit Logs
      • Panther Audit Log Actions
    • Notifications and Errors (Beta)
      • System Errors
    • Panther Deployment Types
      • SaaS
      • Cloud Connected
        • Configuring Snowflake for Cloud Connected
        • Configuring AWS for Cloud Connected
        • Pre-Deployment Tools
      • Legacy Configurations
        • Snowflake Connected (Legacy)
        • Customer-configured Snowflake Integration (Legacy)
        • Self-Hosted Deployments (Legacy)
          • Runtime Environment
  • Panther Developer Workflows
    • Panther Developer Workflows Overview
    • Using panther-analysis
      • Public Fork
      • Private Clone
      • Panther Analysis Tool
        • Install, Configure, and Authenticate with the Panther Analysis Tool
        • Panther Analysis Tool Commands
        • Managing Lookup Tables and Enrichment Providers with the Panther Analysis Tool
      • CI/CD for Panther Content
        • Deployment Workflows Using Panther Analysis Tool
          • Managing Panther Content via CircleCI
          • Managing Panther Content via GitHub Actions
        • Migrating to a CI/CD Workflow
    • Panther API
      • REST API (Beta)
        • Alerts
        • Alert Comments
        • API Tokens
        • Data Models
        • Globals
        • Log Sources
        • Queries
        • Roles
        • Rules
        • Scheduled Rules
        • Simple Rules
        • Policies
        • Users
      • GraphQL API
        • Alerts & Errors
        • Cloud Account Management
        • Data Lake Queries
        • Log Source Management
        • Metrics
        • Schemas
        • Token Rotation
        • User & Role Management
      • API Playground
    • Terraform
      • Managing AWS S3 Log Sources with Terraform
      • Managing HTTP Log Sources with Terraform
    • pantherlog Tool
    • Converting Sigma Rules
  • Resources
    • Help
      • Operations
      • Security and Privacy
        • Security Without AWS External ID
      • Glossary
      • Legal
    • Panther System Architecture
Powered by GitBook
On this page
  • Streaming rules
  • Data latency and timing considerations
  • Examples
  • Restrict AWS console access to specific IP addresses
  • Command and Control (C2) beacon detection
  • How well is my endpoint monitoring working?
  • Unusual Okta logins
  • Detecting password spraying
  • Detecting DNS tunnels
  • Monthly reporting of cloud infrastructure
  • Database (Snowflake) monitoring

Was this helpful?

  1. Investigations & Search
  2. Saved and Scheduled Searches

Scheduled Search Examples

PreviousBehavioral Analytics and Anomaly Detection Template Macros (Beta)NextSearch History

Last updated 9 days ago

Was this helpful?

This page contains common use cases and example searches you may want to use while investigating suspicious activity in your logs.

The examples below will require some customization to the local environment to be effective. Note that all queries should control the result size. This can be done with a LIMIT or GROUP BY clause.

Your company will incur costs on your data platform every time a Scheduled Search runs. Please make sure that your queries can complete inside the specified timeout period.

All examples in this section use Snowflake style SQL.

Athena limitations and considerations
  • All Athena queries should be qualified with partition columns (year, month, day, hour) for performance reasons.

  • Athena (Presto) has issues with returning query results as proper JSON for complex objects. This can lead to unparsable results in Scheduled Rules where the Python rules engine will read the result as a JSON object.

    • To work around this limitation of Athena you should return scalar objects in your Scheduled Queries NOT complex objects. If multiple elements in a complex object are needed in the result then extract them in the SQL as separate scalar columns.

Streaming rules

Panther enables you to run (on an interval) and, in concert with a Scheduled Rule, allows you to create detections that work over large time spans and aggregate data from multiples sources.

When possible, try to implement detections using Streaming Rules. Streaming Rules are low latency and less expensive compared to Scheduled Searches. However, there are cases when more context is required for a detection and then a Scheduled Search is the appropriate solution.

Data latency and timing considerations

In order to write effective Scheduled Rules, you need to understand the latency of the data from the time the event is recorded until it reaches Panther. Use this information to adjust the scheduled and window of time used accordingly.

For example, AWS CloudTrail data has a latency of about 10 minutes. This is due to AWS limitations and not due to Panther functionality. If you want to analyze the last hour of data, as a best practice we recommend that you schedule the search to run 15 minutes past the hour.

This is not a consideration for Panther streaming rules, since they are applied when the data is first processed as it comes into the system. Since Scheduled Searches periodically look back at accumulated data, timing considerations are important.

Examples

Restrict AWS console access to specific IP addresses

Let's start with a very simple end to end example to better understand the process of creating a detection using a Scheduled Search and a Scheduled Rule. Let's assume you very carefully restrict access to the AWS console to IP addresses from with company IP space. In order to verify this control you want to check that all AWS console logins have a sourceIPAddress from within this IP space. You keep a table of these IP blocks in the datalake. NOTE: this example uses a simple equality join operation and assumes that the table of IP blocks are all /32 addresses. In a realistic implementation the IP block table would have CIDR blocks and the check would be to find those not inside the CIDR blocks. This is left as an exercise for the reader.

Let's assume we schedule a rule to run every 15 minutes, checking the previous 30 minutes (we are using long window handle inherent delay in data associated with CloudTrail).

Full disclosure: you could implement this detection using a Streaming Rule if you managed the IP whitelist table in Dynamodb or S3. For very high volume log sources it will be more efficient to do a periodic batch join as below.

The query to detect these events is:

SELECT
    ct.*
FROM
    panther_logs.public.aws_cloudtrail ct LEFT OUTER JOIN infrastructure.networking.egress_blocks egress
        ON (ct.sourceIPAddress = egress.ip)
WHERE
    p_occurs_since('30 minutes') 
    AND
    ct.eventtype = 'AwsConsoleSignIn'
    AND 
    egress.ip IS NULL -- NOT matching!
LIMIT 1000 -- we don't expect many of these BUT we have this here for safety!
panther_logs.public.aws_cloudtrail
| where p_event_time > time.ago(30m) 
    and eventtype == 'AwsConsoleSignIn' 
| join kind=leftouter egress=(infrastructure.networking.egress_blocks) 
     on $left.sourceIPAddress == $right.ip
| where egress.ip == null
| limit 1000

Since the output of a Scheduled Search flows through a Scheduled Rule (in Python) it is important to keep the number of rows returned carefully controlled. It is recommended to always provide a LIMIT clause or use GROUP BY aggregations that return limited number of rows (less than a few thousand maximum).

To implement this:

  1. Make sure the Scheduled Search is set to active.

A Scheduled Rule has all the capability of a streaming rule, allowing you to customize alerts and direct the destinations. The deduping in Panther prevents alert storms, in the above rule we use the sourceIPAddress dedupe which will only create 1 alert per 30 minutes.

This pattern of joining to a list can also be used for IOC detections (keeping a table of IOCs such as TOR Exit Nodes, malware hashes ,etc).

Command and Control (C2) beacon detection

In this example we will create a very simple but effective behavioral detection that uses aggregation to find C2 beaconing.

This is an oversimplified detection for illustration purposes only. Using this without refinements such as allowlisting and tuning thresholds can cause excessive false positives (there are many non-malicious processes that "beacon"). That said, on well understood networks and using the appropriate allowlisting, this technique can be very effective.

We will define a C2 beacon as any IP activity that happens at most five times per day, and repeats for more than three days. To implement this:

WITH low_and_slow_daily_ip_activity AS (
SELECT
    date(p_event_time) as day,
    srcAddr as beacon_ip
FROM
    panther_logs.public.aws_vpcflow
WHERE
    p_occurs_since('1 week')
GROUP BY 1,2
HAVING count(1) <= 5 -- only those will little activity, this requires tuning
)
SELECT
 beacon_ip,
 count(1) as days
FROM
 low_and_slow_daily_ip_activity
GROUP BY 
 1
HAVING days >= 3 -- only those that have activity at least this many days
LIMIT 20 -- avoid alert storms!
panther_logs.public.aws_vpcflow
| where p_event_time > time.ago(7d)
| summarize count=agg.count() by day=time.trunc('d', p_event_time), beacon_ip=srcAddr
| where count <= 5  // only those will little activity, this requires tuning
| summarize days=agg.count() by beacon_ip
| where days >= 3   // only those that have activity at least this many days
| limit 20          // avoid alert storms!

To implement this:

  1. Make sure the Scheduled Search is set to active.

How well is my endpoint monitoring working?

There are many interesting questions that can be asked of this data but for this example we will specifically ask the question: "Which endpoints have not reported ANY data in the last 24 hours?"

In CrowdStrike logs the unique id for the deployed agent is called an aid . The CMDB has a mapping of aid to reference data. For this example we will assume it has the attributes employee_name, employee_group and last_seen. The employee related attributes help identify who currently uses the endpoint and the last_seen is a timestamp we assume is updated by a backend process that tracks network activity (e.g, VPN access, DHCP leases, Authentication, liveliness detections, etc).

To answer this question, we want to know which agents in the CMDB that do have network activity in the last 24 hours but do not have any CrowdStrike activity, which may indicate the agent is not running or has been disabled (indicating a coverage gap). The query below will compute a report by employee group that includes the specific suspect endpoints:

WITH active_aids AS (
SELECT
     DISTINCT aid -- just match the unique agent ids
FROM panther_logs.public.crowdstrike_aidmaster
WHERE p_occurs_since('1 day') -- in last 24 hours of log data
)
SELECT
    cmdb.employee_group,
    count(1) AS num_inactive_endpoints,
    ARRAY_AGG(
        DISTINCT cmdb.aid || ' ' || cmdb.employee_name || ' ' || cmdb.employee_group
    ) as endpoints -- here we collect the specific endpoints
FROM 
  infrastructure.inventory.cmdb AS cmdb LEFT OUTER JOIN active_aids cs USING(aid)
WHERE
  cs.aid IS NULL -- NOT matching any log data
AND 
  cmdb.last_seen BETWEEN current_date - 1 AND current_date -- is likely active
GROUP BY 1
ORDER BY 2 desc
let active_aids = panther_logs.public.crowdstrike_aidmaster
| where p_event_time > time.ago(1d)
| summarize by aid;

infrastructure.inventory.cmdb
| where last_seen > time.ago(1d)
| join kind=leftouter aa=(active_aids) on $left.aid == $right.aid
| where aid == null
| extend endpoints=strings.cat(aid, ' ', employee_name, ' ', employee_group)
| summarize num_inactive_endpoints=agg.count(),
            endpoints=agg.make_set(endpoints) by employee_group 
| sort num_inactive_endpoints

To implement this:

  1. Make sure the Scheduled Search is set to active.

The events associated with the alert can be reviewed by an analyst which will be at most one per employee group. The "hits" are accumulated in endpoints using the employee info for easy vetting. As with all Panther rules, you have the flexibility to customize destinations of alert. For example, if the employee_group is C-Suite then perhaps that generates a page to the oncall, while the default alerts simply go to a work queue for vetting the next day.

Unusual Okta logins

The challenge is defining "suspicious". One way to define suspicious is deviation from normal. If we can construct a baseline for each user, then we could alert when there is a significant change.

That sounds good, but now we have to define "significant change" in a way that generates useful security findings (and not many false positives). For this example we will target significant changes to the client information that might indicate stolen credentials. NOTE: the Okta data is very rich in context, this is just one simple example of how to make use of this data.

Because of VPNs and proxies, it is often not practical to just use specific IP addresses or related geographic information to identity suspect activity. Similarly, users may change their device because they are using a new one, or they may make use of multiple devices. We expect significant variation between legitimate users. However, for any particular user we expect there to be more consistency over time.

For this example, we will characterize "normal" by computing for each actor, for up to 30 days in the past:

  • unique auth clients used

  • unique os versions used

  • unique devices used

  • unique locations used (defined as: country, state, city)

We will define events that do NOT match ANY the four dimensions as "suspicious". This means:

  • We will not get alerts if they get a new device.

  • We will not get alerts when they change location.

  • We will get alerts when all of the attributes change at once,

    and we are assuming this is both anomalous and interesting from the security point of view.

We will also NOT consider actors unless they have at least 5 days of history, to avoid false positives from new employees.

Assume we schedule this to run once a day for the previous day.

This is just an example, and requires tuning like any other heuristic but has the benefit of being self calibrating per actor.

The SQL to compute the above is:

WITH actor_baseline AS (
  SELECT
    actor:id as actor_id,
    ARRAY_AGG (DISTINCT client:id) as client_id_list,
    ARRAY_AGG (DISTINCT client:userAgent.os)  as client_os_list,
    ARRAY_AGG (DISTINCT client:device) as client_devices_list,
    ARRAY_AGG (DISTINCT 
       client:geographicalContext:country || client:geographicalContext:state || client:geographicalContext:city)
      as client_locations_list
  FROM
    panther_logs.public.okta_systemlog
  WHERE
    p_occurs_since('30 days') 
  GROUP BY 1
  HAVING
    COUNT(DISTINCT date(p_event_time)) > 5 -- at least 5 days of history
)
-- compare current day to baseline, returning events that do not match ANY of the baseline attributes
SELECT
  logs.*
FROM
  panther_logs.public.okta_systemlog logs JOIN actor_baseline bl ON (actor:id = bl.actor_id)
WHERE
  p_occurs_since('1 day') 
  AND
  NOT ARRAY_CONTAINS(logs.client:id::variant, bl.client_id_list)
  AND
  NOT ARRAY_CONTAINS(logs.client:userAgent:os::variant, bl.client_os_list)
  AND
  NOT ARRAY_CONTAINS(logs.client:device::variant, bl.client_devices_list)
  AND
  NOT ARRAY_CONTAINS(
          (client:geographicalContext:country || 
           client:geographicalContext:state || 
           client:geographicalContext:city)::variant, bl.client_locations_list)

Recomputing these baselines each time a search is run is not very efficient. In the future Panther will be supporting the ability to create summary tables so that methods such as described above can be made more efficient.

let actor_baseline = panther_logs.public.okta_systemlog
| where p_event_time > time.ago(30d)
| summarize unique_days=agg.count_distinct(time.trunc('d', p_event_time)),
            client_id_list=agg.make_set(client.id),
            cliend_os_list=agg.make_set(client.userAgent.os),
            client_devices_list=agg.make_set(client.device),
            client_locations_list=agg.make_set(strings.cat(client.geographicalContext.country, 
                                                           client.geographicalContext.state, 
                                                           client.geographicalContext.city)) by actor_id=actor.id
| where unique_days > 5;

panther_logs.public.okta_systemlog
| where p_event_time > time.ago(1d)
| join kind=inner ab=(actor_baseline) on $left.actor.id == $right.actor_id
| where client.id not in ab.client_id_list
    and client.userAgent.os not in ab.client_os_list
    and client.device not in ab.client_devices_list
    and strings.cat(client.geographicalContext.country, 
                    client.geographicalContext.state, 
                    client.geographicalContext.city) not in ab.client_locations_list

Detecting password spraying

Password spraying is an attack that attempts to access numerous accounts (usernames) with a few commonly used passwords. Traditional brute-force attacks attempt to gain unauthorized access to a single account by guessing the password. This can quickly result in the targeted account getting locked-out, as commonly used account-lockout policies allow for a limited number of failed attempts (typically three to five) during a set period of time. During a password-spray attack (also known as the “low-and-slow” method), the malicious actor attempts a single commonly used password (such as ‘password123’ or ‘winter2017’) against many accounts before moving on to attempt a second password, and so on. This technique allows the actor to remain undetected by avoiding rapid or frequent account lockouts.

The key to detecting this behavior is to aggregate over time and look at the diversity of usernames with failed logins. The example below uses CloudTrail but a similar technique and be used with any authentication log. The thresholds chosen will need to be tuned to the target network.

SELECT
  -- this information will be in the alert events
  awsRegion as region,
  recipientAccountId as accountid,
  COUNT(DISTINCT useridentity:userName) as distinctUserNames,
  COUNT(1) as failures,
  MIN(p_event_time) as first_attempt,
  MAX(p_event_time) as last_attempt
FROM
  panther_logs.public.aws_cloudtrail
WHERE
  -- this is Panther macro that looks back 3600 seconds (1 hour)
  p_occurs_since('1 hour') 
  AND
  eventtype = 'AwsConsoleSignIn'
  AND
  responseElements:ConsoleLogin = 'Failure'
GROUP BY
  region, accountid
HAVING
  distinctUserNames > 5
   AND
  failures > 10
panther_logs.public.aws_cloudtrail
| where p_event_time > time.ago(1h)
    and eventType == 'AwsConsoleSignIn'
    and responseElements.ConsoleLogin == 'Failure'
| summarize distinctUserNames=agg.count_distinct(useridentity.userName),
            failures=agg.count(),
            first_attempt=agg.min(p_event_time),
            last_attempt=agg.max(p_event_time) by region=awsRegion, accountid=recipientAccountId
| where distinctUserNames > 5 and failures > 10

Detecting DNS tunnels

Since DNS cannot generally be blocked on most networks, DNS based data exfiltration and C2 can be extremely effective. There are many tools available to create DNS-based tunnels. Not all DNS tunnels are malicious, ironically, many anti-virus tools use DNS tunnels to send telemetry back "home". Most security-minded people find DNS tunnels un-nerving, so detecting them on your network is useful. Simple traffic analysis can easily find these tunnels but because of legitimate tunnels, this below example will require some tuning to the local environment for both thresholds and whitelisting.

We will define a potential DNS tunnel as a DNS server (port 53) that moves enough data to be interesting with an hour's time to only a few UNIQUE domains.

Assume we run this search every hour, looking back 1 hour to identify these tunnels:

SELECT
  account_id,
  region,
  vpc_id,
  srcAddr, -- outside
  srcIds:instance, -- inside

  COUNT(1) as message_count,
  ARRAY_AGG(DISTINCT query_name) as query_names
FROM
  panther_logs.public.aws_vpcdns
WHERE
  p_occurs_since('1 hour')
  AND
  -- simple whitelisting
  query_name NOT LIKE '%amazonaws.com'
GROUP BY
  1,2,3,4,5
HAVING
  message_count >= 1000   -- decent amount of activity in an hour
   AND
  ARRAY_SIZE(query_names) <= 2 -- only a small number of distinct domains (not likely a real dns server!)
panther_logs.public.aws_vpcdns
| where p_event_time > time.ago(1h) and not strings.like(query_name, '%amazonaws.com')
| summarize message_count=agg.count(), 
            query_names=agg.make_set(query_name) by account_id,
                                                    region,
                                                    vpc_id,
                                                    srcAddr,         // outside
                                                    srcIds.instance, // inside
| where message_count >= 1000        // decent amount of activity in an hour
    and arrays.len(query_names) <=2  // only a small number of distinct domains (not likely a real dns server!)

Monthly reporting of cloud infrastructure

A simple example is the below report that can be scheduled run on the first of the month for the previous month to show the activity in the monitored accounts.

WITH monthly_report AS (
  SELECT
    accountId,
    changeType,
    ARRAY_AGG(DISTINCT resourceType) as resourceTypes,
    count(1) as objects
  FROM
    panther_cloudsecurity.public.resource_history
  WHERE
      DATE_TRUNC('MONTH', p_event_time) = DATE_TRUNC('MONTH', current_date - 1)  -- all last month cuz we run on the 1st!
    AND
    changeType <> 'SYNC' -- these are not changes but full records
  GROUP BY 1,2
)
-- We want the whole report as a single row of JSON objects we can forward in the Python rule
SELECT
  ARRAY_AGG(OBJECT_CONSTRUCT(*)) as monthly_report
FROM monthly_report
let monthly_report = panther_cloudsecurity.public.resource_history
// TODO: subtract from date
| where time.trunc('month', p_event_time) == time.trunc('month', time.now()-1d)
    and changeType != 'SYNC' // these are not changes but full records
| summarize resourceTypes=agg.make_set(resourceType),
            objects=agg.count() by accountId, changeType;
            
// We want the whole report as a single row of JSON objects we can forward in the Python rule
monthly_report
| project report=object('accountId', accountId, 
                        'changeType', changeType, 
                        'resourceTypes', resourceTypes,
                        'objects', objects)
| summarize monthly_report=agg.make_set(report)

Example output:

{
    "monthly_report": [
        {
            "ACCOUNTID": "34924069XXXX",
            "CHANGETYPE": "DELETED",
            "OBJECTS": 8,
            "RESOURCETYPES": [
                "AWS.IAM.Role"
            ]
        },
        {
            "ACCOUNTID": "34924069XXXX",
            "CHANGETYPE": "MODIFIED",
            "OBJECTS": 2388,
            "RESOURCETYPES": [
                "AWS.CloudTrail.Meta",
                "AWS.S3.Bucket",
                "AWS.CloudFormation.Stack",
                "AWS.CloudTrail",
                "AWS.IAM.Role"
            ]
        },
        {
            "ACCOUNTID": "34924069XXXX",
            "CHANGETYPE": "CREATED",
            "OBJECTS": 11,
            "RESOURCETYPES": [
                "AWS.IAM.Role",
                "AWS.CloudFormation.Stack",
                "AWS.KMS.Key"
            ]
        }
    ]
}

The resource_history table has detail down to the specific resource, so there are variations of the above search that can be more detailed if desired.

Database (Snowflake) monitoring

Databases holding sensitive data require extensive security monitoring as they are often targets of attack.

These queries require that Panther's read-only role has access to the snowflake.account_usage audit database (this may need to be done by the Snowflake admins).

 USE ROLE accountadmin;
 GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE panther_readonly_role;

This query looks for patterns of failed logins by username and should be run on a regular schedule:

 --return users with more than 2 failed logins in the previous 24 hours
  --this was adapted from a SnowAlert query
  SELECT 
    user_name,
    reported_client_type,
    ARRAY_AGG(DISTINCT error_code),
    ARRAY_AGG(DISTINCT error_message),
    COUNT(event_id) AS counts
  FROM snowflake.account_usage.login_history
  WHERE 1=1
    AND DATEDIFF(HOUR, event_timestamp, CURRENT_TIMESTAMP) < 24
    AND error_code IS NOT NULL
  GROUP BY reported_client_type, user_name
  HAVING counts >=3;
snowflake.account_usage.login_history
| where time.diff('h', p_event_time, time.now()) < 24 and error_code != null
| summarize error_codes=agg.make_set(error_code),
            error_msgs=agg.make_set(error_message),
            counts=agg.count() by reported_client_type, user_name
| where counts >= 3

Snowflake failed logins by single IP looks at login attempts by IP over 24 hours and returns IPs with more than 2 failed logins. This could be scheduled to run every 24 hours to highlight potentially suspicious activity. The effectiveness of this approach may depend on how your enterprise handles company-internal IP addresses.

 --return IPs with more than 2 failed logins in the previous 24 hours
  --this was adapted from a SnowAlert query
  SELECT 
    client_ip,
    MIN(event_timestamp) event_start,
    MAX(event_timestamp) event_end,
    timediff(second, event_start, event_end) as event_duration,
    reported_client_type,
    ARRAY_AGG(DISTINCT error_code),
    ARRAY_AGG(DISTINCT error_message),
    COUNT(event_id) AS counts
  FROM snowflake.account_usage.login_history
  WHERE 1=1
    AND DATEDIFF(HOUR,  event_timestamp, CURRENT_TIMESTAMP) < 24
    AND error_code IS NOT NULL
  GROUP BY client_ip, reported_client_type
  HAVING counts >= 3;
snowflake.account_usage.login_history
| where time.diff('h', event_timestamp, time.now()) < 24 and error_code != null
| summarize error_codes=agg.make_set(error_code),
            error_msgs=agg.make_set(error_message),
            event_start=agg.min(p_event_time),
            event_end=agg.max(p_event_time),
            counts=agg.count() by client_ip, reported_client_type
| extend event_duration=time.trunc('s', event_end) - time.trunc('s', event_start)
| where counts >= 3

Grants of admin rights in Snowflake, looking back 7 days. This is not necessarily suspicious, but possibly something the Snowflake admins may want to keep track of.

SELECT
    current_account() AS environment
     , REGEXP_SUBSTR(query_text, '\\s([^\\s]+)\\s+to\\s',1,1,'ie') AS role_granted
     , start_time AS event_time
     , query_text AS event_data
     , user_name AS user_name
     , role_name
     , query_type
     , query_id AS query_id
FROM snowflake.account_usage.query_history
WHERE 1=1
  AND DATEDIFF(DAY,  start_time, CURRENT_TIMESTAMP) <= 7
  AND query_type='GRANT'
  AND execution_status='SUCCESS'
  AND (role_granted ILIKE '%securityadmin%'  OR role_granted ILIKE '%accountadmin%' OR role_granted ILIKE '%admin%')
snowflake.account_usage.query_history
| where time.diff('d', event_timestamp, time.now()) < 7
    and query_type == 'GRANT'
    and execution_status == 'SUCCESS'
    and (strings.ilike(role_granted, '%securityadmin%') OR 
         strings.ilike(role_granted, '%accountadmin%') OR 
         strings.ilike(role_granted, '%admin%'))
| project event_time=start_time,
          event_data=query_text,
          user_name,
          role_name,
          query_type,
          query_id,
          role_granted=re.substr(query_text, '\\s([^\\s]+)\\s+to\\s',1,1,'ie')

Querying account usage views

Note the p_occurs_since() is a Panther to make creating Scheduled Queries easier.

It is not currently possible to create a in . See the to learn more.

Create a Scheduled Search .

In the example screen shot below, a period of every 30 minutes is selected.

Make a targeted at the output of the Scheduled Search.

It is not currently possible to create a in . See the to learn more.

Create a Scheduled Search .

In the example screen shot below, we use a Cron expression to have this run at 1 minute after midnight every day.

Make a targeted at the output of the Scheduled Search:

For this hypothetical example, we will assume you are using CrowdStrike as your endpoint monitoring software. Panther is configured to ingest your logs and you have a populated that maps the deployed agents to their internal associated user(s).

It is not currently possible to create a in . See the to learn more.

Create a Scheduled Search .

In the example screen shot below, we use a Cron expression to have this run at 1 minute after midnight every day.

Make a targeted at the output of the Scheduled Search:

The provide the answers to "who", "with what device" and the "where" questions associated with an event. This information can be used to identify suspicious behavior, for example an attacker using stolen credentials.

It is not currently possible to create a in . See the to learn more.

It is not currently possible to create a in . See the to learn more.

It is not currently possible to create a in . See the to learn more.

Given Panther can report on your AWS infrastructure, you can use the resource_history table to compute activity statistics that may be of interest to operations as well a security.

It is not currently possible to create a in . See the to learn more.

It's also possible to monitor Snowflake activity using the integration.

It is not currently possible to create a in . See the to learn more.

It is not currently possible to create a in . See the to learn more.

It is not currently possible to create a in . See the to learn more.

See Snowflake's documentation for additional .

Scheduled Searches
Saved Searches
SQL macro
CMDB
Scheduled Rule
Okta logs
Cloud Security
Snowflake Audit Logs
examples on querying account usage
Scheduled Search
PantherFlow
Scheduled Rule
Scheduled Search
PantherFlow
Scheduled Rule
Scheduled Search
PantherFlow
Scheduled Search
PantherFlow
Scheduled Search
PantherFlow
Scheduled Search
PantherFlow
Scheduled Search
PantherFlow
Scheduled Search
PantherFlow
Scheduled Search
PantherFlow
Scheduled Search
PantherFlow
Limitations of PantherFlow
Limitations of PantherFlow
Limitations of PantherFlow
Limitations of PantherFlow
Limitations of PantherFlow
Limitations of PantherFlow
Limitations of PantherFlow
Limitations of PantherFlow
Limitations of PantherFlow
Limitations of PantherFlow
CrowdStrikeRule
by following these instructions
by following these instructions
by following these instructions
The image shows an example function written in Python. The Test box is open and has an example test written in it.