Trying to apply filter on scripted field

[Shubham_Mahajan]

Hi team, previously I made a scripted field regarding folr calculcation time difference.

Link to previous post: Elasticsearch giving warning for painless script

time-diff: new Date().getTime() - doc['@timestamp'].value.getMillis()

I need help regarding this functioning. Using above scripted field I am displaying hosts with their last communication time.

But now i just want to display the hosts with time difference greater than, let's say, 7 days only.
Is there any way to compare the time-diff scripted field and only display those hosts that have not sent events for few days, in this case.

Really appreciate your help!!

[Joe_Fleming]

My understanding is that the only way to use scripted fields as filters in Elasticsearch is to us a scripted query.

This functionality isn't exposed nicely in Kibana, but it is available using the filter bar and the filter DSL. Below, you'll see I have a query filter on just a normal field. You should be able to replace the entire query with a scripted query, and while you don't be able to use the scripted field directly, you can re-use the syntax from the scripted field in the scripted query.

[Shubham_Mahajan]

Hi @Joe_Fleming ,thanks for you reply. In my problem, the script query on time-diff field will not give the appropriate result.. i guess. Because all the previous documents will have the time-diff as 8 days if i apply the filter of 8 days, so each of the host would be displayed in the table. But i just want those hosts that were active 7 days ago but not in the present.

So, what is the way to apply an appropriate filter query on the documents???

[Joe_Fleming]

I think I need to understand mode about the data you're indexing to answer this. Specifically, how are you getting the "Last interacted since" value in the table you posted?

[Shubham_Mahajan]

@Joe_Fleming I am defining these scripted fields in metricbeat index.
As you can see the below indexed document, time-diff is being used for getting last interaction time.

But if I apply filter on this field in data table, it just get the documents in the given time frame and that would give all the hosts basically. Because they all would have time-diff field with value 7 days, as time-diff is now-@timestamp

[Shubham_Mahajan]

@Joe_Fleming Any updates on this problem??

[Joe_Fleming]

OK, so you have a bunch of metricbeat data, just a series of documents as that data comes in, and then a scripted field that is now-@timestamp, and it sounds like you want to see a list of the hosts from which you haven't seen data in 7 days.

If I got that right, I don't think you can actually do that in Kibana. You can probably fake it, kind of, without using the scripted field. Instead, do a normal aggregation on the host field, and use the max metric on the @timestampfield.

This will give you a table of all the hosts and the last time you got data from them. Set the time picker to some larger range, maybe the last 2 weeks or something, and then you'll see all the hosts that have sent data within the last 2 weeks, and the last time data came in. Sort by that metric value and you'll see hosts that haven't sent data in a while at the top. It's not exactly what you want, but it provides roughly the same information.