Case management query optimization for large Elasticsearch environments

[dgmyr]

Version

3.2.0

Installation Method

Security Onion ISO image

Description

configuration

Installation Type

Distributed

Location

on-prem with Internet access

Hardware Specs

Exceeds minimum requirements

CPU

32

RAM

128

Storage for /

200gb

Storage for /nsm

6tb

Network Traffic Collection

other (please provide detail below)

Network Traffic Speeds

1Gbps to 10Gbps

Status

Yes, all services on all nodes are running OK

Salt Status

Yes, there are salt failures (please provide detail below)

Logs

Yes, there are additional clues in /opt/so/log/ (please provide detail below)

Detail

We have identified a performance issue related to Case management queries in Security Onion SOC.

When working with Cases, SOC generates queries containing index filters such as:

_index:"*:so-case"

and

_index:"*:so-casehistory"

The problem is that these filters are part of the query itself rather than being used to restrict the Elasticsearch search target. In environments with a large number of indices/shards, Elasticsearch therefore expands the search across a very large number of shards before determining that most of them cannot contain the requested Case data.

For example, in our environment a so-casehistory query was executed against 2,179 shards, with 0 shards skipped, and took approximately 2.1 seconds.

Running the equivalent query directly against:

*:so-casehistory

resulted in only 1 shard being queried and completed in approximately 102 ms.

Would it be possible to optimize Case-related searches so that so-case and so-casehistory are used as the Elasticsearch search target instead of filtering them through the _index field inside the query?

Alternatively, would it be possible in future Security Onion versions to add a configurable parameter in SOC Configuration → Cases (defaults.yaml case section) , for example queryBaseFilter, which would allow administrators to define or override the base query/search target used for Case-related searches?

This would provide a way to optimize Case queries for larger deployments without modifying Security Onion code and could significantly reduce Elasticsearch shard fan-out and overall cluster load.

This log was captured through Elasticsearch Search Slow Log monitoring and represents an actual Case-related query generated by Security Onion SOC. The slowlog shows that the query targeted 2,179 shards and took approximately 2 seconds to complete.

{"size":1000,"query":{"bool":{"must":[{"query_string":{"query":"_index: ":so-case" AND so_kind: "comment" AND so_comment.caseId: "65272df6-ccc3-52eb-8f72-4bbc8a4c1786"","default_field":"","fields":[],"fuzziness":"AUTO","analyze_wildcard":true}},{"range":{"@timestamp":{"boost":1.0}}}],"boost":1.0}},"sort":[{"so_comment.createTime":{"order":"asc","missing":"_last","unmapped_type":"date"}}],"track_total_hits":2147483647,"slowlog":{"took_millis":2014,"total_shards":2179}}

Guidelines

• I have read the discussion guidelines at Read before posting! #1720 and assert that I have followed the guidelines.

[cm-ops]

Are you asking for this? SOC > Administration > Configuration > soc > config > server > client > cases > queryBaseFilter [advanced toggle on] If so, that is currently configurable.

[dgmyr]

Hi, no. I’m referring to a different configuration section — case (singular), not the existing cases section.

The proposed path would be:

SOC > Administration > Configuration > soc > config > server > client > case > queryBaseFilter [advanced toggle on]

The idea is to have a separate queryBaseFilter for Case-related queries that currently use _index:"*:so-case" and _index:"*:so-casehistory", so that the Elasticsearch search target can be restricted and unnecessary shard fan-out can be avoided.