# Can we extract the 7 days stored common value from the elastic search database and visulazize that information on kibana

**URL:** <https://discuss.elastic.co/t/can-we-extract-the-7-days-stored-common-value-from-the-elastic-search-database-and-visulazize-that-information-on-kibana/329883>\
**Category:** Elasticsearch\
**Tags:** kql-kibana-query-language\
**Created:** [April 13, 2023, 5:29am UTC](https://discuss.elastic.co/t/can-we-extract-the-7-days-stored-common-value-from-the-elastic-search-database-and-visulazize-that-information-on-kibana/329883 "2023-04-13T05:29:57Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tanzeela](https://avatars.discourse-cdn.com/v4/letter/t/e99b99/32.png) [@Tanzeela](https://discuss.elastic.co/u/Tanzeela)\
**Post date:** [April 13, 2023, 5:29am UTC](https://discuss.elastic.co/t/can-we-extract-the-7-days-stored-common-value-from-the-elastic-search-database-and-visulazize-that-information-on-kibana/329883/1 "2023-04-13T05:29:57Z")

</div>

Hi @drewdaemon , hope you are doing well.  
Can we write a query that will extract the common value that is stored in Elasticsearch for 7 days and create the visualisation for the extracted information?

for example:  
day1  
equipment : A1, A2,A3  
day2  
equipment : A2,A3,A5  
day3  
equipment : A2,A3,A1,A7  
day7  
equipment : A2,A3,A6

common value for 7 days is :

equipment :A2 , A3

---

<div class="post-metadata">

**Author:** ![drewdaemon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/drewdaemon/32/97779_2.png) [@drewdaemon](https://discuss.elastic.co/u/drewdaemon)\
**Post date:** [April 13, 2023, 8:16pm UTC](https://discuss.elastic.co/t/can-we-extract-the-7-days-stored-common-value-from-the-elastic-search-database-and-visulazize-that-information-on-kibana/329883/2 "2023-04-13T20:16:26Z")

</div>

Hi @Tanzeela: is your data stored with each recorded piece of equipment being a document in your index?

For example

```auto
{
  "@timestamp": "sometime in day 1",
  "equipmentId": "A1",
}

{
  "@timestamp": "sometime in day 1",
  "equipmentId": "A2",
}

{
  "@timestamp": "sometime in day 1",
  "equipmentId": "A3",
}

```

---

<div class="post-metadata">

**Author:** ![Tanzeela](https://avatars.discourse-cdn.com/v4/letter/t/e99b99/32.png) [@Tanzeela](https://discuss.elastic.co/u/Tanzeela)\
**Post date:** [April 14, 2023, 6:07am UTC](https://discuss.elastic.co/t/can-we-extract-the-7-days-stored-common-value-from-the-elastic-search-database-and-visulazize-that-information-on-kibana/329883/3 "2023-04-14T06:07:31Z")

</div>

Hi @drewdaemon ,  
Thanks for your response,

After 1 minute, our data appears in Kibana and is saved in the poweredoffDevices list as shown in attached figure.  
I want to extract the common value of poweredoffDevices that comes from 7 days and keep that information in a list or variable to create a visualisation that shows the following devices that have been powered off for 7 days. Is there any way to do this task?

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/1/d/1d2d93e833773bed497be02966af29b764250749.png)

---

<div class="post-metadata">

**Author:** ![drewdaemon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/drewdaemon/32/97779_2.png) [@drewdaemon](https://discuss.elastic.co/u/drewdaemon)\
**Post date:** [April 14, 2023, 1:43pm UTC](https://discuss.elastic.co/t/can-we-extract-the-7-days-stored-common-value-from-the-elastic-search-database-and-visulazize-that-information-on-kibana/329883/4 "2023-04-14T13:43:59Z")

</div>

Okay, so essentially your data is being ingested in one-minute buckets, one document per bucket.

> [@Tanzeela](#):
>
> I want to extract the common value of poweredoffDevices that comes from 7 days and keep that information in a list or variable to create a visualisation that shows the following devices that have been powered off for 7 days. Is there any way to do this task?

I do have an idea as to how to do this, but it will be a big effort and will require you to learn several Elastic technologies since we don't support this out-of-the-box.

You'd probably need to

1. compose a custom Elasticsearch query to return the results you need
2. build a custom visualization to display the results of that query

If you want to try, I would start by using [the dev console](https://www.elastic.co/guide/en/kibana/8.7/console-kibana.html) to get the Elasticsearch query right.

I believe you'll need to define a custom [aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations.html) in your query. I'd try the [scripted metric](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-scripted-metric-aggregation.html#search-aggregations-metrics-scripted-metric-aggregation) which allows you to define custom logic for the map-reduce phases. My guess is that you can use this to gather only the devices that are in common for all the documents.

Once the query is returning the right results, you'd need to use [Vega](https://www.elastic.co/guide/en/kibana/8.7/vega.html) to define a custom visualization.

We are currently working on a query language called [ESQL](https://www.elastic.co/blog/introduction-to-esql-new-query-language-flexible-iterative-analytics). Hopefully when that lands, it will make this a lot easier by allowing you to use Lens.

---

<div class="post-metadata">

**Author:** ![Tanzeela](https://avatars.discourse-cdn.com/v4/letter/t/e99b99/32.png) [@Tanzeela](https://discuss.elastic.co/u/Tanzeela)\
**Post date:** [April 17, 2023, 4:49am UTC](https://discuss.elastic.co/t/can-we-extract-the-7-days-stored-common-value-from-the-elastic-search-database-and-visulazize-that-information-on-kibana/329883/5 "2023-04-17T04:49:19Z")

</div>

Hi @drewdaemon , Good morning. Thanks for your kind response and provided guidelines.

I have written these Elastic query on Dev tools, but i am not satisfied with their responses.

1st query:

GET health\_ipppol\_gateway/\_search  
{  
"query": {  
"range": {  
"Data.Created": {  
"gte": "now-1d/d",  
"lte": "now/d"  
}  
}  
},  
"aggs": {  
"common\_terms": {  
"terms": {  
"field": "Data.PoweredOff\_Downtime\_devices.PoweredOffDevices.deviceID.keyword",  
"size": 50  
}  
}  
}

}

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/0/8/08e3fdf7e75994294451d6aa10a2864a0cd69091.png)

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/c/1/c112be5ad9dbd4887ba592d0d61e6d48aa28a7b3.png)

2nd Query:

GET health\_ipppol\_gateway/\_search  
{  
"query": {  
"range": {  
"Data.Created": {  
"gte": "now-1d/d",  
"lte": "now/d"  
}  
}  
},

"aggs": {  
"unique\_count": {  
"cardinality": {  
"field": "Data.PoweredOff\_Downtime\_devices.PoweredOffDevices.deviceID.keyword"  
}  
}  
}

}

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/7/1/71be4f1e4140f05475fa57f3e054aea373388f6b.png)

According to your instructions, I think I am near to it, but I am not satisfied with the results, Could you please help me out How can i get the desired results?

It would be highly appreciated if you provide me a helping material links so i can develop my understanding on Vega to define a custom visualization.

I hope ESQL will be the life changing tool for everyone.

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [April 17, 2023, 9:33pm UTC](https://discuss.elastic.co/t/can-we-extract-the-7-days-stored-common-value-from-the-elastic-search-database-and-visulazize-that-information-on-kibana/329883/6 "2023-04-17T21:33:32Z")

</div>

> [@Tanzeela](#):
>
> Hi @Andrew_Tate , hope you are doing well.

I wanted to drop a quick note to ask that you please not ping people that aren't already responding in your topic 🙂

---

<div class="post-metadata">

**Author:** ![drewdaemon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/drewdaemon/32/97779_2.png) [@drewdaemon](https://discuss.elastic.co/u/drewdaemon)\
**Post date:** [April 19, 2023, 9:01pm UTC](https://discuss.elastic.co/t/can-we-extract-the-7-days-stored-common-value-from-the-elastic-search-database-and-visulazize-that-information-on-kibana/329883/7 "2023-04-19T21:01:19Z")

</div>

Hi @Tanzeela ,

It looks like you're trying some good approaches, but I just want to emphasize that while I think it is possible, making this work with our current product is unfortunately a (very) big task.

As far as the Elasticsearch request goes, like I say above,

> [@drewdaemon](#):
>
> I'd try the [scripted metric](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-scripted-metric-aggregation.html#search-aggregations-metrics-scripted-metric-aggregation) which allows you to define custom logic for the map-reduce phases. My guess is that you can use this to gather only the devices that are in common for all the documents.

I don't think that our available aggregations can do what you're trying to accomplish, so as far as I can tell you'd have to write your own scripted metric aggregation. Using this, you define logic for the various phases of aggregation yourself using one of our scripting languages such as [Painless](https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting-painless.html).

> [@Tanzeela](#):
>
> It would be highly appreciated if you provide me a helping material links so i can develop my understanding on Vega to define a custom visualization.

Sure, the [Vega website](https://vega.github.io/vega-lite/) is a good place to start. You can find tutorials, docs, and examples there.

> [@Tanzeela](#):
>
> I hope ESQL will be the life changing tool for everyone.

Oh, it will be! 😃

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 17, 2023, 9:01pm UTC](https://discuss.elastic.co/t/can-we-extract-the-7-days-stored-common-value-from-the-elastic-search-database-and-visulazize-that-information-on-kibana/329883/8 "2023-05-17T21:01:29Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.