Sample YARA-L queries for dashboards

Supported in:

Google secops SIEM

This document provides query examples of common dashboards use cases, organized by data source. For more information about dashboards, see Dashboards overview.

Note: When you define YARA-L queries specifically for dashboards, a match section is required. This section defines the time window and the variables used to aggregate or correlate the events displayed in the dashboard. For more information, see the Dashboards overview.

User login events

This section shows examples for user login events where the focus is on tracking authentication patterns and security results.

Example: Group by login status

The following YARA-L query counts user logins, grouping them by login status of "ALLOW" or "BLOCK":

//USER_LOGIN by status
metadata.event_type = "USER_LOGIN"
$security_result = security_result.action
$security_result = "BLOCK" OR $security_result = "ALLOW"

match:
    $security_result

outcome:
    $event_count = count_distinct(metadata.id)

Example: Success over time

The following YARA-L query counts successful user logins over time:

//successful sign-ins over time 
metadata.event_type = "USER_LOGIN"
$security_result = security_result.action
$security_result = "ALLOW"
$date = timestamp.get_date(metadata.event_timestamp.seconds, "America/Los_Angeles")

match:
    $security_result, $date

outcome:
    $event_count = count_distinct(metadata.id)

order:
    $date desc

Example: Geographic distribution

The following YARA-L query counts user logins, grouped by countries country or region:

//user sign-ins by country
metadata.event_type = "USER_LOGIN"
$country = principal.location.country_or_region
$country != ""

match:
    $country

outcome:
    $event_count = count_distinct(metadata.id)

order:
    $event_count desc

Ingestion metrics

Use ingestion metrics to monitor the health and volume of data that enters the system.

For more information about field descriptions in the following examples, see Ingestion metrics schema.

The following YARA-L query counts log, event, and dropcounts, grouped by log type:

//log count, event count, and drop count by log type
ingestion.log_type != ""
$log_type = ingestion.log_type

match:
    $log_type

outcome:
    $log_count = sum(ingestion.log_count)
    $event_count = sum(ingestion.event_count)
    $drop_count = sum(ingestion.drop_count)

order:
    $log_count desc

Detections

Detections and Indicators of Compromise (IoCs) track identified threats.

For more information about field descriptions in the following examples, see Detection fields.

Example: Detection count

The following YARA-L query counts detections, grouped by severity and date:

//Detection count by severity over time
$date = timestamp.get_date(detection.created_time.seconds)
$severity = detection.detection.severity

match:
    $date, $severity

outcome:
    $detection_count = count_distinct(detection.id)

order:
    $date asc

Example: Top 10 rules

The following YARA-L query retrieves the top 10 rule names, ranked by their detection count (or frequency):

//top ten rule names by detection count
$rule_name = detection.detection.rule_name

match:
    $rule_name

outcome:
    $count = count_distinct(detection.id)

order:
    $count desc

limit:
    10

Example: Top 10 IP addresses

The following YARA-L query retrieves the top 10 IP addresses from principal, targe and source fields, ranked by their detection count:

$ip = group(detection.collection_elements.references.event.principal.ip,detection.collection_elements.references.event.target.ip,detection.collection_elements.references.event.src.ip)
$ip != ""

match:
    $ip

outcome:
    $count = count(detection.id)

order:
    $count desc

limit:
    10

IoCs

IoCs help = security teams quickly identify and respond to threats.

For more information about field descriptions in the following examples, see IoC fields.

Example: Top 10 IoCs

The following YARA-L query retrieves the top 10 IOCs, ranked by count:

//Top 10 IOCs by count
$ioc_value = ioc.ioc_value

match:
    $ioc_value

outcome:
    $ioc_count = count(ioc.ioc_value)

order:
    $ioc_count desc
limit: 
    10

Rules

Rule health and management metrics provide insights into rule performance, authorship trends, and operational status.

For more information about field descriptions in the following examples, see Rule fields.

Example: Rules created per month

The following YARA-L query retrieves the rules created per month:

$month_wise = timestamp.get_timestamp(rules.create_time.seconds,"%y-%m")

match:
    $month_wise

outcome:
    $rule_count = count(rules.name)

Example: Rule detection count

The following YARA-L query retrieves the rules with detection:

$rule_name = rules.display_name
$detection_count = rules.total_detection_count
$detection_count >0

match:
    $rule_name, $detection_count

Example: Rule status

The following YARA-L query retrieves the rules in ENABLED status:

$status = rules.live_status
$status = "ENABLED"

outcome:
 $rule_count = count(rules.name)

Example: Rule velocity

The following YARA-L query retrieves the rules created by day (by author):

$rule_author = rules.author

match:
    $rule_author by day

outcome:
    $count_of_rules = count(rules.name)

order:
    $count_of_rules desc

Example: Rules with text and time query

The following YARA-L query retrieves the all rules text and time query:

$name= rules.name
$display_name = rules.display_name
$author = rules.author
$severity = rules.severity
$live_status = rules.live_status
$alerting_status = rules.alerting
$detection_time = rules.latest_detection_time.seconds
$latest_version_time = rules.update_time.seconds
$detection_count = rules.total_detection_count
$rule_text = rules.rule_text

match:
   $name, $display_name, $live_status, $alerting_status, $severity, $author, $detection_time, $latest_version_time, $detection_count, $rule_text

order:
    $detection_count desc

Example: Rules not triggering

The following YARA-L query retrieves the Rules not triggering (rules with zero (0) detections):

$rule_name = rules.name
$display_name = rules.display_name
$detection_time = rules.latest_detection_time.seconds
$detection_time = 0

match:
    $rule_name, $display_name

Cases and alerts

Use cases and alerts to monitor operational SOC metrics, including alert categorization, case statuses, and incident resolution trends.

The following YARA-L queries help analyze cases and alert data. For more information about field descriptions in the following examples, see Cases and alerts.

Count cases by status

match:
   case.status

outcome:
   $count=count(case.name)

Count cases tagged as SUSPICIOUS

case.tags.name="SUSPICIOUS"

outcome:
   $count=count(case.name)

Calculate mean time to detect (in minutes)

$case_created_time = case.created_time.seconds
$alert_time = case.alerts.metadata.detection_time.seconds

outcome:
   $avg_time = math.round(window.avg($case_created_time - $alert_time)/60, 2)

Playbooks

Use Playbooks to monitor your automated response capabilities with queries designed to identify faulted actions and track real-time playbook performance.

The following YARA-L queries provide insights into Playbook executions. For more information about field descriptions in the following examples, see Playbooks.

Retrieve percentage of faulted actions

1=1
outcome:
   $faulted_action=sum(if(playbook.action.status="FAULTED", 1, 0))
   $total_actions=count(playbook.action.name)
   $percentage=($faulted_action/$total_actions)*100

Count running playbooks

playbook.status="IN_PROGRESS" OR playbook.status="PENDING_FOR_USER"
outcome:
   $count=count_distinct(playbook.name)

Case history

Use case history to track activity trends across the case lifecycle. For more information about field descriptions in the following example, see Case history.

The following YARA-L query retrieves the case history by activity count:

match:
    case_history.case_activity

outcome:
   $count=count_distinct(case_history.name)

What's next

Learn more about how to use functions to build dashboards using YARA-L 2.0 functions for Google Security Operations dashboards.