Aggregate multiple records to single chart


#7

Tnx @LeeDr
What I'm trying is to create is the next script (using Dev Tools) :

GET kpi/Defect/_search
{
"aggs": {
"NAME": {
"AGG_TYPE": {}
}
}
}

I want to create this script to aggregate data for the base object and it's derived (by key). With that, I want create my scripted field.
What do you mean by individual documents ?


#8

Hi @LeeDr
If it's not possible via scripted fields, are there other solutions?
Does value count aggregation can be efficient in this case?


(Lee Drengenberg) #9

Hi Avivc,
I'm sorry but I don't think I can help without some kind of example data we can talk about. Can you post some code we could paste into the dev console to create a few documents that would represent your data to use as an example?

Thanks,
Lee


#10

Hi @LeeDr
I have a field in my index pattern named : Project._refObjectName
I wish to aggregate various values refer to this field

E.g.:
Values name: field1, field2, field3

I want to aggregate this values like described here
Something like this:

POST /myIndex/_search?size=0
{
"aggs" : {
"grades_count" : {
"value_count" : {
"script" : {
"params" : {
"field" : "type"
}
}
}
}
}
}

I'm not sure how to set the aggregation I wish to have using this script

Edit:
If I do the simple query:

GET myIndex/_search
{
"query": {
"match": {
"Project._refObjectName": {
"query": "myValue"
}
}
}
}

I get the total count for this specific value. All I want it to aggregate multiple values (or using wlid card if possible) and fetch the total count

Is it enough data to move on ? is this code is sufficient?

10x a lot !


(Lee Drengenberg) #11

Hi Avivc,

In the Fields aggregation or field formatter? link I posted above I included commands to put a couple of documents using the dev console, and then I used those documents to answer a question. Can you create a couple of sample documents like that and post it here and then what you're trying to aggregate out of that? I can't tell what you're data looks like from your searches.

Thanks,
Lee


#12

Hi @LeeDr

I'm trying too - but regarding to your link, which is great and very helpful, I need to use PUT for specific field in my documents and not new fields
This field is under a specific type. So when I'm trying to do:

PUT myIndex/myType/1
{
"Project._refObjectName" : "baseObejct/ derivedObject"
}

I'm not getting what I wish for.
Maybe I should add another key:value pair/s to my query?


(Lee Drengenberg) #13

I used PUT in my examples but you don't have to.

POST can be used to achieve auto-generation of ids whereas a PUT is used when you want to specify an id.


#14

Hi @LeeDr
I want to simplify it - just taking couple of values for existing key and create scripted field that'll aggregate it to single Object so I can store it in single bar chart. Not related to nested objects

I've tried to follow the steps from your example but no success.

Tnx


#15

@LeeDr any idea please ?
tnx :thumbsup:


(Lee Drengenberg) #16

Hi Avivc,

I'm sorry but I still don't understand the data in your docs. I suggested you provide sample data but your single PUT myIndex/myType/1 isn't enough for me to understand your problem.

Can you please provide a couple of sample docs and then explain which field you're trying to aggregate?

Thanks,
Lee


#17

Hi @LeeDr
The next is part of my json input data that's relevant for my aggregation:

"Project": {
"_type": "Project",
"_rallyAPIMinor": "0",
"_rallyAPIMajor": "2",
"_refObjectName": "Alt-st-aviv avivScrum",
"_ref": "https://data",
"_refObjectUUID": "uniqueGuid"
},

The relevant field is Project._refObjectName
I want to take couple of values for this field into single scripted field

In the Discover I use to do:

Project._refObjectName: ("value1" OR "value2")

But, as the above image display, it gives me more than 1 bar, which I don't want in some cases

Is this the data you need?
Tnx a lot :thumbsup:


(Lee Drengenberg) #18

I think you're saying that the _refObjName field can have some set of values and you want to create a scripted field that combines some of those. So maybe you can create a scripted field like this;
(I just copied this from another post and changed the field name)

NOTE: I broke it into multiple lines to make it easier to read, but it might have to all be on one line for the scripted field.

if (doc["_refObjName"].value == 'Windows Server' || 
    doc["_refObjName"].value == 'Linux Server' || 
    doc["_refObjName"].value == 'PCI-Devices')  {
   return "OS";
} else if (doc["_refObjName"].value == 'Palo Alto Firewall Group' || 
    doc["_refObjName"].value == 'ASA Firewall') {
    return "Firewall";
}

When creating a new scripted field, I would always start with a very simple case and make sure it works in Discover first. And then go back and add more to it. So maybe something like this to start with;

if (doc["_refObjName"].value == 'Alt-st-aviv avivScrum') {
   return "matches";
} else {
   return "no match";
}

#19

Tnx @LeeDr
When I'm creating a very slim scripted field with 1 item (for testing in Discover), I'm getting the next error:
"type":"illegal_argument_exception","reason":"Fielddata is disabled on text fields by default. Set fielddata=true on [Project._refObjectName] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead."

I can see that this field is analyze field (text) in Kibana's management
I found this tutorial, but here too, when trying to modify the field like this:

PUT my_index/_mapping/Defect
{
"Defect": {
"properties": {
"Project._refObjectName": {
"type": "text",
"fielddata": true
}
}
}
}

I'm getting the next error:
"type": "illegal_argument_exception", "reason": "Mapper for [Project._refObjectName] conflicts with existing mapping in other types:\n[mapper [Project._refObjectName] is used by multiple types. Set update_all_types to true to update [fielddata] across all types.]"

Should I add update_all_types == true ? If so, where? or I'm not in the right direction for solution ?

10x in advance :thumbsup:


(Lee Drengenberg) #20

Are you setting the my_index mapping after docs have been indexed already? If so, you might have to delete the docs, set the mapping, and then load the docs again.

In the console, you can just do DELETE my_index. This is assuming this is test data that you can reload.

Regards,
Lee


#21

Hi @LeeDr
Yes I'm trying to set my_index mapping docs after it indexed.
Regarding the deletion - if I remove this docs, how can I map data that is not exist?

There's no problem to delete it, I can restore it easliy

10x in advance


#22

Hi @LeeDr
Any idea?
Tnx


(Lee Drengenberg) #23

Hi Avivc,

You still haven't posted example data such that I can understand your question, so I'm making some up. In the Dev Console I did this;

POST my_index/defect/
{
      "Project._refObjectName": "value1"
}

POST my_index/defect/
{
      "Project._refObjectName": "value2"
}

POST my_index/defect/
{
      "Project._refObjectName": "value3"
}

POST my_index/defect/
{
      "Project._refObjectName": "value4"
}

POST my_index/defect/
{
      "Project._refObjectName": "value5"
}

POST my_index/defect/
{
      "Project._refObjectName": "value1"
}

POST my_index/defect/
{
      "Project._refObjectName": "value2"
}

Then I made this scripted field named oneOrTwo;

if (doc['Project._refObjectName.keyword'].value == 'value1' || doc['Project._refObjectName.keyword'].value == 'value2') {
return "value1or2";
} else {
return doc['Project._refObjectName.keyword'].value
}

And then I made this visualization which shows a terms aggregation on that scripted field. Since there are 2 docs with value1 and 2 docs with value2 the chart shows a count of 4 docs with the value of value1or2;

I can also create the same chart without using the scripted field by using a Filter aggregation (the first filter is Project._refObjectName.keyword=value1 OR Project._refObjectName.keyword=value2


Charting an IP flow based metric
#24

Hi @LeeDr

I've tried that before - it causing an error that related to unsupported files
But the 2nd option works great ! I think for now it's good enough for me.
I'll share some code and the error I'm getting for other developers too

Thank you very much :v::ok_hand:

BTW: using filtering in visualize exist from which version ?


(Lee Drengenberg) #25

I see the Filter aggregation being available as far back as 4.6.5 version of Kibana, probably earlier than that.


(system) #26

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