Subsearch

Anomali Search lets you embed a search query within the primary search and use the results of this embedded query as parameters within the primary search, enabling you to create complex search queries and perform correlation across multiple datasets.

With the ability to filter, manipulate, and transform results within the primary search, subsearches let you combine multiple search queries, yield the results, and join them using one single query, leading to easier, faster, and more efficient understanding and use of Search.

Additionally, the search bar displays auto-complete suggestions to help you build a valid search query, simplifying the search experience.

Benefits

By using subsearches, you can:

• Simplify search queries by embedding multiple queries within the same primary search.

• Create more powerful queries that dynamically adapt according to the results of the embedded query.

• Enhance the quality of threat hunting by efficiently querying multiple datasets simultaneously.

• Reduce workload by eliminating the need to create multiple queries.

How does it work?

Use square brackets ([]) to embed a embedded query within the primary search query and use the results for further analyses.

1
2
3
4
5
6
| where sourcetype = "aws_vpc_flow" 
| join left [
    observables 
    | where ioc_type = "ip" and confidence > 75 and severity != "low"
] 
on dest_ip = indicator output indicator, confidence, severity, feed_id, tags

In the example above, the subsearch captures IP addresses for AWS VPC Flow logs that have high severity with a confidence score > 75.

Key Highlights

• You can define up to 6 subsearches in one primary search query.

• You cannot perform subsearch nesting, that is, you cannot embed one subsearch inside another subsearch.

• A subsearch will timeout after 180 seconds.

• Only the first 10000 result rows are considered as the output from a subsearch, which are then passed on to the primary search query.

• You must provide as output only one field with the fields operator, if you use the where and in operators in a subsearch filter.

• You can define a search time range that is different from the range of the primary search by using the earliest | latest function within the where operator.

  • Example: | aggr count by sourcetype | appendtable [| where earliest=10d and latest=5d | aggr count by sourcetype]

• When used with appendtable, a subsearch will fail if the primary search does not have any data. However, a regular primary search with an empty subsearch will function correctly.
  

Caution: This is currently a known issue. A resolution will be deployed in an upcoming release.

Use Case

Filtering

Filter the primary search results by defining conditional filters within the embedded search. The following subsearch returns the top 10 users that encountered a failed login attempt using the top operator as its conditional filter.

1
2
3
4
5
6
| where event_name contains_ci "userlogonfailed"
| where user in [
    | where event_name contains_ci "userlogonfailed"
    | top 10 user
    | fields user
]

Using OCSF Fields in Subsearches

Subsearches support OCSF Version 1.2 fields with dotted notation. Field references from parent queries are properly resolved in subsearch contexts, allowing you to construct complex queries on OCSF fields.

Example 1: Join with subsearch using OCSF fields

1
2
3
4
5
6
7
ocsf
| fields metadata.product.name, activity_name
| dedup metadata.product.name
| join left [
    ocsf
    | aggr count as cnt by metadata.product.name
] on metadata.product.name=metadata.product.name output cnt

Example 2: Join with observables using subsearch on OCSF endpoint field

1
2
3
4
5
6
7
ocsf
| fields metadata.product.name, activity_name , dst_endpoint.ip
| join left [
  observables
  | where ioc_type = 'ip' and confidence > 75 and severity!='low'
  | dedup indicator
] on dst_endpoint.ip = indicator output indicator, confidence, severity, feed_id, tags

Example 3: Nested subsearches with IN operator using OCSF user fields

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ocsf
| where metadata.product.name!='unknown_source' and isnotnull(activity_name) and (actor.user.name in
  [ocsf
    | where isnotnull(actor.user.name) and activity_name contains_ci 'Success'
    | top 2 actor.user.name | fields actor.user.name
  ]
OR actor.user.name in
  [ocsf
    | where isnotnull(actor.user.name) and activity_name contains_ci 'create'
    | aggr count as cnt by actor.user.name
    | sort cnt desc
    | head 2
    | fields actor.user.name
  ])
| fields activity_name, actor.user.name

Generation

Generate values to be used by parameters in the primary search. In the following example, the subsearch generates a list of IP addresses with high severity and more than 75% confidence, which is then joined with destination IP addresses to obtain the final result.

| where sourcetype = "aws_vpc_flow" 
| join left [
    observables 
    | where ioc_type = "ip" and confidence > 75 and severity != "low"
] 
on dest_ip = indicator output indicator, confidence, severity, feed_id, tags

Manipulation

Append results from different searches into the primary search. In the example below, the subsearch fetches the command string of the parent process and appends them to the eventlog table.

eventlog | appendcols [iocmatch | field parent_command_line]

Operator Scope

Subsearches allow you to use all AQL Search operators. Once a subsearch runs successfully, its results are passed on as parameters to the primary search.

Primary searches that involve embedded queries support the following operators:

• where

• join

• appendtable

• appendcols

• set

• turbosearch

Subsearch Audit

You can obtain audit logs for subsearch using the audit keyword, one log for each subsearch and one entry for the primary search. In the following example, the user yourusername issues a search with a subsearch, and then obtains audit logs accordingly.

audit 
| where user contains 'yourusername' 
| fields action, search_job_id, search_query, response_digest