Sample YARA-L queries for dashboards

Supported in:

Google secops SIEM

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

This section provides YARA-L query examples for monitoring user authentication activity. Use these patterns to identify successful logins, failed attempts, and potential credential abuse across your data sources.

Count logins by 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)

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

Track logins by location

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

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

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

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

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

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

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

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

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)

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

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

The following YARA-L query retrieves the Rules not triggering (rules with no 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

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

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 
 )) 
  
 $ 
 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

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 .