KQL query for fields with a value which is not `-`

[mikewillis]

Kibana 7.17.

I've got some indices where documents contain a field called username . Sometimes the value is a username, like bob or alice and often the value is -. What's a KQL query that will return documents where the value of username is not - ?

not username:"-" doesn't work and nor does not username:"\-"

(I'm not looking at that thing where Kibana sometimes shows - for a field value because the field doesn't exist in a document and thinking the value is -, I have checked the JSON view of documents, the field exists, it's value is -.)

[Marius_Dragomir]

The easiest way is to find a document in Discover where you have the value of - and then click on the magnifying glass with the minus sign it. That will filter out documents with that value.
As for what I thing was wrong in that query it might be because you have to use NOT as KQL is case sensitive.

[mikewillis]

@Marius_Dragomir

The easiest way is to find a document in Discover where you have the value of - and then click on the magnifying glass with the minus sign it. That will filter out documents with that value.

The icon doesn't look like a magnifying glass but I know the one you mean. And it is easy. And it causes Kibana to show that it has added the desired filter

But it does not do anything. The number of hits Kibana reports does not change with the filter applied. Nothing is actually filtered out.

@Marius_Dragomir

As for what I thing was wrong in that query it might be because you have
to use NOT as KQL is case sensitive.

Kibana Query Language | Kibana Guide [7.17] | Elastic

says

KQL supports or, and, and not. By default, and has a higher precedencethan or. To override the default precedence, group operators in parentheses. These operators can be upper or lower case.

I've tried "not" and "NOT" (with queries that do not involve a value of -) and got the same results.

[stephenb (Stephen Brown)]

@mikewillis Quick Simple Check ...

Are you talking about when you see a - in Discovery like this?

If so that is not a literal - it means the field does not exist for that document ... You can check the json itself

If that is the case then use the field does not exist syntax

So in my case to filter for those docs

not kubernetes.labels.app :*

in your case

not username :*

[mikewillis]

@stephenb I refer you to the last paragraph of my original post. I am aware that in scenarios such as the one you show in your screenshots Kibana will show - for a field value to indicate that the field does not exist in that document. I am working with indices in which some documents contain a field with a value of -. Here is a part of the JSON I get if I request such a document with curl via the Get API.

    "user-agent-detail" : {
      "device" : "Other",
      "name" : "IE",
      "os" : "Windows",
      "os_name" : "Windows",
      "major" : "11",
      "minor" : "0",
      "build" : ""
    },
    "username" : "-",
    "requested_uri" : "/idp/Authn/RemoteUser",
    "geoip" : {

[stephenb (Stephen Brown)]

Hi @mikewillis

Apologies... On track now but Hmmm I am not seeing what you are ... perhaps I am still off track

OK 7.17.6

with literal "-" both of these work for me.

What is your mappings... any chance that username is a text... I am thinking it may be...

Can you show the mapping for that field

Both of these work for me

not username: -
and
not username: "-"

PUT discuss-test
{
  "mappings": {
    "properties": {
      "username" : {"type": "keyword"},
      "org" : {"type": "keyword"}
    }
  }
}


POST discuss-test/_doc
{
    "@timestamp": "2022-11-07T16:00:00.000Z",
    "username" : "myname",
    "org" : "myorg"
}

POST discuss-test/_doc
{
    "@timestamp": "2022-11-07T16:01:00.000Z",
    "username" : "-",
    "org" : "noorg"
}

POST discuss-test/_doc
{
    "@timestamp": "2022-11-07T16:02:00.000Z",
    "username" : "othername",
    "org" : "otherorg"
}

Discover

[stephenb (Stephen Brown)]

@mikewillis

just tested with username as type text and I see exactly the results you are...

That is because with text "-" is a tokenizer and get tokenized on store and search.... so I think that is not going to work

So options if this is default mapping could you try (default creates both text and keyword fields)

not username.keyword : "-"

Or fix the mapping to be keyword

Show us the mapping for that fields ... username best practice is type keyword

If this is a beat... did you run setup before you started which would have created the proper mappings or that is a net new field added along the way.

[mikewillis]

username.keyword is the answer, thanks! (An answer with an irritating feeling of déjà vu. )

No beats are involved, various systems are sending JSON to a Logstash tcp input and we have an index template that was loaded by our configuration management. username is indeed type text

    "mappings" : {
      "username" : {
        "full_name" : "username",
        "mapping" : {
          "username" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword"
              },
              "raw" : {
                "type" : "keyword"
              }
            },
            "analyzer" : "usercodes_pattern"
          }
        }
      }
    }

username.raw exists for backwards compatibility with something someone did N years ago. The (poorly named!) usercodes_pattern is

          "usercodes_pattern": {
            "type": "pattern",
            "pattern": "\\W"
          },

which we have because sometimes usernames look like domain\bob and we want those to be returned by a search for username:bob (hence username can't be keyword).

So
not username.keyword:"-"
removes documents where the value is - and
not username.keyword:"-" and not username.keyword:""
also removes documents where the value is (for what I assume are really good reasons…) a zero length string like this

    "username" : "",
    "elapsed_time" : 27,

which Kibana shows as (empty).