How to get sum value for fields based on input field in elasticsearch (input field and sum output fields are different)

Question

This is document present in elastic search and wants to output based fields in which it returns the sum of the high and medium and which be greater than zero, the value of high and medium must be greater than > 0

         {
            "host_id": 1,
            "hostname": "Hostname1",
            "businesshierarchy": {
                "businessunit": "NON Unit",
                "Location":"Un",
                "Application":"App1"
            },
            "updatedts": 1601894092,
            "critical": 0,
            "high": 1,
            "medium": 1,
            "low": 0
        },
        {
            "host_id": 2,
            "hostname": "Hostname2",
            "businesshierarchy": {
                "businessunit": "One Unit",
                "Location":"Un",
                "Application":"App2"
            },
            "updatedts": 1601894092,
            "critical": 0,
            "high": 1,
            "medium": 2,
            "low": 0
        },
        {
            "host_id": 3,
            "hostname": "Hostname3",
            "businesshierarchy": {
                "businessunit": "NON Unit",
                "Location":"Uk",
                "Application":"App2"
            },
            "updatedts": 1601894092,
            "critical": 0,
            "high": 2,
            "medium": 2,
            "low": 0
        }

Is there are any query or method to get output like in elastic search?

based on location

Location - Un High - 2 medium - 3

Location - Uk High - 2 medium - 2
Based on application

Application - App1 High - 1 medium - 1

Application - App2 High - 3 medium - 4
or based on hostname

hostname - Hostname1 High - 1 medium - 1

hostname - Hostname2 High - 1 medium - 2

hostname - Hostname3 High - 2 medium - 2

Similarly for businessunit. The field name passed dynamically like businessunit, hostname, application, location-based on it want to get count High and medium value like the above output.

did you get a chance to go through my answer, looking forward to get feedback from you :) — Bhavya
– Bhavya, Commented Oct 6, 2020 at 4:34
yeah, it works fine!! my mapping is different just need to add a keyword for "field": "businesshierarchy.Location.keyword " — ROHITH P
– ROHITH P, Commented Oct 6, 2020 at 4:55
glad it worked for you. Can you please accept and upvote my answer as well as it worked for you 🙂 — Bhavya
– Bhavya, Commented Oct 6, 2020 at 5:01

Bhavya · Accepted Answer · 2020-10-06 03:15:27Z

Adding a working example with index mapping, index data(same as that given in question), search query, and search result

Index Mapping:

{
  "mappings": {
    "properties": {
      "hostname": {
        "type": "keyword"
      },
      "businesshierarchy": {
        "properties": {
          "Location": {
            "type": "keyword"
          },
          "Application": {
            "type": "keyword"
          }
        }
      }
    }
  }
}

Search Query:

{
  "size": 0,
  "aggs": {
    "user": {
      "terms": {
        "field": "businesshierarchy.Location"
      },
      "aggs": {
        "top_user_hits": {
          "top_hits": {
            "_source": {
              "includes": [
                "high",
                "medium"
              ]
            }
          }
        },
        "high_sum": {
          "sum": {
            "field": "high"
          }
        },
        "medium_sum": {
          "sum": {
            "field": "medium"
          }
        }
      }
    }
  }
}

Search Result:

Based on the location

"aggregations": {
    "user": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "Un",
          "doc_count": 2,
          "top_user_hits": {
            "hits": {
              "total": {
                "value": 2,
                "relation": "eq"
              },
              "max_score": 1.0,
              "hits": [
                {
                  "_index": "stof_64218649",
                  "_type": "_doc",
                  "_id": "1",
                  "_score": 1.0,
                  "_source": {
                    "high": 1,
                    "medium": 1
                  }
                },
                {
                  "_index": "stof_64218649",
                  "_type": "_doc",
                  "_id": "2",
                  "_score": 1.0,
                  "_source": {
                    "high": 1,
                    "medium": 2
                  }
                }
              ]
            }
          },
          "high_sum": {
            "value": 2.0       <-- note this
          },
          "medium_sum": {
            "value": 3.0
          }
        },
        {
          "key": "Uk",
          "doc_count": 1,
          "top_user_hits": {
            "hits": {
              "total": {
                "value": 1,
                "relation": "eq"
              },
              "max_score": 1.0,
              "hits": [
                {
                  "_index": "stof_64218649",
                  "_type": "_doc",
                  "_id": "3",
                  "_score": 1.0,
                  "_source": {
                    "high": 2,
                    "medium": 2
                  }
                }
              ]
            }
          },
          "high_sum": {
            "value": 2.0                       <-- note this
          },
          "medium_sum": {
            "value": 2.0
          }
        }
      ]
    }

For querying on the basis of application replace terms aggregation like this:

"aggs": {
        "user": {
          "terms": {
            "field": "businesshierarchy.Application"
          },

The following search result will be there:

 "aggregations": {
    "user": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "App2",
          "doc_count": 2,
          "top_user_hits": {
            "hits": {
              "total": {
                "value": 2,
                "relation": "eq"
              },
              "max_score": 1.0,
              "hits": [
                {
                  "_index": "stof_64218649",
                  "_type": "_doc",
                  "_id": "3",
                  "_score": 1.0,
                  "_source": {
                    "high": 2,
                    "medium": 2
                  }
                },
                {
                  "_index": "stof_64218649",
                  "_type": "_doc",
                  "_id": "2",
                  "_score": 1.0,
                  "_source": {
                    "high": 1,
                    "medium": 2
                  }
                }
              ]
            }
          },
          "high_sum": {
            "value": 3.0
          },
          "medium_sum": {
            "value": 4.0
          }
        },
        {
          "key": "App1",
          "doc_count": 1,
          "top_user_hits": {
            "hits": {
              "total": {
                "value": 1,
                "relation": "eq"
              },
              "max_score": 1.0,
              "hits": [
                {
                  "_index": "stof_64218649",
                  "_type": "_doc",
                  "_id": "1",
                  "_score": 1.0,
                  "_source": {
                    "high": 1,
                    "medium": 1
                  }
                }
              ]
            }
          },
          "high_sum": {
            "value": 1.0
          },
          "medium_sum": {
            "value": 1.0
          }
        }
      ]
    }

For querying on the basis of hostname replace terms aggregation like this:

"aggs": {
    "user": {
      "terms": {
        "field": "hostname"
      },

Search Results will be :

"aggregations": {
    "user": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "Hostname1",
          "doc_count": 1,
          "top_user_hits": {
            "hits": {
              "total": {
                "value": 1,
                "relation": "eq"
              },
              "max_score": 1.0,
              "hits": [
                {
                  "_index": "stof_64218649",
                  "_type": "_doc",
                  "_id": "1",
                  "_score": 1.0,
                  "_source": {
                    "high": 1,
                    "medium": 1
                  }
                }
              ]
            }
          },
          "high_sum": {
            "value": 1.0
          },
          "medium_sum": {
            "value": 1.0
          }
        },
        {
          "key": "Hostname2",
          "doc_count": 1,
          "top_user_hits": {
            "hits": {
              "total": {
                "value": 1,
                "relation": "eq"
              },
              "max_score": 1.0,
              "hits": [
                {
                  "_index": "stof_64218649",
                  "_type": "_doc",
                  "_id": "2",
                  "_score": 1.0,
                  "_source": {
                    "high": 1,
                    "medium": 2
                  }
                }
              ]
            }
          },
          "high_sum": {
            "value": 1.0
          },
          "medium_sum": {
            "value": 2.0
          }
        },
        {
          "key": "Hostname3",
          "doc_count": 1,
          "top_user_hits": {
            "hits": {
              "total": {
                "value": 1,
                "relation": "eq"
              },
              "max_score": 1.0,
              "hits": [
                {
                  "_index": "stof_64218649",
                  "_type": "_doc",
                  "_id": "3",
                  "_score": 1.0,
                  "_source": {
                    "high": 2,
                    "medium": 2
                  }
                }
              ]
            }
          },
          "high_sum": {
            "value": 2.0
          },
          "medium_sum": {
            "value": 2.0
          }
        }
      ]
    }

ROHITH P · Accepted Answer · 2020-10-06 05:30:35Z

we can use this query to get the excepted result

  {
          "query": {
            "bool": {
              "filter": [
                {
                  "bool": {
                    "should": [
                      {
                        "range": {
                          "medium": {
                            "gt": 0
                          }
                        }
                      },
                      {
                        "range": {
                          "high": {
                            "gt": 0
                          }
                        }
                      }
                    ]
                  }
                }
              ]
            }
          },
          "aggs": {
            "fieldnames": {
              "terms": {
                "field": "hostname.keyword"
              },
              "aggs": {
                "medium": {
                  "sum": {
                    "field": "medium"
                  }
                },
                "high": {
                  "sum": {
                    "field": "high"
                  }
                }
              }
            }
          },
          "size": 0
        }

Search result for this look like this

"aggregations": {
        "fieldnames": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": "ALL Unit",
                    "doc_count": 1,
                    "high": {
                        "value": 0.0
                    },
                    "medium": {
                        "value": 7.0
                    }
                },
                {
                    "key": "Latest Unit",
                    "doc_count": 1,
                    "high": {
                        "value": 0.0
                    },
                    "medium": {
                        "value": 5.0
                    }
                },
                {
                    "key": "NO Unit",
                    "doc_count": 1,
                    "high": {
                        "value": 1.0
                    },
                    "medium": {
                        "value": 1.0
                    }
                }
            ]
        }
    }

In case if we need the result for location and application, just need to change for Location

"aggs": {
                "fieldnames": {
                  "terms": {
                    "field": "businesshierarchy.Application.keyword"
                  }

for Application

"aggs": {
                    "fieldnames": {
                      "terms": {
                        "field": "businesshierarchy.Location.keyword"
                      }

if the mapping is something like this,

{
  "mappings": {
    "properties": {
      "hostname": {
        "type": "keyword"
      },
      "businesshierarchy": {
        "properties": {
          "Location": {
            "type": "keyword"
          },
          "Application": {
            "type": "keyword"
          }
        }
      }
    }
  }
}

There is no need for adding .keyword to

"terms": {
             "field": "businesshierarchy.Location"
           }

Collectives™ on Stack Overflow

How to get sum value for fields based on input field in elasticsearch (input field and sum output fields are different)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related