opauloh
commented
2 months ago While researching options to achieve a trendline query that accounts for dynamic data, I identified date_histogram aggregations combined with scripted metrics can be used to achieve a trendline query that accounts for dynamic data, the following script can be executed in the Kibana dev tools:
POST logs-cloud_security_posture.scores-default/_search
{
"query": {
"bool": {
"filter": [
{ "term": { "policy_template": "cspm" } },
{ "term": { "is_enabled_rules_score": true } }
],
"must": [
{
"range": {
"@timestamp": {
"gte": "now-1d",
"lte": "now"
}
}
}
]
}
},
"aggs": {
"trends": {
"date_histogram": {
"field": "@timestamp",
"fixed_interval": "5m"
},
"aggs": {
"sum_total_findings": {
"sum": {
"field": "total_findings"
}
},
"sum_passed_findings": {
"sum": {
"field": "passed_findings"
}
},
"sum_failed_findings": {
"sum": {
"field": "failed_findings"
}
},
"sum_score_by_cluster": {
"scripted_metric": {
"init_script": "state.clusters = [:]",
"map_script": """
if (params._source.containsKey('score_by_cluster_id')) {
for (entry in params._source.score_by_cluster_id.entrySet()) {
def cluster = state.clusters[entry.getKey()];
if (cluster == null) {
cluster = ['total_findings': 0, 'passed_findings': 0, 'failed_findings': 0];
state.clusters[entry.getKey()] = cluster;
}
if (entry.getValue().total_findings != null) {
cluster.total_findings += entry.getValue().total_findings;
}
if (entry.getValue().passed_findings != null) {
cluster.passed_findings += entry.getValue().passed_findings;
}
if (entry.getValue().failed_findings != null) {
cluster.failed_findings += entry.getValue().failed_findings;
}
}
}
""",
"combine_script": "return state.clusters",
"reduce_script": """
def clusters = [:];
for (state in states) {
if (state != null) {
for (entry in state.entrySet()) {
def cluster = clusters[entry.getKey()];
if (cluster == null) {
cluster = ['total_findings': 0, 'passed_findings': 0, 'failed_findings': 0];
clusters[entry.getKey()] = cluster;
}
cluster.total_findings += entry.getValue().total_findings;
cluster.passed_findings += entry.getValue().passed_findings;
cluster.failed_findings += entry.getValue().failed_findings;
}
}
}
def sum_clusters = [];
for (entry in clusters.entrySet()) {
def cluster = entry.getValue();
sum_clusters.add([
'cluster_id': entry.getKey(),
'sum_total_findings': cluster.total_findings,
'sum_passed_findings': cluster.passed_findings,
'sum_failed_findings': cluster.failed_findings
]);
}
return sum_clusters;
"""
}
},
"sum_score_by_benchmark": {
"scripted_metric": {
"init_script": "state.benchmarks = [:]",
"map_script": """
if (params._source.containsKey('score_by_benchmark_id')) {
for (entry in params._source.score_by_benchmark_id.entrySet()) {
for (subEntry in entry.getValue().entrySet()) {
def benchmark = state.benchmarks[entry.getKey() + '_' + subEntry.getKey()];
if (benchmark == null) {
benchmark = ['total_findings': 0, 'passed_findings': 0, 'failed_findings': 0];
state.benchmarks[entry.getKey() + '_' + subEntry.getKey()] = benchmark;
}
if (subEntry.getValue().total_findings != null) {
benchmark.total_findings += subEntry.getValue().total_findings;
}
if (subEntry.getValue().passed_findings != null) {
benchmark.passed_findings += subEntry.getValue().passed_findings;
}
if (subEntry.getValue().failed_findings != null) {
benchmark.failed_findings += subEntry.getValue().failed_findings;
}
}
}
}
""",
"combine_script": "return state.benchmarks",
"reduce_script": """
def benchmarks = [:];
for (state in states) {
if (state != null) {
for (entry in state.entrySet()) {
def benchmark = benchmarks[entry.getKey()];
if (benchmark == null) {
benchmark = ['total_findings': 0, 'passed_findings': 0, 'failed_findings': 0];
benchmarks[entry.getKey()] = benchmark;
}
benchmark.total_findings += entry.getValue().total_findings;
benchmark.passed_findings += entry.getValue().passed_findings;
benchmark.failed_findings += entry.getValue().failed_findings;
}
}
}
def sum_benchmarks = [];
for (entry in benchmarks.entrySet()) {
def benchmark = entry.getValue();
sum_benchmarks.add([
'benchmark_id': entry.getKey(),
'sum_total_findings': benchmark.total_findings,
'sum_passed_findings': benchmark.passed_findings,
'sum_failed_findings': benchmark.failed_findings
]);
}
return sum_benchmarks;
"""
}
}
}
}
},
"size": 0 // No need to return individual documents
}date_histogram
The date_histogram aggregation is used to group documents into buckets based on their timestamp. Each bucket represents a specific time interval (e.g., minute, hour, day), allowing for the analysis of trends over time.
Functionality:
Bucketing by Time: It divides the documents into buckets based on a specified time interval (fixed_interval), enabling time-based aggregation of the data.
date_histogram was used to perform Aggregation Over Time, to group documents into fixed time intervals, allowing for the analysis of trends over these periods.
scripted_metric
The scripted_metric aggregation allows for complex custom calculations on documents that can't be achieved with standard aggregations. It's particularly useful for handling nested fields and performing multiple operations in a single pass.
- Initialization (init_script): Sets up the initial state for the aggregation, such as creating an empty map to store interim results.
- Mapping (map_script): Processes each document, updating the state with values from the document. This is where individual document values are added to the state.
- Combining (combine_script): Combines the results from the mapping phase into a single intermediate state. This is useful when multiple shards are involved.
- Reduction (reduce_script): Combines the intermediate states from all shards to produce the final result. This phase typically performs the final calculations and returns the aggregated results.
scripted_metrics was used here to Handle Nested Fields. As we have dynamic nested fields (score_by_cluster_id and score_by_benchmark_id), which can't be directly aggregated using standard Elasticsearch aggregations. Also it allows for the implementation of custom logic to sum values across multiple nested documents, providing flexibility to handle complex aggregation requirements.
The idea is that a datastream.namespace filter can then be passed to this filter to account for either user roles or space awareness and the score will still be generated considering what the current user can see
References: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-scripted-metric-aggregation.html
maxcold
JordanSh
Motivation
Once the
datastream.namespacefield is added to thelogs-cloud_security_posture.scores-defaultindex the trend line query will need to be updated to reflect the ability to query across multiple namespaces.As per the information below, the query can be achieved through the use of
date_histogramaggregations, we don't need to use scripted_metrics aggregations, and instead, we can perform a query to the Findings Misconfigurations DataView to get the benchmarks with the logged user permission.Requirements
https://github.com/elastic/kibana/issues/184750 Should be completed
Definition of done