Not able to output two or multiple aggregations in a loop

Elastic Stack Elasticsearch
1

Hi all,

i am able to print values in loop but i am stuck in one condition, i have a chain query where i am able to print values from first chain using its aggregations but "I am not able to print values from Second chain and its aggregations in HTML Table"

due to word limit .. Here is the first query: and second query is in my next Reply to this post...

  {
      "trigger": {
        "schedule": {
          "interval": "10s"
        }
      },
      "input": {
        "chain": {
          "inputs": [
            {
              "first": {
                "search": {
                  "request": {
                    "search_type": "query_then_fetch",
                    "indices": [
                      "logstash-checkpoint-*"
                    ],
                    "types": [],
                    "body": {
                      "aggs": {
                        "time": {
                          "terms": {
                            "field": "@timestamp",
                            "size": 50,
                            "order": {
                              "_count": "desc"
                            }
                          },
                          "aggs": {
                            "srcip": {
                              "terms": {
                                "field": "srcip.keyword",
                                "size": 50,
                                "order": {
                                  "_count": "desc"
                                }
                              },
                              "aggs": {
                                "dstip": {
                                  "terms": {
                                    "field": "dstip.keyword",
                                    "size": 50,
                                    "order": {
                                      "_count": "desc"
                                    }
                                  }
                                }
                              }
                            }
                          }
                        }
                      },
                      "version": true,
                      "_source": {
                        "excludes": []
                      },
                      "stored_fields": [
                        "*"
                      ],
                      "script_fields": {},
                      "docvalue_fields": [
                        "@timestamp"
                      ],
                      "query": {
                        "bool": {
                          "must": [
                            {
                              "match_all": {}
                            },
                            {
                              "match_all": {}
                            },
                            {
                              "exists": {
                                "field": "srcip.keyword"
                              }
                            },
                            {
                              "exists": {
                                "field": "dstip.keyword"
                              }
                            },
                            {
                              "bool": {
                                "should": [
                                  {
                                    "match_phrase": {
                                      "threaintel_IP.keyword": "true"
                                    }
                                  }
                                ],
                                "minimum_should_match": 1
                              }
                            },
                            {
                              "range": {
                                "@timestamp": {
                                  "gte": "now-1d"
                                }
                              }
                            }
                          ],
                          "filter": [],
                          "should": [],
                          "must_not": []
                        }
                      }
                    }
                  }
                }
              }
            },
2

Anuj,

Can you please post the full output of your watch execution in Console (the entire JSON of the right hand panel)? You can redact any sensitive information.

3

Second Query and HTML table where i am facing issue----

   {
      "second": {
        "search": {
          "request": {
            "search_type": "query_then_fetch",
            "indices": [
              "logstash-security-*"
            ],
            "types": [],
            "body": {
                "aggs": {
                    "feed": {
                      "terms": {
                        "field": "feed_name.keyword",
                        "size": 50,
                        "order": {
                          "_count": "desc"
                        }
                      },
                      "aggs": {
                        "type": {
                          "terms": {
                            "field": "indicatortype.keyword",
                            "size": 50,
                            "order": {
                              "_count": "desc"
                            }
                          }
                        }
                      }
                    }
                  },
                  "version": true,
                  "_source": {
                    "excludes": []
                  },
                  "stored_fields": [
                    "*"
                  ],
                  "script_fields": {},
                  "docvalue_fields": [
                    "@timestamp",
                    "security_log.time"
                  ],
                  "query": {
                    "bool": {
                      "must": [
                        {
                          "match_all": {}
                        },
                        {
                          "match_all": {}
                        },
                        {
                          "match_phrase": {
                            "type.keyword": {
                              "query": "threatintel"
                            }
                          }
                        },
                        {
                          "exists": {
                            "field": "feed_name.keyword"
                          }
                        },
                        {
                          "exists": {
                            "field": "indicatortype.keyword"
                          }
                        },
                        {
                                          "query_string": {
                                            "query": "indicator:{{#ctx.payload.first.aggregations.time.buckets.0.srcip.buckets.0.dstip.buckets}}{{key}} {{/ctx.payload.first.aggregations.time.buckets.0.srcip.buckets.0.dstip.buckets}}"
                                          }
                                        },
                        {
                          "range": {
                            "@timestamp": {
                              "gte": "now-7d"
                            }
                          }
                        }
                      ],
                      "filter": [],
                      "should": [],
                      "must_not": []
                    }
                  }
            }
          }
        }
      }
    }
  ]
}
  },
  "condition": {
"compare": {
  "ctx.payload.second.hits.total": {
    "gt": 0
  }
}
  },
  "actions": {
"log": {
  "logging": {
    "level": "info",
    "text": "Test"
  }
},
"send_mail": {
  "email": {
    "profile": "standard",
    "to": [
      "addanuj@gmail.com"
    ],
    "subject": "Test 2 Threat Intel",
    "body": {
         "html": "<table border='2'><tr><th>Timestamp</th><th>Source IP</th><th>Indicator IP</th><th>Feed Name</th><th>Count</th></tr><tr>{{#ctx.payload.first.aggregations.time.buckets}}<td>{{key_as_string}}</td><td>{{srcip.buckets.0.key}}</td><td>{{srcip.buckets.0.dstip.buckets.0.key}}</td><td>{{#ctx.payload.second.aggregations.feed.buckets}}{{key}}{{/ctx.payload.second.aggregations.feed.buckets}}</td><td>{{doc_count}}</td></tr><tr>{{/ctx.payload.aggregations.time.buckets}}</tr></table>"
    }
  }
}
  }
}

please help me to correct it.

4

Hi Rich,

due to word limit in a post i have posted this thread again, please help.

5

I'm not 100% sure that a mustache syntax "loop" will work in the query_string like this.

@spinscale - do you know?

6

The better and more flexible way would be to use a transform between the two chained inputs, to create the search string you need in a script. See https://www.elastic.co/guide/en/elastic-stack-overview/6.3/input-chain.html#_transforming_chained_input_data

7

loop is working perfectly for me... i am not able to solve the below part...

"body": {
         "html": "<table border='2'><tr><th>Timestamp</th><th>Source IP</th><th>Indicator IP</th><th>Feed Name</th><th>Count</th></tr><tr>{{#ctx.payload.first.aggregations.time.buckets}}<td>{{key_as_string}}</td><td>{{srcip.buckets.0.key}}</td><td>{{srcip.buckets.0.dstip.buckets.0.key}}</td><td>{{#ctx.payload.second.aggregations.feed.buckets}}{{key}}{{/ctx.payload.second.aggregations.feed.buckets}}</td><td>{{doc_count}}</td></tr><tr>{{/ctx.payload.aggregations.time.buckets}}</tr></table>"
    }

it should four fields in following format: time - srcip - dstip - feed - count
here feed is from second query aggregation and from second loop.

8

can you include a sample search response here as well, that you are trying to parse?

9

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