# KQL query for fields with a value which is not \`-\` **URL:** \ **Category:** Kibana\ **Created:** [November 3, 2022, 10:50am UTC](https://discuss.elastic.co/t/kql-query-for-fields-with-a-value-which-is-not/318098 "2022-11-03T10:50:35Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![mikewillis](https://avatars.discourse-cdn.com/v4/letter/m/b2d939/32.png) [@mikewillis](https://discuss.elastic.co/u/mikewillis)\ **Post date:** [November 3, 2022, 10:50am UTC](https://discuss.elastic.co/t/kql-query-for-fields-with-a-value-which-is-not/318098/1 "2022-11-03T10:50:35Z")
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 `-`.) ---
**Author:** ![Marius\_Dragomir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marius_dragomir/32/42087_2.png) [@Marius\_Dragomir](https://discuss.elastic.co/u/Marius_Dragomir)\ **Post date:** [November 3, 2022, 3:33pm UTC](https://discuss.elastic.co/t/kql-query-for-fields-with-a-value-which-is-not/318098/2 "2022-11-03T15:33:39Z")
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. ---
**Author:** ![mikewillis](https://avatars.discourse-cdn.com/v4/letter/m/b2d939/32.png) [@mikewillis](https://discuss.elastic.co/u/mikewillis)\ **Post date:** [November 4, 2022, 9:29am UTC](https://discuss.elastic.co/t/kql-query-for-fields-with-a-value-which-is-not/318098/3 "2022-11-04T09:29:50Z")
> [@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 ![notusernamefilter](https://us1.discourse-cdn.com/elastic/original/3X/d/6/d69f9ab03debee0d6a92793c41bdfbf13b025df5.png) 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](https://www.elastic.co/guide/en/kibana/7.17/kuery-query.html#_boolean_queries)** 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. ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [November 6, 2022, 5:36am UTC](https://discuss.elastic.co/t/kql-query-for-fields-with-a-value-which-is-not/318098/4 "2022-11-06T05:36:35Z")
@mikewillis Quick Simple Check ... Are you talking about when you see a `-` in Discovery like this? ![Screen Shot 2022-11-05 at 10.29.59 PM](https://us1.discourse-cdn.com/elastic/original/3X/b/4/b4ee0da1b849759b1fb1287736dfd1de49bf4792.png) If so that is not a literal `-` it means the field does not exist for that document ... You can check the json itself ![Screen Shot 2022-11-05 at 10.34.34 PM](https://us1.discourse-cdn.com/elastic/original/3X/f/7/f797e815438bcf71fbd7c1f5a17a29ebd37f35ba.jpeg) 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 :*` ---
**Author:** ![mikewillis](https://avatars.discourse-cdn.com/v4/letter/m/b2d939/32.png) [@mikewillis](https://discuss.elastic.co/u/mikewillis)\ **Post date:** [November 7, 2022, 10:46am UTC](https://discuss.elastic.co/t/kql-query-for-fields-with-a-value-which-is-not/318098/5 "2022-11-07T10:46:22Z")
@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. ```auto "user-agent-detail" : { "device" : "Other", "name" : "IE", "os" : "Windows", "os_name" : "Windows", "major" : "11", "minor" : "0", "build" : "" }, "username" : "-", "requested_uri" : "/idp/Authn/RemoteUser", "geoip" : { ``` ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [November 7, 2022, 4:17pm UTC](https://discuss.elastic.co/t/kql-query-for-fields-with-a-value-which-is-not/318098/6 "2022-11-07T16:17:27Z")
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: "-"` ```auto 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 ![Screen Shot 2022-11-07 at 8.13.00 AM](https://us1.discourse-cdn.com/elastic/original/3X/4/b/4b2c5e45785294097e726b364c19afab493cb650.png) ![Screen Shot 2022-11-07 at 8.15.02 AM](https://us1.discourse-cdn.com/elastic/original/3X/8/7/877f457671429df92d449546608b8a2d2d4dafb0.png) ![Screen Shot 2022-11-07 at 8.13.15 AM](https://us1.discourse-cdn.com/elastic/original/3X/f/a/fafdd93ecc905884b5544f0d8d002cde1b0024eb.png) ![Screen Shot 2022-11-07 at 8.13.25 AM](https://us1.discourse-cdn.com/elastic/original/3X/e/7/e7d1fe13acee5851b5eb6c2ae807e3e571e6894f.png) ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [November 7, 2022, 4:36pm UTC](https://discuss.elastic.co/t/kql-query-for-fields-with-a-value-which-is-not/318098/7 "2022-11-07T16:36:22Z")
@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 ![Screen Shot 2022-11-07 at 8.26.48 AM](https://us1.discourse-cdn.com/elastic/original/3X/2/c/2cdc11b5a2acdac73585209672f0e22d74d65740.png) 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. ---
**Author:** ![mikewillis](https://avatars.discourse-cdn.com/v4/letter/m/b2d939/32.png) [@mikewillis](https://discuss.elastic.co/u/mikewillis)\ **Post date:** [November 8, 2022, 11:12am UTC](https://discuss.elastic.co/t/kql-query-for-fields-with-a-value-which-is-not/318098/8 "2022-11-08T11:12:08Z")
`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` ```auto "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 ```auto "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 ```auto "username" : "", "elapsed_time" : 27, ``` which Kibana shows as `(empty)`. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 6, 2022, 11:12am UTC](https://discuss.elastic.co/t/kql-query-for-fields-with-a-value-which-is-not/318098/9 "2022-12-06T11:12:35Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.