How to query multiple fileds in Kibana

Elastic Stack Elasticsearch
1

Hi,
I am trying to query kibana for multiple fields where the servicename is serviceworker and the correlationId is one of many. My understanding is using terms is the best option here but I cannot get this query to work regardless of where I place the condition.

GET unity/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "@timestamp": {
              "gte": "2020-04-01",
              "lte": "2022-05-01"
            }
          }
        }
      ], 
      "should": [
        {
          "match": {
            "serviceName": "Serviceworker"
          }
        }
      ],
      "must": [
        {
          "terms": {
            "correlationId": [
              "085178d5-e782-4ad9-a56a-5c08d906686d",
              "01b4ac00-1c67-4056-a571-455f9fd1c296"
            ]
            
          }
          
        }
        
        
      ]
    }
    
  }
  
}

What am I missing here?
Thank you

2

Hi,
Could explain more about "I cannot get this query to work"?
What i the result (or error) and what is your expected result?

In addition, the terms query is query for multiple "terms" and what do you mean by "query multiple fields"?

3

If there are no results returned, I suggest to narrow the problem by removing parts of the query, does it work with with the @timestamp range filter? adding the should match for the serviceName ...

4

Hi Tom,
What I meant is I am not getting any results but if I search these records in KIbana using
correlationId :"085178d5-e782-4ad9-a56a-5c08d906686d" AND serviceName: "Serviceworker" I get the expected document. By multiple fields I meant I wanted to get all the documents with serviceName: "Serviceworker" and correlationId is one of the of the items in specified in the terms. Like an join in SQL.
Running this query in the devTools, returns

{
  "took" : 2613,
  "timed_out" : false,
  "_shards" : {
    "total" : 110,
    "successful" : 110,
    "skipped" : 105,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}
5

Hi Matw,
I tried removing the date range completely and I still didn't get any results. I even reduced it to one item in the terms that I know exists but even that returned nothing

6

It's confusing. I suppose the condition you explained is "WHERE *** AND ***" and not JOIN.

Anyway, your query looks ok to query correlationId :"085178d5-e782-4ad9-a56a-5c08d906686d" AND serviceName: "Serviceworker". Something is strange. Have you checked each single query in should and must query:

{
          "match": {
            "serviceName": "Serviceworker"
          }
        }

and

{
          "terms": {
            "correlationId": [
              "085178d5-e782-4ad9-a56a-5c08d906686d",
              "01b4ac00-1c67-4056-a571-455f9fd1c296"
            ]
            
          }
          
        }

work as you intended?

7

I am sorry, I don't understand what you mean by Have you checked each single query in should and must query. I have confirmed these documents exists. If I switch the query like

GET unity/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "@timestamp": {
              "gte": "2020-04-01",
              "lte": "2022-07-01"
            }
          }
        }
      ], 
      "must": [
        {
          "match": {
            "serviceName": "Serviceworker"
          }
        }
      ],
      "should": [
        {
          "terms": {
            "correlationId": [
              "085178d5-e782-4ad9-a56a-5c08d906686d",
              "01b4ac00-1c67-4056-a571-455f9fd1c296"
            ]
            
          }
          
        }
        
        
      ]
    }
    
  }
  
}

This switch causes the query to return documents with servicename = serviceworker but the correlationIds are not correct

8

How about

GET unity/_search
{
  "query": {
      "match": {
        "serviceName": "Serviceworker"
    }  
  }
}

and

GET unity/_search
{
"query":{
          "terms": {
            "correlationId": [
              "085178d5-e782-4ad9-a56a-5c08d906686d",
              "01b4ac00-1c67-4056-a571-455f9fd1c296"
            ]
            
          }
          
        }
}

?

Then check both

GET unity/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "@timestamp": {
              "gte": "2020-04-01",
              "lte": "2022-07-01"
            }
          }
        }
      ], 
      "must": [
        {
          "match": {
            "serviceName": "Serviceworker"
          }
        }
      ]
    }
    
  }
  
}

and

GET unity/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "@timestamp": {
              "gte": "2020-04-01",
              "lte": "2022-07-01"
            }
          }
        }
      ], 
      "should": [
        {
          "terms": {
            "correlationId": [
              "085178d5-e782-4ad9-a56a-5c08d906686d",
              "01b4ac00-1c67-4056-a571-455f9fd1c296"
            ]
            
          }
          
        }
        
        
      ]
    }
    
  }
  
}

work well.

9

The first query worked well with my test.

PUT test_multiple_fields_query
{
  "mappings": {
    "properties": {
      "correlationId":{
      "type":"keyword"
    },
    
    "serviceName":{
      "type":"keyword"
    }
    }
    
  }
}

POST test_multiple_fields_query/_doc
{
  "@timestamp": "2021-01-01",
  "serviceName": "Serviceworker",
  "correlationId": 
              "085178d5-e782-4ad9-a56a-5c08d906686d"
}

GET test_multiple_fields_query/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "@timestamp": {
              "gte": "2020-04-01",
              "lte": "2022-07-01"
            }
          }
        }
      ], 
      "must": [
        {
          "match": {
            "serviceName": "Serviceworker"
          }
        }
      ],
      "should": [
        {
          "terms": {
            "correlationId": [
              "085178d5-e782-4ad9-a56a-5c08d906686d",
              "01b4ac00-1c67-4056-a571-455f9fd1c296"
            ]
            
          }
          
        }
        
        
      ]
    }
    
  }
  
}
10

This works and return the correct results based on the specified search term "serviceName": "Serviceworker". I can see the results are correct. However, this query

GET unity/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "@timestamp": {
              "gte": "2020-04-01",
              "lte": "2022-07-01"
            }
          }
        }
      ], 
      "should": [
        {
          "terms": {
            "correlationId": [
              "085178d5-e782-4ad9-a56a-5c08d906686d",
              "01b4ac00-1c67-4056-a571-455f9fd1c296"
            ]
            
          }
          
        }
        
        
      ]
    }
    
  }
  
}

Returns invalid results. in fact, it doesn't return any document with the specified correlationId specified in the terms. I get records but none of them matches the Ids specified.

Thank you for your help

11

Could you share samples which I can copy and paste to Dev tools (just like my last post) and reproduce the "invalid" results?

12

Here is one of the returned documents. Note that the correlationid 159ad2fc-0144-4d7c-a3d2-1539666cd7cf is not in the terms array.

{
        "_index" : "unity-2022.05.28-000044",
        "_type" : "_doc",
        "_id" : "8rUnCIEBz8iVD5dbH4b3",
        "_score" : 5.886705,
        "_ignored" : [
          "message.keyword"
        ],
        "_source" : {
          "@timestamp" : "2022-05-28T00:53:08.353Z",
          "ecs" : {
            "version" : "1.12.0"
          },
          "serviceComponent" : null,
          "serverIp" : "192.168.0.1",
          "requestId" : null,
          "result" : "Checkin successful.",
          "serviceVersion" : "2.0.0",
          "category" : "SERVICE",
          "details" : "Service instance registered: default.serviceworker.prod.2.0.0.7957389e4b0f08ecd7d4cdb2f9bde808",
          "serverPort" : {REMOVED},
          "serviceProcessId" : 6468,
          "serviceRealm" : null,
          "resultReason" : null,
          "serviceEnvironment" : "X",
          "severity" : "info",
          "class" : "INSTANCE.REGISTRATION",
          "action" : "put",
          "duration" : 0,
          "host" : {
            "name" : "PSERVER_ONE"
          },
          "identity" : "SYSTEM",
          "serviceName" : "Serviceworker",
          "schemaVersion" : 1,
          "correlationId" : "159ad2fc-0144-4d7c-a3d2-1539666cd7cf",
          "messageRole" : null,
          "requestRole" : null,
          "input" : {
            "type" : "log"
          },
          "target" : "{REMOVED}",
          "msource" : "registry.ts",
          "serverName" : "SERVER_ONE",
          "identityDelegate" : null,
          "dataClassification" : "CONFIDENTIAL",
          "timestamp" : "2022-05-28T00:53:07.193Z",
          "agent" : {
            "hostname" : "SERVER_ONE",
            "ephemeral_id" : "94258ab1-6508-406b-a434-4729e1abb9cb",
            "id" : "3d9b3d93-8f87-4156-8f8e-1edf2e31d3cc",
            "name" : "SERVER_ONE",
            "type" : "filebeat",
            "version" : "7.16.2"
          },
          "correlationRole" : "PARTICIPANT",
          "clientIp" : null,
          "log" : {
            "offset" : 6753369,
            "file" : {
              "path" : "LOG FILE PATH"
            }
          },
          "message" : """{"schemaVersion":1,"timestamp":"2022-05-28T00:53:07.193Z","severity":"info","details":"Service instance registered: default.serviceworker.prod.2.0.0.7957389e4b0f08ecd7d4cdb2f9bde808","clientIp":null,"serverName":"SERVER_ONE","serverIp":"192.168.0.1","serviceComponent":null,"serviceProcessId":6468,"serviceRealm":null,"identity":"SYSTEM","identityDelegate":null,"correlationId":"159ad2fc-0144-4d7c-a3d2-1539666cd7cf","correlationRole":"PARTICIPANT","requestId":null,"requestRole":null,"messageId":null,"messageRole":null,"category":"SERVICE","class":"INSTANCE.REGISTRATION","action":"put","target":"instance","result":"Checkin successful.","dataClassification":"CONFIDENTIAL","resultReason":null,"duration":0,"source":"registry.ts","serverPort":33018,"serviceName":"serviceworker","serviceVersion":"2.0.0","serviceEnvironment":"X"}""",
          "messageId" : null
        }
      }

The top part is

{
  "took" : 3804,
  "timed_out" : false,
  "_shards" : {
    "total" : 110,
    "successful" : 110,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : 5.886705,
    "hits" : [
      {
        "_index" : "unity-2022.05.28-000044",
        "_type" : "_doc",
        "_id" : "k7UjCIEBz8iVD5dbdWZL",
        "_score" : 5.886705,
        "_ignored" : [
          "message.keyword"
        ],
13

I meant samples which I can copy and paste to Dev tools to create index, index documents and query them.

14

Sorry, I found the reason.

If the bool query includes at least one should clause and no must or filter clauses, the default value is 1. Otherwise, the default value is 0.

the default minimum_should_parameter is 0 with must or filter clause.

set the terms query in must or filter clause or set minimum_should_parameter as 1.

15

I had tried that last night and just did that again

GET unity/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "@timestamp": {
              "gte": "2020-04-01",
              "lte": "2022-07-01"
            }
          }
        }
      ], 
      "must": [
        {
          "match": {
            "serviceName": "Serviceworker"
          }
        }
      ],
      "should": [
        {
          "terms": {
            "correlationId": [
              "085178d5-e782-4ad9-a56a-5c08d906686d",
              "01b4ac00-1c67-4056-a571-455f9fd1c296"
            ]
            
            
          }
          
        }
        
        
        
      ],
      "minimum_should_match": 1
    }
    
  }
  
}

returns

{
  "took" : 2233,
  "timed_out" : false,
  "_shards" : {
    "total" : 110,
    "successful" : 110,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}
16

It returned hits with my test index.

GET test_multiple_fields_query/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "@timestamp": {
              "gte": "2020-04-01",
              "lte": "2022-07-01"
            }
          }
        }
      ], 
      "must": [
        {
          "match": {
            "serviceName": "Serviceworker"
          }
        }
      ],
      "should": [
        {
          "terms": {
            "correlationId": [
              "085178d5-e782-4ad9-a56a-5c08d906686d",
              "01b4ac00-1c67-4056-a571-455f9fd1c296"
            ]
          }
        }
      ],
      "minimum_should_match": 1
    }
  }
}

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 1.287682,
    "hits" : [
      {
        "_index" : "test_multiple_fields_query",
        "_type" : "_doc",
        "_id" : "czS8Q4EBf0nakUP8WpBP",
        "_score" : 1.287682,
        "_source" : {
          "@timestamp" : "2021-01-01",
          "serviceName" : "Serviceworker",
          "correlationId" : "085178d5-e782-4ad9-a56a-5c08d906686d"
        }
      }
    ]
  }
}

Therefore the query itself should be ok.

Again, could you share samples which I can copy and paste to Dev tools (to create index, index documents and query them) and reproduce the "invalid" results?

17

The index template

PUT _index_template/unity
{
  "template": {
    "settings": {
      "index": {
        "lifecycle": {
          "name": "unity_index_ilm_policy",
          "rollover_alias": "unity"
        },
        "number_of_shards": "5",
        "number_of_replicas": "1"
      }
    },
    "mappings": {
      "properties": {
        "serviceComponent": {
          "fielddata": true,
          "type": "text"
        },
        "serverName": {
          "fielddata": true,
          "type": "text"
        },
        "source": {
          "fielddata": true,
          "type": "text"
        },
        "serverPort": {
          "type": "integer"
        },
        "serviceProcessId": {
          "type": "integer"
        },
        "duration": {
          "type": "integer"
        },
        "identityDelegate": {
          "fielddata": true,
          "type": "text"
        },
        "result": {
          "fielddata": true,
          "type": "text"
        },
        "serviceVersion": {
          "fielddata": true,
          "type": "text"
        },
        "dataClassification": {
          "fielddata": true,
          "type": "text"
        },
        "identity": {
          "fielddata": true,
          "type": "text"
        },
        "requestId": {
          "fielddata": true,
          "type": "text"
        },
        "resultReason": {
          "fielddata": true,
          "type": "text"
        },
        "action": {
          "fielddata": true,
          "type": "text"
        },
        "correlationId": {
          "fielddata": true,
          "type": "text"
        },
        "details": {
          "fielddata": true,
          "type": "text"
        },
        "class": {
          "fielddata": true,
          "type": "text"
        },
        "serviceRealm": {
          "fielddata": true,
          "type": "text"
        },
        "messageId": {
          "fielddata": true,
          "type": "text"
        },
        "messageRole": {
          "fielddata": true,
          "type": "text"
        },
        "requestRole": {
          "fielddata": true,
          "type": "text"
        },
        "correlationRole": {
          "fielddata": true,
          "type": "text"
        },
        "serviceName": {
          "fielddata": true,
          "type": "text"
        },
        "target": {
          "fielddata": true,
          "type": "text"
        },
        "@timestamp": {
          "type": "date"
        },
        "clientIp": {
          "ignore_malformed": true,
          "type": "ip"
        },
        "serviceEnvironment": {
          "fielddata": true,
          "type": "text"
        },
        "serverIp": {
          "ignore_malformed": true,
          "type": "ip"
        },
        "xForwardedFor": {
          "ignore_malformed": true,
          "type": "ip"
        },
        "category": {
          "fielddata": true,
          "type": "text"
        }
      }
    }
  },
  "index_patterns": [
    "unity-*"
  ],
  "composed_of": [
    "severity",
    "schemaversion"
  ]
}

Unfortunately, our Dev Elastic environment is down now and I cannot create test documents in Production

18

Found an environment to run some tests on

GET unity-2022.05.29-000015/_doc/0dFvRIEBvv5zIOG6LLXF

POST unity/_doc
{
"ServiceName": "SERVICE_ONE",
"serverName" : "SERVER_ONE",
"correlationId" : "bbc4be64-d240-46fb-97f7-b1be9c11a6be"
}
POST unity/_doc
{
"ServiceName": "SERVICE_ONE",
"serverName" : "SERVER_ONE",
"correlationId" : "bbc4be64-d240-46fb-97f7-b1be9c11a6bc"
}

GET unity/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "@timestamp": {
              "gte": "2020-04-01",
              "lte": "2022-07-01"
            }
          }
        }
      ], 
      "must": [
        {
          "match": {
            "serviceName": "SERVICE_ONE"
          }
        }
      ],
      "should": [
        {
          "terms": {
            "correlationId": [
              "bbc4be64-d240-46fb-97f7-b1be9c11a6be",
              "bbc4be64-d240-46fb-97f7-b1be9c11a6bc"
            ]
          }
        }
      ],
      "minimum_should_match": 1
    }
  }
}

returned

{
  "took" : 36,
  "timed_out" : false,
  "_shards" : {
    "total" : 4,
    "successful" : 4,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}
19

One problem is:

"correlationId": {
          "fielddata": true,
          "type": "text"
        }

You should use keyword type family mappings, if you want to use term-level queries.

Within text field, each text is analyzed to tokens to be indexed. When use term-level query on text type field, each term should match on each tokens.

See the top WARNING of this doc.

This is the example. You will realize what had happened.

PUT test_term_on_text
{
  "mappings": {
    "properties": {
      "text": {"type":"text"}
    }
  }
}

POST test_term_on_text/_doc
{
  "text": "foo bar"
}

GET test_term_on_text/_search
{
  "query":{
    "term":{
      "text": {
        "value": "foo"
      }
    }
  }
}

GET test_term_on_text/_search
{
  "query":{
    "term":{
      "text": {
        "value": "foo bar"
      }
    }
  }
}

serverName field should be keyword field. Using term query with keyword field should be better to guarantee exact match.

20

Thanks Tom.
One more question, I changed the field to a keyword and now when I preview the changes under index management, I see this

"serverName": {
      "eager_global_ordinals": false,
      "norms": false,
      "index": true,
      "store": false,
      "type": "keyword",
      "index_options": "docs",
      "split_queries_on_whitespace": false,
      "doc_values": true
    },

Do you see an issue with these settings?