Elasticsearch DSL query, exists and not exists fields

Hello,

I am trying to achieve the following with DSL querying ( not_exists: "data.aws.userIdentity.sessionContext.attributes.mfaAuthenticated" AND data.aws.eventName: ConsoleLogin AND data.aws.additionalEventData.MFAUsed: No) OR (exists: "data.aws.userIdentity.sessionContext.attributes.mfaAuthenticated" AND data.aws.eventName: ConsoleLogin AND data.aws.additionalEventData.MFAUsed: No AND data.aws.userIdentity.sessionContext.attributes.mfaAuthenticated: false)

This is what i've got but i dont think the second part of the query works.. Can someone help?

{
    "size": 0,
    "query": {
        "bool": {
            "must": [
                {
                    "bool": {
                        "must": [
                            {
                                "exists": {
                                    "field": "data.aws.userIdentity.sessionContext.attributes.mfaAuthenticated",
                                    "boost": 1
                                }
                            },
                            {
                                "match": {
                                    "data.aws.eventName": {
                                        "query": "ConsoleLogin",
                                        "operator": "OR",
                                        "prefix_length": 0,
                                        "max_expansions": 50,
                                        "fuzzy_transpositions": true,
                                        "lenient": false,
                                        "zero_terms_query": "NONE",
                                        "auto_generate_synonyms_phrase_query": true,
                                        "boost": 1
                                    }
                                }
                            },
                            {
                                "match": {
                                    "data.aws.additionalEventData.MFAUsed": {
                                        "query": "No",
                                        "operator": "OR",
                                        "prefix_length": 0,
                                        "max_expansions": 50,
                                        "fuzzy_transpositions": true,
                                        "lenient": false,
                                        "zero_terms_query": "NONE",
                                        "auto_generate_synonyms_phrase_query": true,
                                        "boost": 1
                                    }
                                }
                            },
                            {
                                "match": {
                                    "data.aws.userIdentity.sessionContext.attributes.mfaAuthenticated": {
                                        "query": "false",
                                        "operator": "OR",
                                        "prefix_length": 0,
                                        "max_expansions": 50,
                                        "fuzzy_transpositions": true,
                                        "lenient": false,
                                        "zero_terms_query": "NONE",
                                        "auto_generate_synonyms_phrase_query": true,
                                        "boost": 1
                                    }
                                }
                            }
                        ],
                        "adjust_pure_negative": true,
                        "boost": 1
                    }
                },
                {
                    "bool": {
                        "should": [
                            {
                                "bool": {
                                    "must": [
                                        {
                                            "exists": {
                                                "field": "data.aws.userIdentity.sessionContext.attributes.mfaAuthenticated",
                                                "boost": 1
                                            }
                                        },
                                        {
                                            "match": {
                                                "data.aws.eventName": {
                                                    "query": "ConsoleLogin",
                                                    "operator": "OR",
                                                    "prefix_length": 0,
                                                    "max_expansions": 50,
                                                    "fuzzy_transpositions": true,
                                                    "lenient": false,
                                                    "zero_terms_query": "NONE",
                                                    "auto_generate_synonyms_phrase_query": true,
                                                    "boost": 1
                                                }
                                            }
                                        },
                                        {
                                            "match": {
                                                "data.aws.additionalEventData.MFAUsed": {
                                                    "query": "No",
                                                    "operator": "OR",
                                                    "prefix_length": 0,
                                                    "max_expansions": 50,
                                                    "fuzzy_transpositions": true,
                                                    "lenient": false,
                                                    "zero_terms_query": "NONE",
                                                    "auto_generate_synonyms_phrase_query": true,
                                                    "boost": 1
                                                }
                                            }
                                        },
                                        {
                                            "match": {
                                                "data.aws.userIdentity.sessionContext.attributes.mfaAuthenticated": {
                                                    "query": "true",
                                                    "operator": "OR",
                                                    "prefix_length": 0,
                                                    "max_expansions": 50,
                                                    "fuzzy_transpositions": true,
                                                    "lenient": false,
                                                    "zero_terms_query": "NONE",
                                                    "auto_generate_synonyms_phrase_query": true,
                                                    "boost": 1
                                                }
                                            }
                                        }
                                    ],
                                    "adjust_pure_negative": true,
                                    "boost": 1
                                }
                            }
                        ],
                        "adjust_pure_negative": true,
                        "boost": 1
                    }
                }
            ],
            "filter": [
                {
                    "range": {
                        "@timestamp": {
                            "from": "{{period_end}}||-800000m",
                            "to": "{{period_end}}",
                            "include_lower": true,
                            "include_upper": true,
                            "format": "epoch_millis",
                            "boost": 1
                        }
                    }
                }
            ],
            "adjust_pure_negative": true,
            "boost": 1
        }
    },
    "aggregations": {}
}

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.