Not able to compare two loops in a chain query

Elastic Stack Elasticsearch
1

Hi i am trying to achieve below
I have two different index and i want to compare two fields values using loop and get result if any of the IP address value matches

for example :-
Index 1: threatintel
fields :- 1. indicator_ip
2. message
3. userid

Index 2: checkpoint
fields:- 1. srcip
2. dstip

using below watch condition i am only able to check only first value, how to use a loop to check all values

 "condition": {
    "compare": {
      "ctx.payload.first.hits.hits.0._source.srcip": {
        "eq": "{{ctx.payload.second.hits.hits.0._source.indicator-ip}}"
      }
    }
  },

@richcollier Please help

2

First Chain Query

{
      "watch": 
      {
      "trigger": {
        "schedule": {
          "interval": "1m"
        }
      },
      "input": {
        "chain": {
          "inputs": [
            {
              "first": {
                "search": {
                  "request": {
                    "search_type": "query_then_fetch",
                    "indices": [
                      "logstash-checkpoint*"
                    ],
                    "types": [],
                    "body": {
                        "aggs": {
                            "time": {
                              "terms": {
                                "field": "@timestamp",
                                "order": {
                                  "_count": "desc"
                                }
                              },
                              "aggs": {
                                "srcip": {
                                  "terms": {
                                    "field": "srcip.keyword",
                                    "order": {
                                      "_count": "desc"
                                    }
                                  },
                                  "aggs": {
                                    "dstip": {
                                      "terms": {
                                        "field": "dstip.keyword",
                                          "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"
                                  }
                                },
                                {
                                  "range": {
                                    "@timestamp": {
                                        "gte": "{{ctx.trigger.scheduled_time}}||-1d",
                                        "lte": "{{ctx.trigger.scheduled_time}}",
                                        "format": "strict_date_optional_time||epoch_millis"
                                      }
                                  }
                                }
                              ],
                              "filter": [],
                              "should": [],
                              "must_not": []
                            }
                          }
                    }
                  }
                }
              }
            },
3 
Second Chain Query  
  {
              "second": {
                "search": {
                  "request": {
                    "search_type": "query_then_fetch",
                    "indices": [
                      "logstash-security-*"
                    ],
                    "types": [],
                    "body": {
                        "aggs": {
                            "time": {
                              "terms": {
                                "field": "@timestamp",
                                "size": 5,
                                "order": {
                                  "_count": "desc"
                                }
                              },
                              "aggs": {
                                "indicator": {
                                  "terms": {
                                    "field": "indicator.keyword",
                                    "size": 5,
                                    "order": {
                                      "_count": "desc"
                                    }
                                  }
                                }
                              }
                            }
                          },
                          "version": true,
                          "_source": {
                            "excludes": []
                          },
                          "stored_fields": [
                            "*"
                          ],
                          "script_fields": {},
                          "docvalue_fields": [
                            "@timestamp",
                            "security_log.time"
                          ],
                          "query": {
                            "bool": {
                              "must": [
                                {
                                  "match_all": {}
                                },
                                {
                                  "match_all": {}
                                },
                                {
                                  "bool": {
                                    "should": [
                                      {
                                        "match_phrase": {
                                          "type.keyword": "threatintel"
                                        }
                                      }
                                    ],
                                    "minimum_should_match": 1
                                  }
                                },
                                {
                                  "match_phrase": {
                                    "indicator_parent": {
                                      "query": "ip"
                                    }
                                  }
                                },
                                {
                                  "exists": {
                                    "field": "indicator"
                                  }
                                },
                                {
                                  "range": {
                                    "@timestamp": {
                                        "gte": "{{ctx.trigger.scheduled_time}}||-1d",
                                        "lte": "{{ctx.trigger.scheduled_time}}",
                                        "format": "strict_date_optional_time||epoch_millis"
                                      }
                                  }
                                }
                              ],
                              "filter": [],
                              "should": [],
                              "must_not": []
                            }
                          }
                    }
                  }
                }
              }
            }
          ]
        }
      },
      "condition": {
        "compare": {
      ?????????????
      },
      "actions": {
        "log": {
          "logging": {
            "level": "info",
            "text": "what condition should i write"
          }
        }
      }
    }
4

@jamesspi please help

5

Hi @addanuj,

I don't think it's possible to loop through hits. However, I would do this differently.

I would use the ip from the 1st input, and then build the query for the second input using that variable.

Then, the compare condition would need to be based on the result of the second input. You don't even need aggregations in this case really.

So the query for your second input would be something like:

 "body": {
                  "query": {
                    "bool": {
                      "must": [
                        {
                          "query_string": {
                            "query": "type:threatintel AND indicator-ip:{{ctx.payload.first.hits.hits.0._source.srcip}}"
                          }
                        }
                      ]
                    }
                  },

And the condition:

 "condition": {
    "compare": {
      "ctx.payload.second.hits.total": {
        "gt": 0
      }
    }
  },

Thanks,
James

6

And I should mention - you should probably be doing the comparison at the entry level (I assume you're using logstash?) using the translate filter plugin. Then just create a watch to see if there is a match. What I usually do is create a boolean field with the translate plugin, and then just watch for value=true. Much more efficient.

7

Can you provide an example for this

8

Hi James,
i tried it but it is not comparing all threatintel IP address with all srcip values, it only compare first one.
but i tried this -
"bool": {
"must": [
{
"query_string": {
"query": "indicator:{{#ctx.payload.first.aggregations.srcip.buckets}}{{key}} {{/ctx.payload.first.aggregations.srcip.buckets}}"
}
}
]
}

this works for me but i need to know how shall i print matching values only in action seciton

9

You won't need to compare all values, you just need to know when there is a match, correct?

Here is a link for some translate examples:

10

I've already recommended in a different thread that you should look at this post for ideas:

Why do you keep on asking? I'm confused...

11

Thanks James, for guiding me to the right way. i will try to use logstash method also, but your answer helped me to solve some specific problems anyway,

I am really thankful to this forum and you guys, providing great support to community.

12

No problem!

13

Using a painless script and a chained input that executes both of hte searches you mentioned in the top might be another solution.

Be aware that something like joins do not work though!