AWS Elasticsearch 7.7 : Query index for one field to match a subset of chars in another text field in a document? Can it be done?

Elastic Stack Elasticsearch
1

Hi everyone,

I hope someone can guide me with some guides or links on this please .

I have an index with two fields of interest : adapterid and country and I am trying to write a query without going thorugh every permutation.

adapterid : is a text field and the string consists of a countrycode followed by a string of text ie "ukoffnetwork" or "ukonnetwork" or "aeoffnetwork" .. there is one for every single country which is about 100+
and in the same document there is a
country field : ie "uk" , "ae" , "de" "us" etc

Now I want to create a search that will return every document that adapterid does not match the country code .

    GET /testindex-f-audit/_search
    {
    "query": {
        "bool": {
          "must": [],
          "filter": [
            {
              "match_all": {}
            },
            {
              "match_phrase": {
                "adapterid": "AEOffNetwork"
              }
            }
          ],
          "should": [],
          "must_not": [
            {
              "match_phrase": {
                "country": "AE"
              }
            }
          ]
        }
      }
    }

but i dont want to write a query for every single country

Is there a way to write a query where i can return all documents where the first 2 characters (subset) of one field does not match the contents of another field in the same document?

I am using aws elasticsearch so i am on 7.7 and i have looked at the wildcard keyword but i think that is a 7.10 but hasn't anyone anything similar to this in the past .

Any help would be grately appreciated.

Cheers

yoyomonkey

2

Use a query based on a scripted field that compares the two fields you described.

3

Thank you Christian for the . I am testing out the query i have so far before i create a scripted field containing the value but how do i compare the first 2 characters of the adapater id with the country ?

Am i going in the right direction for creating the right field? (below)

{
  "bool": {
    "filter": {
      "script": {
        "script": "doc['adapterid'].value !=  doc['country'].value"
    }
  }
}

Many thanks again.

Cheers

yoyomonkey

4

Hi Christian,

Thank you for the advice. I have tried the following but it seems the search is just returning everything . I have tried == and != to see if the results are different .

I need to create a field that will either return true or false is the first 2 letters of adapterid matches the country field.

so in the case below IT of ITOffNetwork matches the country field IT.

A typical document is

     {
        "_index" : "storm-test-pk-audit",
        "_type" : "_doc",
        "_id" : "49612776696518265133771728348729303449084247219025657986.0",
        "_score" : 1.0,
        "_source" : {
          "timestamp" : "2020-11-19 14:05:14,876",
          "trackingid" : "tid:Pitmro_EwrF0",
          "event" : "AUTHN_ATTEMPT",
          "subject" : "",
          "app" : "",
          "connectionid" : "https://test.mintos.mooncow.com",
          "protocol" : "SAML20",
          "host" : "storm-test-lubo-sso-1b",
          "role" : "IdP",
          "status" : "inprogress",
          "adapterid" : "ITOffNetwork",
          "description" : "",
          "responsetime" : "34",
          "useragent" : "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 10.0; WOW64; Trident/7.0; .NET4.0C; .NET4.0E; .NET CLR 2.0.50727; .NET CLR 3.0.30729; .NET CLR 3.5.30729)",
          "city" : "Milan",
          "country" : "IT",
          "browser" : "IE",
          "os" : "Windows",
          "location" : {
            "lat" : 45.443,
            "lon" : 9.195
          }
        }
      }
    ]
  }
}

I am worried that if i create a scripted field it could break my kibana dashboards and index so I just wanted to see if i could first of all return documents that either matched or did not match but both just returns everything.

GET storm-test-pk-audit/_search

{
  "query":{
    "bool":{
      "must":{
        "script": {
          "lang": "painless",
          "inline": "def result; result = doc['adapterid'].value.substring(2) !=  doc['country'].value; return result;"
        }
      }
    }
  }
}

Can anyone point me to any links or could tell me where i am going wrong? This is the first query like this i am writing so any help would be really appreciated.

5

Not directly related to your question but did you look at Cloud by Elastic, also available if needed from AWS Marketplace ?

Cloud by elastic is one way to have access to all features, all managed by us. Think about what is there yet like Security, Monitoring, Reporting, SQL, Canvas, Maps UI, Alerting and built-in solutions named Observability, Security, Enterprise Search and what is coming next :slight_smile: ...

And the service is always up to date with the latest Elastic versions.

6

Thanks David. I have had a look at Elastic by cloud and to be honest as I am still learning and developing this technology internally I am happy with AWS elasticsearch currently.

I have learned so much from a few months back and thought i would come on to the forums for advise and guidance.

Cheers

yoyomonkey

7

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