# Data set difference between fields on different indexes
**URL:** https://discuss.elastic.co/t/data-set-difference-between-fields-on-different-indexes/160015
**Category:** Elasticsearch
**Created:** [December 8, 2018, 7:41pm UTC](https://discuss.elastic.co/t/data-set-difference-between-fields-on-different-indexes/160015 "2018-12-08T19:41:28Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@nandrik](https://discuss.elastic.co/u/nandrik)
#### Post date: [December 8, 2018, 7:41pm UTC](https://discuss.elastic.co/t/data-set-difference-between-fields-on-different-indexes/160015/1 "2018-12-08T19:41:29Z")
Hi, per my previous [post](https://discuss.elastic.co/t/best-way-to-store-2-data-sources-with-1-linked-field-document-type-or-new-index/158661/12), I ended up **creating two separate indexes** (`Index 1`, `Index 2`) **sharing one common field** `dstIP`.
I'd like now, let's say on an hourly basis, to get a list of all the IP addresses that exist in `Index 1` but not in `Index 2`.
What is the best way of accomplishing this? A scripted filter, a DSL filter query?
Can the outcome be visualized for further understanding of where these IPs come from?
Thanks.
---
### Author:  [@abdon](https://discuss.elastic.co/u/abdon)
#### Post date: [December 10, 2018, 5:30pm UTC](https://discuss.elastic.co/t/data-set-difference-between-fields-on-different-indexes/160015/2 "2018-12-10T17:30:37Z")
I would solve this with aggregations rather than queries. You start with a [composite aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html), to get a list of all unique IPs across the two indexes.
Nested inside of that you use a [terms aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html) on the `_index` meta field to get a bucket for each index in which each IP exists. IPs that occur in both indexes will get two buckets. IPs that occur in only one index get one bucket.
Finally, you can use a [bucket\_selector pipeline aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-selector-aggregation.html) to filter out those IP addresses for which there are two buckets. Or, in other words, retrieve only the IPs that occur in only one index.
That request would look like this:
```auto
GET index1,index2/_search
{
"size": 0,
"aggs": {
"all_ips": {
"composite": {
"sources": [
{
"dstIP": {
"terms": {
"field": "dstIP"
}
}
}
]
},
"aggs": {
"indexes_per_ip": {
"terms": {
"field": "_index",
"size": 2
}
},
"index_count_bucket_filter": {
"bucket_selector": {
"buckets_path": {
"index_count": "indexes_per_ip._bucket_count"
},
"script": "params.index_count == 1"
}
}
}
}
}
}
```
---
### Author:  [@nandrik](https://discuss.elastic.co/u/nandrik)
#### Post date: [December 10, 2018, 8:55pm UTC](https://discuss.elastic.co/t/data-set-difference-between-fields-on-different-indexes/160015/3 "2018-12-10T20:55:33Z")
Thank you @abdon for your reply.
I ran the query, replacing `index1` and `index2` with the daily indexes for my two data logs.
The result I got was:
```auto
{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 2,
"successful" : 2,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : 240139,
"max_score" : 0.0,
"hits" : []
},
"aggregations" : {
"all_ips" : {
"after_key" : {
"dstIP" : "5.189.xx.xx"
},
"buckets" : [
{
"key" : {
"dstIP" : "5.135.xx.xx"
},
"doc_count" : 1,
"indexes_per_ip" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "filebeat-name-of-index1-2018.12.08",
"doc_count" : 1
}
]
}
}
]
}
}
}
```
The first dstIP, `5.189.x.x` mentioned appears on both indexes; second one is unique to index\_1.
Is this how this was meant to work?
Suprised that index2 does not have more unique to it IP addresses since my Unique IP graphs per index shows different numbers (see below). Also, funny to notice that despite setting the time from 7pm EST time to 6:59pm EST the next day, index\_2 which includes network data and has likely a delay, seems to be spilling over the index of the next day.
---
### Author:  [@abdon](https://discuss.elastic.co/u/abdon)
#### Post date: [December 11, 2018, 7:52am UTC](https://discuss.elastic.co/t/data-set-difference-between-fields-on-different-indexes/160015/4 "2018-12-11T07:52:01Z")
The composite aggregation does not return all results at once. It allows you to page through the buckets. The `after_key` that's returned is something you can use to get the next page of buckets, [using the `after` parameter in a subsequent request](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html#_after):
```auto
GET logs_server*/_search
{
"size": 0,
"aggs": {
"all_ips": {
"composite": {
"sources": [
{
"dstIP": {
"terms": {
"field": "geoip.city_name.keyword"
}
}
}
],
"after": { "dstIP" : "5.189.xx.xx" }
},
"aggs": {
"indexes_per_ip": {
"terms": {
"field": "_index",
"size": 2
}
},
"index_count_bucket_filter": {
"bucket_selector": {
"buckets_path": {
"index_count": "indexes_per_ip._bucket_count"
},
"script": "params.index_count == 1"
}
}
}
}
}
}
```
To make paging more efficient, you could set the [`size` parameter](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html#_size) in the composite aggregation to for example 1000.
---
### Author:  [@nandrik](https://discuss.elastic.co/u/nandrik)
#### Post date: [December 11, 2018, 11:22pm UTC](https://discuss.elastic.co/t/data-set-difference-between-fields-on-different-indexes/160015/5 "2018-12-11T23:22:38Z")
@abdon, thanks so much. I wonder, is there any way to save this aggregation within Kibana so that I can do visualizations on top of it?
---
### Author:  [@abdon](https://discuss.elastic.co/u/abdon)
#### Post date: [December 13, 2018, 5:38pm UTC](https://discuss.elastic.co/t/data-set-difference-between-fields-on-different-indexes/160015/6 "2018-12-13T17:38:36Z")
As far as I know, you can't visualize an aggregation like this with regular Kibana visualizations. You can try building a [Vega visualization](https://www.elastic.co/guide/en/kibana/current/vega-graph.html), but those have a bit of a learning curve. [This tutorial](https://www.elastic.co/blog/getting-started-with-vega-visualizations-in-kibana) is a good way to get started.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [January 10, 2019, 5:38pm UTC](https://discuss.elastic.co/t/data-set-difference-between-fields-on-different-indexes/160015/7 "2019-01-10T17:38:37Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.