# Where statement in Kibana search? **URL:** \ **Category:** Kibana\ **Created:** [March 5, 2017, 10:49pm UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432 "2017-03-05T22:49:54Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![prophoto](https://avatars.discourse-cdn.com/v4/letter/p/f6c823/32.png) [@prophoto](https://discuss.elastic.co/u/prophoto)\ **Post date:** [March 5, 2017, 10:49pm UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432/1 "2017-03-05T22:49:54Z")
This is a typical log line from Apache being stored in AWS Elasticsearch. I'd like to be able to add a viz to my dashboard showing top referrers. The problem is that many static files have referrers from its own domain which prevents me from seeing the data I want. Is it possible to have a search expression like "where REFERRER does not contain VHOST" 123.456.78.9 - - [15/Feb/2017:18:33:25 +0000] [example.com](http://example.com) "GET / HTTP/1.1" 200 42766 "[http://facebook.com/](http://facebook.com/)" "Mozilla/5.0 (iPhone; CPU iPhone OS 10\_0\_2 like Mac OS X) AppleWebKit/602.1.50 (KHTML, like Gecko) Version/10.0 Mobile/14A456 Safari/602.1" Server=aws8 SSL=- 8868 0 123.456.78.9 - - [15/Feb/2017:18:33:25 +0000] [example.com](http://example.com) "GET /js/lib/jquery-ui/jquery-ui.js HTTP/1.1" 200 42766 "[http://example.com/](http://example.com/)" "Mozilla/5.0 (iPhone; CPU iPhone OS 10\_0\_2 like Mac OS X) AppleWebKit/602.1.50 (KHTML, like Gecko) Version/10.0 Mobile/14A456 Safari/602.1" Server=aws8 SSL=- 8868 0 This is obviously a custom log, here is them mapping. [ip, user, username, timestamp, vhost, request, status\_code, bytes, referrer, browser, server, ssl, timems, times] ---
**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\ **Post date:** [March 6, 2017, 2:53am UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432/2 "2017-03-06T02:53:06Z")
You could create a boolean scripted field for the index which would be true if VHOST is in REFERRER and then use that to filter those out in Discover, Visualize, etc. Select `painless` as the script language, `boolean` as the type and a script something like; Although you can use regular expressions in painless scripted fields, they are disabled in Elasticsearch by default because of potential performance implications. You should instead try to use something like ; `if (doc['referrer'].value.indexOf(doc['vhost']) ) > 0; return true; else return false` (that's probably not exactly right, but you basically want to check if the index of vhost in referrer is \> 0) Here's a blog post; > **[Using Painless in Kibana scripted fields | Elastic](https://www.elastic.co/blog/using-painless-kibana-scripted-fields)** > > Kibana provides powerful ways to search and visualize data stored in Elasticsearch. For the purpose of visualizations, Kibana looks for fields defined in Elasticsearch mappings and presents them as op... And the docs; [https://www.elastic.co/guide/en/kibana/current/scripted-fields.html](https://www.elastic.co/guide/en/kibana/current/scripted-fields.html) ---
**Author:** ![prophoto](https://avatars.discourse-cdn.com/v4/letter/p/f6c823/32.png) [@prophoto](https://discuss.elastic.co/u/prophoto)\ **Post date:** [March 6, 2017, 5:05pm UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432/3 "2017-03-06T17:05:35Z")
Thanks for your help, here is the error message I'm getting. Error: Request to Elasticsearch failed: {"error":{"root\_cause":[{"type":"script\_exception","reason":"compile error","script\_stack":["... .indexOf(doc['vhost']) ) \> 0; return true; else re ..."," ^---- HERE"],"script":"if (doc['referrer'].value.indexOf(doc['vhost']) ) \> 0; return true; else return false","lang":"painless"}],"type":"search\_phase\_execution\_exception","reason":"all shards failed","phase":"query","grouped":true,"failed\_shards":[{"shard":0,"index":"cwl-2017.03.06","node":"8lpyxo4oSjeww7cR1OtWDg","reason":{"type":"script\_exception","reason":"compile error","caused\_by":{"type":"illegal\_argument\_exception","reason":"invalid sequence of tokens near ['\>'].","caused\_by":{"type":"no\_viable\_alt\_exception","reason":null}},"script\_stack":["... .indexOf(doc['vhost']) ) \> 0; return true; else re ..."," ^---- HERE"],"script":"if (doc['referrer'].value.indexOf(doc['vhost']) ) \> 0; return true; else return false","lang":"painless"}}],"caused\_by":{"type":"script\_exception","reason":"compile error","caused\_by":{"type":"illegal\_argument\_exception","reason":"invalid sequence of tokens near ['\>'].","caused\_by":{"type":"no\_viable\_alt\_exception","reason":null}},"script\_stack":["... .indexOf(doc['vhost']) ) \> 0; return true; else re ..."," ^---- HERE"],"script":"if (doc['referrer'].value.indexOf(doc['vhost']) ) \> 0; return true; else return false","lang":"painless"}},"status":500} ---
**Author:** ![prophoto](https://avatars.discourse-cdn.com/v4/letter/p/f6c823/32.png) [@prophoto](https://discuss.elastic.co/u/prophoto)\ **Post date:** [March 6, 2017, 5:11pm UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432/4 "2017-03-06T17:11:35Z")
Tried this and now getting a different error. Script: if ((doc['referrer'].value.indexOf(doc['vhost']) ) \> 0) {return true; } else { return false; } Error: Courier Fetch: 5 of 5 shards failed. ---
**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\ **Post date:** [March 6, 2017, 7:27pm UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432/5 "2017-03-06T19:27:42Z")
Yep, that's the kind of error you get when it can't evaluate the script for some reason. I think you might need to add the `.value` on the vhost like; `if ((doc['referrer'].value.indexOf(doc['vhost'].value) ) > 0) {return true; } else { return false; }` Oh, and if one string matches the other than indexOf returns 0, so you might need to use either `> -1` or `>= 0`. ---
**Author:** ![prophoto](https://avatars.discourse-cdn.com/v4/letter/p/f6c823/32.png) [@prophoto](https://discuss.elastic.co/u/prophoto)\ **Post date:** [March 6, 2017, 8:05pm UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432/6 "2017-03-06T20:05:58Z")
Thanks for your continued help. Still getting error message "Courier Fetch: 5 of 5 shards failed." ---
**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\ **Post date:** [March 6, 2017, 10:00pm UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432/7 "2017-03-06T22:00:55Z")
Can you check the Fields tab of your index pattern and look at your referrer and vhost fields. Are they both searchable and aggregatable? I think they need to be in order to use them in a scripted field. Do you have the language set to painless and the type set to boolean? Does every document in this index have a value for both of those fields? You could use the Discover tab to check by using the `*` icon on those fields; ![](https://us1.discourse-cdn.com/elastic/original/3X/c/b/cb6a9fca1008c7d3a710ba35245e909d13cb7e36.png) and then negate that exists filter by clicking the magnifying glass with the `-` in it; ![](https://us1.discourse-cdn.com/elastic/original/3X/4/3/43dea05fb09e0705c5e110b28eb3aa1ced43d484.png) If there are docs where either of those fields don't have a value, you'll have to use a little bit more complex script fir the scripted field. I can try to find a good example of that if you find that's the case. An intermediate step you could try to help debug your scripted field would be to make another scripted field of type `number` and just try to find the index of vhost in referrer like this; `doc['referrer'].value.indexOf(doc['vhost'].value)` Then check in Discover if you're getting that numeric value. You'll have to delete the existing scripted field so that you don't get the `shards failed` error on it. ---
**Author:** ![prophoto](https://avatars.discourse-cdn.com/v4/letter/p/f6c823/32.png) [@prophoto](https://discuss.elastic.co/u/prophoto)\ **Post date:** [March 7, 2017, 4:15pm UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432/8 "2017-03-07T16:15:37Z")
Neither field is set to aggregatable, both are set to searchable. How do I fix that? I do have fields called vhost.keyword and referrer.keyword which are both set to aggregatable and searchable. Yes on painless/boolean. Yes on values in both fields. Apache puts a dash "-" in the log line when there is no data. I checked as you specified too. The last test with "doc['referrer'].value.indexOf(doc['vhost'].value)" as a new script with number as the type gave the same error "Courier Fetch: 5 of 5 shards failed." ---
**Author:** ![prophoto](https://avatars.discourse-cdn.com/v4/letter/p/f6c823/32.png) [@prophoto](https://discuss.elastic.co/u/prophoto)\ **Post date:** [March 7, 2017, 4:17pm UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432/9 "2017-03-07T16:17:56Z")
This might be working! if ((doc['referrer.keyword'].value.indexOf(doc['vhost.keyword'].value) ) \> 0) {return true; } else { return false; } ---
**Author:** ![prophoto](https://avatars.discourse-cdn.com/v4/letter/p/f6c823/32.png) [@prophoto](https://discuss.elastic.co/u/prophoto)\ **Post date:** [March 7, 2017, 4:29pm UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432/10 "2017-03-07T16:29:59Z")
I have no errors which is a step in the right direction! BUT I can't seem to use that field in Discover. I've called the new scripted field vhostIsReferrer. I've tried the following in Discover and Visualizer: vhostIsReferrer:"true" vhostIsReferrer:"false" vhostIsReferrer:true vhostIsReferrer:false vhostIsReferrer:0 vhostIsReferrer:1 ---
**Author:** ![prophoto](https://avatars.discourse-cdn.com/v4/letter/p/f6c823/32.png) [@prophoto](https://discuss.elastic.co/u/prophoto)\ **Post date:** [March 7, 2017, 4:53pm UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432/11 "2017-03-07T16:53:04Z")
Tried a bunch of Visualizations and no go.... ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 4, 2017, 4:53pm UTC](https://discuss.elastic.co/t/where-statement-in-kibana-search/77432/12 "2017-04-04T16:53:31Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.