SQL Query convert to ES query

Elastic Stack Elasticsearch
1

Hi Guys,

(message == "Accpted" OR message == "Password)
OR
(Hostname == "SSDESK-003)
AND
(( src_ip != 10.100.7.102) AND (engine_log_id == "7"))

How to convert above query to ES Query?

Any quick help would be appreciated!

2

Check the ES SQL translate API.
Keep in mind you need to enclose your string literals in single quotes:

SELECT * FROM test WHERE (message = 'Accepted' OR message = 'Password') OR (Hostname = 'SSDESK-003') AND ((src_ip != '10.100.7.102') AND (engine_log_id = 7))
3

This solution may not help us as we don't have x-pack.

4 
{
    "size": 1000,
    "query": {
        "bool": {
            "should": [
                {
                    "bool": {
                        "should": [
                            {
                                "term": {
                                    "message.keyword": {
                                        "value": "Accepted",
                                        "boost": 1.0
                                    }
                                }
                            },
                            {
                                "term": {
                                    "message.keyword": {
                                        "value": "Password",
                                        "boost": 1.0
                                    }
                                }
                            }
                        ],
                        "boost": 1.0
                    }
                },
                {
                    "bool": {
                        "must": [
                            {
                                "term": {
                                    "Hostname.keyword": {
                                        "value": "SSDESK-003",
                                        "boost": 1.0
                                    }
                                }
                            },
                            {
                                "bool": {
                                    "must": [
                                        {
                                            "bool": {
                                                "must_not": [
                                                    {
                                                        "term": {
                                                            "src_ip": {
                                                                "value": "10.100.7.102",
                                                                "boost": 1.0
                                                            }
                                                        }
                                                    }
                                                ],
                                                "boost": 1.0
                                            }
                                        },
                                        {
                                            "term": {
                                                "engine_log_id": {
                                                    "value": 7,
                                                    "boost": 1.0
                                                }
                                            }
                                        }
                                    ],
                                    "boost": 1.0
                                }
                            }
                        ],
                        "boost": 1.0
                    }
                }
            ],
            "boost": 1.0
        }
    }
}
6

I don't get what do you mean by this image, but the query I've pasted before, uses the bool-should for OR and bool-must for AND and it's quite visible how to match values for a field.
Please refer to docs for term-level queries and compound queries for more info.

1 Like
7

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