Can a watch search over two different Indexes

[robrang558 (Rob)]

I have a watch set up to send an email anytime that the metricbeat index's field "mysql.status.connections" shows nothing over 5 minutes. It works great with the only exception that when the pipeline bottlenecks, all metricbeat data backs up causing the alert to fire falsely (false positive). I am looking at correlating the status down with a filebeat message that has a SQL error code to make my watch more robust and prevent the false positives from occurring while the infrastructure team works on the pipeline. I got the syntax correct (I do not see JSON errors) but when I try to simulate the watch or save the watch, I get the following error:

Watcher: [parse_exception] Expected closing JSON object after parsing input [search] named [mbinfo] 
in watch[MyWatch]

I have been unable to find anything that would explain what in the mbinfo is causing this. I will be looking at this in the devtools to see if I can figure it out but if someone has any ideas why this is being caused or has an example that would let me search between two indexes, it would save me a lot of time. Here is the watch that I have and I am open to suggestions as to how to do this correctly:

{
  "trigger": {
"schedule": {
  "interval": "4m"
}
  },
"input": {
  "chain": {
    "inputs": [
        {
          "mbinfo": {
                  "search": {
                      "request": {
                        "search_type": "query_then_fetch",
                        "indices": [
                          "my_metricbeat_index"
                        ],
                        "types": [],
                        "body": {
                          "size": 50,
                          "query": {
                            "bool": {
                              "filter": [
                                {
                                  "range": {
                                    "@timestamp": {
                                      "from": "now-5m",
                                      "to": "now"
                                    }
                                  }
                                }
                              ],
                              "must": [
                                {
                                  "match": {
                                    "fields.team": "my_team"
                                  }
                                },
                                {
                                  "exists": {
                                    "field": "mysql.status.connections"
                                  }
                                }
                              ]
                            }
                          },
                          "_source": {
                            "excludes": []
                          }
                        }
                      }
                    }
                },
            "fbinfo": {
                    "search": {
                        "request": {
                            "search_type": "query_then_fetch",
                            "indices": [
                              "my_filebeat_index"
                            ],
                            "types": [],
                            "body": {
                              "size": 50,
                              "query": {
                                "bool": {
                                  "filter": [
                                    {
                                      "range": {
                                        "@timestamp": {
                                          "from": "now-5m",
                                          "to": "now"
                                        }
                                      }
                                    }
                                  ],
                                  "must": [
                                    {
                                      "match": {
                                        "fields.team": "my_team"
                                      }
                                    },
                                    {
                                      "match_phrase": {
                                        "message": "SQLSTATE = 08s01"
                                      }
                                    }
                                  ]
                                }
                              },
                              "_source": {
                                "excludes": []
                              }
                            }
                        }
                    }
                }
            }
        ]
    }
},
  "condition": {
"compare": {
  "ctx.payload.hits.total": {
    "lt": 1
  }
}
  },

Thank you for your time and assistance with regards to this and the action is just to send an email.

[robrang558 (Rob)]

I was able to fix the JSON error by enclosing both the mbinfo and fbinfo in {}. My question now is how can I get this logic to work? Right now, I am looking for one field that exists (and it working would need to be lt:1) and another field that the exact term is gt:1 (both have to match).

[spinscale (Alexander Reelsen)]

if you include the response data and explicitely mention what fields you are after, it might be easier to follow the problem, as it is rather data specific your problem.

In general, you can use the script condition to check for two different things by doing something like this (not tailored to your problem, just the concept using the && operator)

"condition" : {
  "script" : "return ctx.payload.mbinfo.hits.total > 0 && ctx.payload.search.hits.total < 200"
}

[robrang558 (Rob)]

Hi and thanks for the response. Your response actually answered a question that I was going to look into for the condition (if a script can be used to alert if the mbinfo.hits.total is lt 1 and the fbinfo.hits.total is gt 1). I will start looking at this and will reply if it works for me or not. All that this watch is set to alert on is if the connection to the mysql db has been lost (metricbeat field mysql.status.connections). I have a watch working just looking at the metricbeat index but when data ingestion bottlenecks, I get false positives as the field that I am looking for does not have data over the timeframe given. I did see a sql execption in filebeat index at the same time that the database does go down and believe that looking for both the lost connection and the sql exception will keep false positives from occurring. Thanks again and I will update this with a works or doesnt work by the end of next week.

[robrang558 (Rob)]

Here is the condition that I came up with:

  "condition": {
    "script": {
      "source": "return ctx.payload.mbinfo.hits.total < 1 && ctx.payload.fbinfo.hits.total > 1",
      "lang": "painless"
    }
  },

the logic seems to work to me and I did have some data to test it with back in May but the infrastructure team may have finally set the data retention setting as I am no longer able to see that data. I will circle around with the infrastructure team and see what can be done from there to test this.

Thanks,
Robert Rangel

[robrang558 (Rob)]

To update everyone, the above condition worked as I planned. I would get email alerts whenever the mysql connections was less than 1 and the sql code in filebeat was greater than 1.