Sample YARA-L queries for dashboards

Supported in:

Google SecOps SIEM

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

The following queries are examples of common dashboards use cases, organized by data source.

UDM Events

The following YARA-L query provides a count of user logins grouped by the login status of ALLOW or BLOCK.

//user sign-ins 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 provides a count of 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 provides a count of 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 provides a log count, event count, and drop count 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 provides a count of detections 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 provides a list of the top ten rule names based on 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 provides a list of the top ten IPs based on 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 provides a list of top ten IOCs 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

Appendix

YARA-L 2.0 functions for Google Security Operations preview dashboards

In addition to the YARA-L 2.0 functions that Google Security Operations supports in the Detection Engine, the following functions can be used in queries to build charts.

math.log

Supported in:

Rules Search

math.log(numericExpression)

Description

Returns the natural log value of an integer or float expression.

Param data types

NUMBER

Return type

NUMBER

Example

math.log($e1.network.sent_bytes) > 20

math.round

Supported in:

Rules Search

math.round(numericExpression,decimalPlaces)

Description

Returns the value of a float expression rounded to the specified number of decimal places.

Param data types

NUMBER

Return type

NUMBER

Examples

math.round(10.7) // returns 11
math.round(1.2567, 2) // returns 1.25
math.round(-10.7) // returns -11
math.round(-1.2) // returns -1
math.round(4) // returns 4, math.round(integer) returns the integer

group

Supported in:

Rules Search

group(field1, field2, field3…)

Description

Groups fields of the same type into a placeholder.

Param data types

Event fields

Return type

Grouped event fields

Example

In the following example, the group() function gathers all the IP addresses found in the principal.ip, target.ip, and src.ip fields across all the events that triggered the detection. The IP addresses are added to the placeholder variable $ip. The rule then matches on the IP address and returns a count of distinct events for each unique IP address.

$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_distinct(detection.id)
order:
  $count desc

// Detection1: principal.ip = 1.1.1.1
// Detection2: src.ip = 1.1.1.1, target.ip = 2.2.2.2
// Detection3: target.ip = 1.1.1.1
// Detection4: principal.ip = 2.2.2.2

Result:

`$ip`	`$count`
1.1.1.1	3
2.2.2.2	2

Aggregate functions

All events that contain multiple values must be aggregated using aggregate functions. In addition to the existing aggregate functions, you can also use the following aggregate functions:

avg(): outputs the average over all possible values. Only works with integer and float.
stddev(): outputs the standard deviation over all possible values. Only works with integer and float.

avg

Supported in:

Rules Search

avg(numericExpression)

Description

The avg function returns the average of values within a numeric column. It ignores NULL values during the calculation. It is often used with match to calculate the averages within specific groups in the data.

Param data types

NUMBER

Return type

NUMBER

Code Samples

Example

Find all the events where target.ip is not empty. For all the events that match on principal.ip, store the average of metadata.event_timestamp.seconds in a variable called avg_seconds.

target.ip != ""
  match:
    principal.ip
  outcome:
    $avg_seconds = avg(metadata.event_timestamp.seconds)

stddev

Supported in:

Rules Search

stddev(numericExpression)

Description

The stddev function returns the standard deviation over all the possible values.

Param data types

NUMBER

Return type

NUMBER

Code Samples

Example

Find all the events where target.ip is not empty. For all the events that match on principal.ip, store the standard deviation of metadata.event_timestamp.seconds in a variable called stddev_seconds.

target.ip != ""
  match:
    principal.ip
  outcome:
    $stddev_seconds = stddev(metadata.event_timestamp.seconds)

IOC Fields

Fields
`ioc_value`	IOC indicator, can be either domain name or IP address
`ioc_type`	IOC type: can be either `IOC_TYPE_DOMAIN` or `IOC_TYPE_IP`
`feed_log_type`	IOC feed log type, for example, `ET_PRO_IOC`
`is_global`	If this is global IOC indicator
`day_bucket_seconds`	Day bucket when an IOC hit occurred
`category`	The category/type of this indicator
`confidence_score`	Raw confidence level from the IOC source
`feed_name`	Original feed this indicator originated from
`severity`	The indicator's raw severity
`ioc_ingest_time`	This IOC's first ingestion time
`asset`	Asset indicator
`location`	Physical location

Rule Sets Fields

Fields
`ruleset`	Display name
`ruleset_family`	Family name
`precise_alerting`	Alerting status of precise rules in the rule set
`precise_live`	Status of precise rules
`broad_alerting`	Alerting status of precise rules in the rule set
`broad_live`	Status of broad rules
`detection_timestamp`	Timestamp of the detection