# Scripted Fields + Filtering
**URL:** https://discuss.elastic.co/t/scripted-fields-filtering/262047
**Category:** Kibana
**Tags:** painless
**Created:** [January 25, 2021, 3:12am UTC](https://discuss.elastic.co/t/scripted-fields-filtering/262047 "2021-01-25T03:12:09Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@Rob\_wylde](https://discuss.elastic.co/u/Rob_wylde)
#### Post date: [January 25, 2021, 3:12am UTC](https://discuss.elastic.co/t/scripted-fields-filtering/262047/1 "2021-01-25T03:12:09Z")
I am trying to use painless to calculate the duration of 2 ISO8601 timestamps The field is correctly populated with the returned string i have defined. However the field can't not be filtered upon / with. This is my first time utilizing script fields and painless scripting.
Language: Painless
Type: String
Format: String
Transform: none
```auto
if (doc.containsKey('start_date') && doc.containsKey('last_update'))
{
if (doc['start_date'].size() > 0 && doc['last_update'].size() > 0 )
{
def time_diff = doc['last_update'].value.millis - doc['start_date'].value.millis;
def minutes = time_diff / 60000;
// minutes > 30 ? 'Open': 'Closed'
if ( minutes > 30 )
{
return 'Open'
}else{
return 'Closed'
}
}
}
```
When i go to filter by clicking the + or - button next to the field or using the 'add filter' button i get an error.
```auto
search_phase_execution_exception
_construct@https://domain.com/35949/bundles/core/core.entry.js:6:4859
Wrapper@https://domain.com/35949/bundles/core/core.entry.js:6:4249
_createSuperInternal@https://domain.com/35949/bundles/core/core.entry.js:6:3388
HttpFetchError@https://domain.com/35949/bundles/core/core.entry.js:6:6016
_callee3$@https://domain.com/35949/bundles/core/core.entry.js:6:59535
l@https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:380:1740519
s/o._invoke
### Author:  [@stu](https://discuss.elastic.co/u/stu)
#### Post date: [January 25, 2021, 11:00pm UTC](https://discuss.elastic.co/t/scripted-fields-filtering/262047/2 "2021-01-25T23:00:35Z")
Hi @Rob_wylde
What are your mappings for `start_date` and `last_update`? If they're `date` then you can do
```auto
def time_diff = doc['last_update'].value.toInstant().toEpochMilli() -
doc['start_date'].value.toInstant().toEpochMilli()
```
If you need to parse them because they're keyword, you'll need to parse them using some [Datetime methods](https://www.elastic.co/guide/en/elasticsearch/painless/current/painless-datetime.html).
Something like:
```auto
ZonedDateTime start = ZonedDateTime.parse(doc['last_update.keyword'].value);
ZonedDateTime last = ZonedDateTime.parse(doc['last_update.keyword'].value);
def time_diff = start.toInstant().toEpochMilli() -
last.toInstant().toEpochMilli();
```
It's hard to tell, can you provide the exception returned by elasticsearch? It'll look something like:
```auto
{
"error" : {
"root_cause" : [
{
"type" : "script_exception",
"reason" : "runtime error",
"script_stack" : []
...
"script": ""
...
"lang" : "painless",
"position" : {
"offset" : 375,
"start" : 331,
"end" : 475
}
```
---
### Author:  [@Rob\_wylde](https://discuss.elastic.co/u/Rob_wylde)
#### Post date: [January 26, 2021, 5:24am UTC](https://discuss.elastic.co/t/scripted-fields-filtering/262047/3 "2021-01-26T05:24:09Z")
Stu,
Thanks for the reply. I've tried value.toInstant().toEpochMilli() as suggested as both fields are mapped as 'date'. Once again the scripted field gets populated but I can not filter on this field. I'm not sure how to get the exception error from elastic within kibana
Below is what i see in the Firefox console
```auto
statusCode 400
error "Bad Request"
message "status_exception"
attributes Object { error: {…} }
error Object { type: "status_exception", reason: "error while executing search", caused_by: {…} }
type "status_exception"
reason "error while executing search"
caused_by Object { type: "search_phase_execution_exception", phase: "fetch", grouped: true, … }
type "search_phase_execution_exception"
reason ""
```
Also and probably more likely the issue.
```auto
Firefox
Content Security Policy: The page’s settings blocked the loading of a resource at inline (“script-src”).
Chrome
Refused to execute inline script because it violates the following Content Security Policy directive: "script-src 'unsafe-eval' 'self'". Either the 'unsafe-inline' keyword, a hash ('sha256-P5polb1UreUSOe5V/Pv7tc+yeZuJXiOi/389hGsU7BE='), or a nonce ('nonce-...') is required to enable inline execution.
^ A single error about an inline script not firing due to content security policy is expected!
```
Should be noted that i'm running ECE 2.6 with 7.10.1 .. I'm not seeing anything glaring in the logging and metrics kibana interface ( where i ship my logs ).I'm also using Nginx as a reverse proxy but I've tried filtering via this scripted field while bypassing nginx and experience the same behavior.
Thanks
---
### Author:  [@stu](https://discuss.elastic.co/u/stu)
#### Post date: [January 26, 2021, 3:47pm UTC](https://discuss.elastic.co/t/scripted-fields-filtering/262047/4 "2021-01-26T15:47:17Z")
> [@Rob\_wylde](#):
>
> ```auto
> attributes Object { error: {…} }
> error Object { type: "status_exception", reason: "error while executing search", caused_by: {…} }
>
> ```
Can you expand those objects? That's where the juicy parts of the error is.
> Also and probably more likely the issue.
The error you quoted above indicates that the request has successful hit elasticsearch, so the CSP problem is likely unrelated.
---
### Author:  [@Rob\_wylde](https://discuss.elastic.co/u/Rob_wylde)
#### Post date: [January 26, 2021, 7:35pm UTC](https://discuss.elastic.co/t/scripted-fields-filtering/262047/5 "2021-01-26T19:35:27Z")
Stu,
```auto
XHRPOSThttps://domain.com/internal/search/ese
[HTTP/1.1 400 Bad Request 183ms]
1
{"statusCode":400,"error":"Bad Request","message":"status_exception","attributes":{"error":{"type":"status_exception","reason":"error while executing search","caused_by":{"type":"search_phase_execution_exception","reason":"","phase":"fetch","grouped":true,"failed_shards":[{"shard":0,"index":"trouble_conditions-2021","node":"Qnt6JaU9RW24Og8rpAqrjA","reason":{"type":"query_shard_exception","reason":"failed to create query: compile error","index_uuid":"Ck8UTBhATpKnUdhSGRmqPg","index":"trouble_conditions-2021","caused_by":{"type":"script_exception","reason":"compile error","script_stack":["... rn s.get() == v;}compare(() -> { if (doc.containsK ..."," ^---- HERE"],"script":"boolean compare(Supplier s, def v) {return s.get() == v;}compare(() -> { if (doc.containsKey('start_date') && doc.containsKey('last_update'))\n{\n if (doc['start_date'].size() > 0 && doc['last_update'].size() > 0 )\n {\n def time_diff = doc['last_update'].value.toInstant().toEpochMilli() - doc['start_date'].value.toInstant().toEpochMilli();\n def minutes = time_diff / 60000;\n minutes > 30 ? \"Ongoing\": \"Closed\"\n }\n} }, params.value);","lang":"painless","position":{"offset":65,"start":40,"end":90},"caused_by":{"type":"illegal_argument_exception","reason":"not all paths return a value for lambda"}}}}],"caused_by":{"type":"null_pointer_exception","reason":"Cannot invoke \"org.elasticsearch.search.aggregations.InternalAggregations.getSerializedSize()\" because \"reducePhase.aggregations\" is null"}}}}}
```
Apologies for the formatting
---
### Author:  [@BenB196](https://discuss.elastic.co/u/BenB196)
#### Post date: [January 26, 2021, 7:58pm UTC](https://discuss.elastic.co/t/scripted-fields-filtering/262047/6 "2021-01-26T19:58:06Z")
Instead of using two if statements, you might want to try:
```auto
if (!doc['start_date'].empty && !doc['last_update'].empty)
```
Your script would look like:
```auto
if (!doc['start_date'].empty && !doc['last_update'].empty)
{
def time_diff = doc['last_update'].value.millis - doc['start_date'].value.millis;
def minutes = time_diff / 60000;
// minutes > 30 ? 'Open': 'Closed'
if ( minutes > 30 )
{
return 'Open'
} else {
return 'Closed'
}
}
```
This topic explains a similar issue: [Painless, if two field exists then calculate](https://discuss.elastic.co/t/painless-if-two-field-exists-then-calculate/117489/6)
---
### Author:  [@Rob\_wylde](https://discuss.elastic.co/u/Rob_wylde)
#### Post date: [January 26, 2021, 8:05pm UTC](https://discuss.elastic.co/t/scripted-fields-filtering/262047/7 "2021-01-26T20:05:52Z")
Ben,
I've tried your code and i get the same results but I also now get ttScope error
```auto
TypeError: ttScope is null
hide https://domain.com/35949/bundles/plugin/kibanaLegacy/kibanaLegacy.plugin.js:1
hideTooltipBind https://domain.com/35949/bundles/plugin/kibanaLegacy/kibanaLegacy.plugin.js:1
$digest https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
$apply https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
_callee$ https://domain.com/35949/bundles/plugin/discover/discover.chunk.4.js:1
l https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:380
_invoke https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:380
t https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:380
create_doc_table_react_asyncGeneratorStep https://domain.com/35949/bundles/plugin/discover/discover.chunk.4.js:1
_next https://domain.com/35949/bundles/plugin/discover/discover.chunk.4.js:1
promise callback*create_doc_table_react_asyncGeneratorStep https://domain.com/35949/bundles/plugin/discover/discover.chunk.4.js:1
_next https://domain.com/35949/bundles/plugin/discover/discover.chunk.4.js:1
create_doc_table_react_asyncToGenerator https://domain.com/35949/bundles/plugin/discover/discover.chunk.4.js:1
create_doc_table_react_asyncToGenerator https://domain.com/35949/bundles/plugin/discover/discover.chunk.4.js:1
injectAngularElement https://domain.com/35949/bundles/plugin/discover/discover.chunk.4.js:1
convertDirectiveToRenderFn https://domain.com/35949/bundles/plugin/discover/discover.chunk.4.js:1
DocTableLegacy https://domain.com/35949/bundles/plugin/discover/discover.chunk.4.js:1
ps https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
El https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
unstable_runWithPriority https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:442
Hi https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
Sl https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
ol https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
Gi https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
unstable_runWithPriority https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:442
Hi https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
Gi https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
qi https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
el https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
$l https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
Gl https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
render https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:434
renderComponent https://domain.com/35949/bundles/plugin/kibanaLegacy/kibanaLegacy.plugin.js:1
$digest https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
evalAsync https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
completeTask https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
r https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
setTimeout handler*cn/o.defer https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
$evalAsync https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
get https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
d https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
e https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
o https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
promise callback*e https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
h https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
d https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
d https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
$digest https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
$apply https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
callInDigest https://domain.com/35949/bundles/plugin/kibanaLegacy/kibanaLegacy.plugin.js:1
next https://domain.com/35949/bundles/plugin/kibanaLegacy/kibanaLegacy.plugin.js:1
__tryOrUnsub https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:21
next https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:21
_next https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:21
next https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:21
debouncedNext https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:480
oe https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:480
_execute https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:340
execute https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:340
flush https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:340
setInterval handler* __kbnSharedDeps__ Function:4
i https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
$eval https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
$apply https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
compile https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:426
dispatch https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:404
handle https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:404
add https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:404
Le https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:404
each https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:393
each https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:393
Le https://domain.com/35949/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:404
kbn-ui-shared-deps.js:426:78980
```
Thanks for the suggestion
---
### Author:  [@stu](https://discuss.elastic.co/u/stu)
#### Post date: [February 1, 2021, 4:40pm UTC](https://discuss.elastic.co/t/scripted-fields-filtering/262047/8 "2021-02-01T16:40:13Z")
@Rob_wylde
We need the error from elasticsearch, not kibana, to diagnose this issue.
From what you posted above:
```auto
"caused_by":{
"type":"null_pointer_exception","reason":"Cannot invoke \"org.elasticsearch.search.aggregations.InternalAggregations.getSerializedSize()\" because \"reducePhase.aggregations\" is null"}
```
Tells me your script is returning null in some cases. You'll need to return a default value if your conditionals do not match. Something like:
```auto
if (!doc['start_date'].empty && !doc['last_update'].empty)
{
...
}
return 'Closed'
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [March 1, 2021, 4:40pm UTC](https://discuss.elastic.co/t/scripted-fields-filtering/262047/9 "2021-03-01T16:40:15Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.