Sample YARA-L queries for dashboards

Supported in:

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

YARA-L examples for user login events

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)

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

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

//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

The following YARA-L query counts log, event, and drop counts, 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

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

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

//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

The following YARA-L query retrieves the top 10 IP addresses, 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

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

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)

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

The following YARA-L query retrieves the rules in Enabled status

$status = rules.live_status
$status = "ENABLED"
outcome:
 $rule_count = count(rules.name)

Cases and alerts

The following YARA-L queries help analyze cases and alert data.

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

The following YARA-L queries provide insights into Playbook executions.

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

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.