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 .

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 
 )) 
  
 $ 
 to 
 tal_actions 
 = 
 count 
 ( 
 playbook 
 . 
 action 
 . 
 name 
 ) 
  
 $ 
 percentage 
 = 
 ( 
 $ 
 faulted_action 
 / 
 $ 
 to 
 tal_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 .