AQL Best Practices

Use the recommendations below to optimize your queries and use less resources for their calculation.

Use superapi Efficiently

The superapi operator makes one API call per input row, when the call includes the input_fields parameter. When queries pipe many rows through endpoints that return large responses, the cumulative response size can exceed the 50 MB limit, causing some rows to be skipped.

When the total exceeds 50 MB, the operator stops making additional API calls. The size check occurs after each response is received.

If a call pushes the cumulative total over the 50 MB limit, that call completes and returns data normally, and only subsequent calls are skipped. Therefore, rows processed before the limit is reached return normally, while rows that would have been processed after the limit is reached receive a warning in their output instead of API results.

To avoid hitting this limit, reduce the scope of your API calls using the following practices:

1. Limit Input Rows

Add | limit N before | superapi to cap the number of rows that trigger API calls.

Inefficient Query	Recommended Query
| makeresults count=100 | streamstats count AS counter | superapi endpoint='/api/v1/intelligence/?limit=1000&offset=<<counter>>' input_fields='counter'	| makeresults count=100 | streamstats count AS counter | limit 20 | superapi endpoint='/api/v1/intelligence/?limit=1000&offset=<<counter>>' input_fields='counter'

2. Use a Smaller Page Size

Lower the limit= parameter in your endpoint URL to reduce the size of each individual API response.

Inefficient Query	Recommended Query
anomali_api_proxy | superapi endpoint='/api/v1/intelligence/?limit=1000'	anomali_api_proxy | superapi endpoint='/api/v1/intelligence/?limit=100'

3. Filter Before the API Call

Use | where conditions before | superapi to reduce the number of rows that need API enrichment.

Inefficient Query	Recommended Query
ocsf | superapi endpoint='/api/v1/actor/<<id>>/tipreport/' input_fields='id'	ocsf | where actor_name in ("APT27", "APT28") | superapi endpoint='/api/v1/actor/<<id>>/tipreport/' input_fields='id'

Use OCSF Index to Reduce Parsing Overhead

If your data exists in OCSF, the OCSF-normalized index pre-parses and structures all event data at ingestion time. Fields are extracted, typed, and stored as structured columns, so queries run directly against pre-processed data with no runtime parsing overhead. By contrast, the eventlog index parses raw messages at query time, reducing query speeds and making high-volume ingestion more expensive.

To reduce this parsing overhead, Anomali recommends the following practice when using the ocsf Version 1.2 index:

Filter on category_uid or category_name Early

Always narrow the scope by filtering on category_uid or category_name early in your query. This allows the query planner to target only the relevant category tables instead of scanning all of them, significantly reducing the amount of data scanned and improving query speed.

Inefficient Query	Recommended Query
ocsf | where metadata.log_name contains "windows"	ocsf | where category_uid in (4, 5) and metadata.log_name contains "windows"
ocsf | where src_ip = "192.168.1.1"	ocsf | where category_name in ("Network Activity") and src_ip = "192.168.1.1"

See OCSF Schema Overview for more information on OCSF Version 1.2 parsing and naming conventions.

Resolving error: "To optimize your experience and reduce your AVC usage, Anomali recommends reducing the time range to 24h to proceed."

When you issue a search, you might encounter the error mentioned above. To resolve this issue, Anomali recommends the following practices:

1. Specify Sourcetype with Parsed Fields: 

Use sourcetype as the primary constraint filter in the search query, while using parsed fields. See the Anomali Security Analytics Data Onboarding Guide for the complete list of supported log sources.

Inefficient Query	Recommended Query
| where message contains_ci '23b6c45a48c45a4c3b56a2c09a'	| where sourcetype = 'aws_cloudtrail' and file_hash = '23b6c45a48c45a4c3b56a2c09a'

2. Avoid Wildcard Matching on Sourcetype

Avoid performing any wildcard matching such as sourcetype contains '%' in the search query.

Inefficient Query	Recommended Query
| where sourcetype contains '%'	| where sourcetype = 'defender'

3. Avoid message on large search ranges

Avoid using the message field on large search time ranges. Anomali recommends that you restrict a search query with the message field to only 24 hours.

Inefficient Query	Recommended Query
| where message contains_ci 'RunInstances'	| where sourcetype = 'okta' | where message contains_ci 'RunInstances'

4. Turbosearch on Indexed Fields

Use the turbosearch operator on the following indexed fields:

• account_id

• dest

• dest_ip

• dvc_name

• file_hash

• file_hash2

• file_hash3

• file_name

• host

• http_referrer

• message_id

• query

• src

• src_ip

• url

• user

Inefficient Query	Recommended Query
| where sourcetype = 'aws_cloudtrail' | where message contains '23b6c45a48c45a4c3b56a2c09a' | where event_name = 'RunInstances'	| turbosearch '23b6c45a48c45a4c3b56a2c09a' | where sourcetype = 'aws_cloudtrail' and event_name = 'RunInstances'

If you require additional fields from the eventlog schema, Anomali recommends that you contact Anomali Support for assistance in enabling those for your needs.

Searching across different time ranges

Anomali lets you issue searches across multiple time ranges. Anomali recommends the following solutions for launching efficient searches across different time ranges:

I want to search for a time range of	Anomali's Recommendation	Additional Information
Less than 7 days	Use a regular search, considering all the AQL Best Practices. For example: | where sourcetype = 'aws_cloudtrail' and file_hash = '23b6c45a48c45a4c3b56a2c09a'	See defining your AQL search query for more information on how to create an AQL query expressions and define a search time range.
7 days to 90 days	Use the turbosearch operator to analyze event data across large time spans, up to 90 days. For example: | turbosearch '23b6c45a48c45a4c3b56a2c09a' | where sourcetype = 'aws_cloudtrail'	See turbosearch.
Greater than 90 days	Your data is archived beyond 90 days. Refer to the Anomali Search Data Retention Policy to understand the next steps to search this data.	See Anomali Virtual Compute to understand its usage and the different user actions that lead to AVC usage.

Use rex Operator and json_extract Efficiently

To reduce the number of records the rex operator and json_extract process, Anomali recommends using the rex operator and the calc operator's json_extract function after all conditional clauses used to filter the data.

Before	After
|where sourcetype = 'cisco' |rex field=message '.*?msg=(?<agent_msg>.*?)rawEvent=(?<rawEvent>.*?)$' |where action = 'create' |fields action, event_time, category, sourcetype	|where sourcetype = 'cisco' AND action = 'create' |rex field=message '.*?msg=(?<agent_msg>.*?)rawEvent=(?<rawEvent>.*?)$' |fields action, event_time, agent_msg, rawEvent

Use array_agg Operator Effectively

The array_agg operator enforces a limit of 50 distinct values. If the data being aggregated exceeds this limit, the results are truncated. Anomali recommends the following solutions to effectively use array_agg and avoid hitting resource limits:

• Use low cardinality fields.

• Filter or transform the data before applying the array_agg operator.

• Use the group by clause for high cardinality fields.

Use the dedup Operator Effectively

• Use the dedup operator over small data sets. Reduce the data set size by limiting time range or filtering out events.

• Do not apply the dedup operator to the message field. Doing so impacts search performance because each raw event is held in memory for this operation.

Stagger Scheduled Tasks

Stagger any task that you can schedule to avoid overloading the system with too many scheduled jobs. For example, instead of scheduling lots of alerts at 2:00 AM, consider staggering them between 2:00 AM and 3:00 AM.

Anomali recommends you consider staggering the following tasks:

• Scheduled Alerts

• Scheduled Reports

Use Definitive Operators

If possible, use definitive operators such as = and != instead of operators such as contains. For more accurate results, check the values in your tables before using them in the query.

Before	After
|where sourcetype contains_ci "defender"	|where sourcetype = "ms_defender"

Place Limits Last

Place the limit operator last in the query to avoid getting non-deterministic results. In the Before example, the limit is placed before the second where clause, which means that the where clause will be applied only to 100000 events.

If you place the limit operator last, all matching events are calculated first and then limited.

Before	After
|where sourcetype contains_ci "okta" |calc dtHash= json_extract(message, 'debugContext.debugData.dtHash') |aggr count(message) by dtHash |limit 100000 |where dthash="<hash>"	|where sourcetype contains_ci "okta" |calc dtHash= json_extract(message, 'debugContext.debugData.dtHash') |aggr count(message) by dtHash |where dthash="<hash>" |limit 100000

Use Smallest Possible Time Range

Limit your query to the shortest time frame necessary. The larger the time range, the more data that needs to be scanned, which increases the query execution time.

Specify Targeted Search Table

Specify a system lookup table, such as iocmatch, or a custom lookup table to reduce the data volume scanned. By default, queries scan the eventlog table, which contains all organizational data.

Example
iocmatch | where action in ('allow', 'accept')

Prioritize Predicates

Prioritize predicates (where statements) in terms of order: place them earlier but only if it does not affect the accuracy of the query.

In the Before example, the calc line uses the bytes_in and bytes_out fields, which are unrelated to the where clause, which uses sourcetype. You can place the where clause before, and now calculations will be skipped for fields with the source type other than aws_cloudtrail.

Before	After
|calc total_bytes = bytes_in + bytes_out |where sourcetype = "aws_cloudtrail"	|where sourcetype = "aws_cloudtrail" |calc total_bytes = bytes_in + bytes_out

Use Normalized Fields

When extracting values from JSON, check if the field to extract is a normalized column in the eventlog table schema. If yes, use the normalized column instead.

In the After example, the username field in the JSON string message field has been normalized as user in the table schema.

Before	After
|calc username = json_extract(message, "username") |fields username	|fields user