# Aggregate with DevTool!

**URL:** <https://discuss.elastic.co/t/aggregate-with-devtool/99705>\
**Category:** Kibana\
**Created:** [September 7, 2017, 9:51am UTC](https://discuss.elastic.co/t/aggregate-with-devtool/99705 "2017-09-07T09:51:50Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Beuhlet\_Reseau](https://avatars.discourse-cdn.com/v4/letter/b/e95f7d/32.png) [@Beuhlet\_Reseau](https://discuss.elastic.co/u/Beuhlet_Reseau)\
**Post date:** [September 7, 2017, 9:51am UTC](https://discuss.elastic.co/t/aggregate-with-devtool/99705/1 "2017-09-07T09:51:50Z")

</div>

Hello,

I search to put aggregat on my data.

Here a party of my data :

```
TYPE Usage
FREEUSER 345
PREMIUM_USER 8653
FREEUSER 1369
FREEUSER 87654
PREMIUM_USER 43678
FREEUSER 8654
PREMIUM_USER 2387
FREEUSER 98723
FREEUSER 45873
PREMIUM_USER 2847
PREMIUM_USER 89235
USER_UNKNOW 16235
USER_GOLD 32457

```

**My aim is :**

To **sum Usage by type of client** , example :

For no gold user, i want know the sum of usage of other user on the same graph ! :

So, USER\_PREMIUM & FREEUSER, USER\_UNKNOW have use a total of 405 653 octets today.

Currentl, in kibana when i test this, i have the sum of usage by type of user 😕 so I have 3 curves on the same graph. But i want just One with cumulutavie sum of 3 specific type of client.

I thought this :

```
{
“query” : {
“constant_score” : {
“filter” : {
“match” : { “TYPE” : “PREMIUMUSER or FREEUSER or UNKNOW_USER” }
}
}
},
“aggs” : {
“sum_no_gold_user” : { “sum” : { “field” : “USAGE” } }
}
}

```

What do you think friends ?

---

<div class="post-metadata">

**Author:** ![Stacey\_Gammon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stacey_gammon/32/14025_2.png) [@Stacey\_Gammon](https://discuss.elastic.co/u/Stacey_Gammon)\
**Post date:** [September 7, 2017, 2:20pm UTC](https://discuss.elastic.co/t/aggregate-with-devtool/99705/2 "2017-09-07T14:20:44Z")

</div>

I'm not exactly sure what type of graph you are looking for, but you should be able to do this with a scripted painless field.

 ![44 AM](https://us1.discourse-cdn.com/elastic/original/3X/f/0/f068d89d03b54ebc23934c13529cfde7e2e4cf1b.png)

My example is using a bytes value per ip address, but yours would be a usage value per type.

 ![21 AM](https://us1.discourse-cdn.com/elastic/original/3X/4/b/4b18a6670d74220afceab960e125e121ba63a937.png)

Once you have the scripted field you can use it in visualizations to compare the total sum vs the sum per `no_gold_user`.

In my example, the lines match up except for two timestamps, this is because the ip I selected only has values for those two times:

 ![27 AM](https://us1.discourse-cdn.com/elastic/original/3X/4/8/48fe40ec160c1eeaf36b28506091a006b9eec0d5.png)

Hope this helps.

---

<div class="post-metadata">

**Author:** ![Beuhlet\_Reseau](https://avatars.discourse-cdn.com/v4/letter/b/e95f7d/32.png) [@Beuhlet\_Reseau](https://discuss.elastic.co/u/Beuhlet_Reseau)\
**Post date:** [September 7, 2017, 2:45pm UTC](https://discuss.elastic.co/t/aggregate-with-devtool/99705/3 "2017-09-07T14:45:32Z")

</div>

@Stacey_Gammon If i take 2 type of client and i make sum, here the graph ... :

 ![courbe](https://us1.discourse-cdn.com/elastic/original/3X/e/b/eb9e6657c586edf907c3a8b73edad99478ad58d0.JPG)

But I only want one, depending on the type of users I choose.

I don't understand your post, what is better use method that i post previously :

{  
“query” : {  
“constant\_score” : {  
“filter” : {  
“match” : { “TYPE” : “PREMIUMUSER or FREEUSER or UNKNOW\_USER” }  
}  
}  
},  
“aggs” : {  
“sum\_no\_gold\_user” : { “sum” : { “field” : “USAGE” } }  
}  
}

or you painless scripted field ? your solution seems to be a bit deprecated no ?

Else, if i take your mistery solution this would give :

```
if (doc['TYPE'].value = 'PREMIUMUSER' , '23953', '962', 'FREEUSER', 'UNKNOW_USER' {
return doc['USAGE'].value
}
return 0
```

---

<div class="post-metadata">

**Author:** ![Stacey\_Gammon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stacey_gammon/32/14025_2.png) [@Stacey\_Gammon](https://discuss.elastic.co/u/Stacey_Gammon)\
**Post date:** [September 7, 2017, 3:26pm UTC](https://discuss.elastic.co/t/aggregate-with-devtool/99705/4 "2017-09-07T15:26:48Z")

</div>

Painless fields are not deprecated though they do come with some performance issues.

If you use painless fields, yours would look like this:

```auto
if (doc['TYPE'].value != 'USER_GOLD') {
 return doc['USAGE'].value
}
return 0

```

It's less to write to do a single "not equals" then OR'ing all the types you do want, though you can do it that way too:

```auto
if (doc['TYPE'].value == 'PREMIUM_USER' || doc['TYPE'].value == 'FREEUSER' || doc['TYPE'].value == 'USER_UNKNOWN' ) {
 return doc['USAGE'].value
}
return 0

```

As for your proposed method - where are you putting that JSON?

> But I only want one, depending on the type of users I choose.

What do you want only one of? One bar on that graph instead of two? If you are simply looking for a single number, the total sum over your whole time range, you might want to look into a metric visualization.

 ![27 AM](https://us1.discourse-cdn.com/elastic/original/3X/8/b/8b128ae74f485384f2f0047b8bd9fc2120603796.png)

If you want to do this dynamically (e.g. easily change the sum of the types you are looking for) you can just use a filter. Perhaps that is where you were putting your JSON above. Your filter JSON could look either like this:

```auto
{
  "query": {
    "bool": {
      "should": [
        {
          "match_phrase": {
            "ip": "1.104.179.62"
          }
        },
        {
          "match_phrase": {
            "ip": "0.137.97.198"
          }
        }
      ],
      "minimum_should_match": 1
    }
  }
}

```

for the "or" version, or for the "not" version:

```auto
{
  "query": {
    "match": {
      "ip": {
        "query": "10.92.69.153",
        "type": "phrase"
      }
    }
  }
}

```

 ![54 AM](https://us1.discourse-cdn.com/elastic/original/3X/6/8/682f31fb548d6ba00a9495d2e86b6ab6e431fde5.png)

---

<div class="post-metadata">

**Author:** ![Beuhlet\_Reseau](https://avatars.discourse-cdn.com/v4/letter/b/e95f7d/32.png) [@Beuhlet\_Reseau](https://discuss.elastic.co/u/Beuhlet_Reseau)\
**Post date:** [September 7, 2017, 4:05pm UTC](https://discuss.elastic.co/t/aggregate-with-devtool/99705/5 "2017-09-07T16:05:00Z")

</div>

Oh excuse moi ... i want this type of graph ;

Filter by type of user, here is the normal behavior :

 ![captyu](https://us1.discourse-cdn.com/elastic/original/3X/f/3/f357960b59ae452ac2346bfb1d03d99d63094b45.JPG)

But if I want to focus on a particular type of few users, how can I do it?

=\> Thank you for

> [@Stacey\_Gammon](#):
>
> if (doc['TYPE'].value == 'PREMIUM\_USER' || doc['TYPE'].value == 'FREEUSER' || doc['TYPE'].value == 'USER\_UNKNOWN' ) {  
> return doc['USAGE'].value  
> }  
> return 0

**With your experience, what is the best method?** Painless or dynamically with DevTool

Other things, I have a _120 000 000 messages by day_. **I want make a graph on a month** , but impossible because there are _3 480 000 000 messages_ and i think that kibana is not robust enough (some timeout). [it's just a parenthesis]

---

<div class="post-metadata">

**Author:** ![Stacey\_Gammon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stacey_gammon/32/14025_2.png) [@Stacey\_Gammon](https://discuss.elastic.co/u/Stacey_Gammon)\
**Post date:** [September 7, 2017, 5:06pm UTC](https://discuss.elastic.co/t/aggregate-with-devtool/99705/6 "2017-09-07T17:06:34Z")

</div>

> [@Beuhlet\_Reseau](#):
>
> But if I want to focus on a particular type of few users, how can I do it?

Is the filter method shown above not sufficient?

> With your experience, what is the best method? Painless or dynamically with DevTool

Can you explain a bit more what you mean by `dynamically with devtools`? I don't see how devtools would help you when creating a visualization.

> I want make a graph on a month,

You can create month intervals if that is what you are looking for. The calculations are done in Elasticsearch, so Kibana won't actually be handling 3 billion messages, it will just get back the aggregates from elasticsearch. If Elasticsearch can't handle the query (you can test this via dev tools), there are ways to improve performance. If that is the case, I encourage you to ask in the Elasticsearch room to get some more details on how to improve your setup if it's hanging.

 ![51 PM](https://us1.discourse-cdn.com/elastic/original/3X/e/2/e2d9099c102e0497403a98101d80dba789021845.png)

---

<div class="post-metadata">

**Author:** ![Beuhlet\_Reseau](https://avatars.discourse-cdn.com/v4/letter/b/e95f7d/32.png) [@Beuhlet\_Reseau](https://discuss.elastic.co/u/Beuhlet_Reseau)\
**Post date:** [September 8, 2017, 10:16am UTC](https://discuss.elastic.co/t/aggregate-with-devtool/99705/7 "2017-09-08T10:16:50Z")

</div>

I understand @Stacey_Gammon with devtool, i can obtain the result but not a field which can be used to create graph...

I wanted to say, filter JSON or painless field better ?

As a remember, i have disable \_all field in mapping of elasticsearch and message field in Logstash.

So to create a js filter i can make that (but i can't exploit a field with ot no ?):

```
{
  "query": {
    "bool": {
      "should": [
        {
          "match_phrase": {
            "TYPE": "PREMIUM_USER"
          }
        },
        {
          "match_phrase": {
            "TYPE": "UNKNOW_USER"
          }
        },
        {
          "match_phrase": {
            "TYPE": "FREEUSER"
          }
        }
      ],
      "minimum_should_match": 1
    }
  }

```

If FREEUSER is a party of TYPE, i can use " \*FREEUSER \* " no ?

But it will just filter and in the end I would always have 3 curves on my graph no ?

To have the total sum on a single curve i must use painless scripted field no ?

---

<div class="post-metadata">

**Author:** ![Beuhlet\_Reseau](https://avatars.discourse-cdn.com/v4/letter/b/e95f7d/32.png) [@Beuhlet\_Reseau](https://discuss.elastic.co/u/Beuhlet_Reseau)\
**Post date:** [September 11, 2017, 12:22pm UTC](https://discuss.elastic.co/t/aggregate-with-devtool/99705/8 "2017-09-11T12:22:03Z")

</div>

if (doc['TYPE'].value == 'PREMIUM\_USER' || doc['TYPE'].value == 'FREEUSER' || doc['TYPE'].value == 'USER\_UNKNOWN' ) {  
return doc['USAGE'].value  
}  
return 0

It's doesn't work.

---

<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:** [October 9, 2017, 12:22pm UTC](https://discuss.elastic.co/t/aggregate-with-devtool/99705/9 "2017-10-09T12:22:16Z")

</div>

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