Watcher alert error

Elastic Stack Kibana
1

Hello,

I want to calculate the ratio of orders/searches per customer number in my erp and send an alert with that info.
So i have created an advance alert with the following code:

{
  "trigger": {
    "schedule": {
      "cron": "0 0/2 * 1/1 * ? *"
    }
  },
  "input": {
    "search": {
      "request": {
        "search_type": "query_then_fetch",
        "indices": [
          "connect-*"
        ],
        "rest_total_hits_as_int": true,
        "body": {
          "size": 0,
          "aggs": {
            "convratepercustomer_account": {
              "terms": {
                "field": "customer_nr"
              },
              "aggs": {
                "total_orders": {
                  "filter": {
                    "term": {
                      "order_type": "ORDER"
                    }
                  }
                }
              },
              "total_searches": {
                "filter": {
                  "term": {
                    "must": [
                      {
                        "terms": {
                          "event_type": [
                            "fulltextsearch",
                            "fahrzeugsuche",
                            "teilesuche"
                          ]
                        }
                      }
                    ]
                  }
                }
              },
              "bucket_script": {
                "buckets_path": {
                  "totalorders": "total_orders",
                  "totalsearches": "total_searches"
                },
                "script": "params.totalorders / params.totalsearches * 100"
              }
            }
          }
        }
      }
    }
  },
  "condition": {
    "always": {}
  },
  "actions": {
    "send_email": {
      "email": {
        "profile": "standard",
        "to": [
          "alexandros.ananikidis@sag-ag.ch"
        ],
        "subject": "{{#ctx.payload.aggregations.convratepercustomer_account}}",
        "body": {
          "text": "ole"
        }
      }
    }
  }
}

but the error response that i get is the following:

{
  "_id" : "conversion_report_07c4c121-90c9-45ac-a6ed-d06332252db2-2020-07-15T14:12:01.04247Z",
  "watch_record" : {
    "watch_id" : "conversion_report",
    "node" : "IQhDHLCVRXCAKsejaLyT-w",
    "state" : "failed",
    "user" : "elastic",
    "status" : {
      "state" : {
        "active" : false,
        "timestamp" : "2020-07-15T14:11:56.357Z"
      },
      "actions" : {
        "send_email" : {
          "ack" : {
            "timestamp" : "2020-07-15T14:11:56.357Z",
            "state" : "awaits_successful_execution"
          }
        }
      },
      "execution_state" : "failed",
      "version" : 20
    },
    "trigger_event" : {
      "type" : "manual",
      "triggered_time" : "2020-07-15T14:12:01.042Z",
      "manual" : {
        "schedule" : {
          "scheduled_time" : "2020-07-15T14:12:01.042Z"
        }
      }
    },
    "input" : {
      "search" : {
        "request" : {
          "search_type" : "query_then_fetch",
          "indices" : [
            "connect-*"
          ],
          "rest_total_hits_as_int" : true,
          "body" : {
            "size" : 0,
            "aggs" : {
              "convratepercustomer_account" : {
                "terms" : {
                  "field" : "customer_nr"
                },
                "aggs" : {
                  "total_orders" : {
                    "filter" : {
                      "term" : {
                        "order_type" : "ORDER"
                      }
                    }
                  }
                },
                "total_searches" : {
                  "filter" : {
                    "term" : {
                      "must" : [
                        {
                          "terms" : {
                            "event_type" : [
                              "fulltextsearch",
                              "fahrzeugsuche",
                              "teilesuche"
                            ]
                          }
                        }
                      ]
                    }
                  }
                },
                "bucket_script" : {
                  "buckets_path" : {
                    "totalorders" : "total_orders",
                    "totalsearches" : "total_searches"
                  },
                  "script" : "params.totalorders / params.totalsearches * 100"
                }
              }
            }
          }
        }
      }
    },
    "condition" : {
      "always" : { }
    },
    "metadata" : {
      "xpack" : {
        "type" : "json"
      }
    },
    "result" : {
      "execution_time" : "2020-07-15T14:12:01.042Z",
      "execution_duration" : 0,
      "input" : {
        "type" : "search",
        "status" : "failure",
        "error" : {
          "root_cause" : [
            {
              "type" : "parsing_exception",
              "reason" : "Found two aggregation type definitions in [convratepercustomer_account]: [terms] and [total_searches]",
              "line" : 1,
              "col" : 166
            }
          ],
          "type" : "parsing_exception",
          "reason" : "Found two aggregation type definitions in [convratepercustomer_account]: [terms] and [total_searches]",
          "line" : 1,
          "col" : 166
        },
        "search" : {
          "request" : {
            "search_type" : "query_then_fetch",
            "indices" : [
              "connect-*"
            ],
            "rest_total_hits_as_int" : true,
            "body" : {
              "size" : 0,
              "aggs" : {
                "convratepercustomer_account" : {
                  "terms" : {
                    "field" : "customer_nr"
                  },
                  "aggs" : {
                    "total_orders" : {
                      "filter" : {
                        "term" : {
                          "order_type" : "ORDER"
                        }
                      }
                    }
                  },
                  "total_searches" : {
                    "filter" : {
                      "term" : {
                        "must" : [
                          {
                            "terms" : {
                              "event_type" : [
                                "fulltextsearch",
                                "fahrzeugsuche",
                                "teilesuche"
                              ]
                            }
                          }
                        ]
                      }
                    }
                  },
                  "bucket_script" : {
                    "buckets_path" : {
                      "totalorders" : "total_orders",
                      "totalsearches" : "total_searches"
                    },
                    "script" : "params.totalorders / params.totalsearches * 100"
                  }
                }
              }
            }
          }
        }
      },
      "actions" : [ ]
    },
    "messages" : [
      "failed to execute watch input"
    ]
  }
}

What shall i change to make it work?

2

This is not a watcher problem, but the structure of the search request is wrong.

Just copy the query into the dev tools console an see it fails. My assumption is, that you have specified a terms agg and the total_searches in the same json structure instead of possibly nesting the total search in another aggs structure within the terms agg.

3

Hello Alexander i created a transform script like that in order to ectraxt the info that i want in another index and from there to create a table visualization easily.

My script is the following:

PUT _transform/testalextest
{
"source": {
  "index": "connect-000001"
},
"dest" : {
  "index" : "convertionpercustomer"
},
"pivot": {
  "group_by": {
    "carrier": { "terms": { "field": "customer_nr" }}
  },
  "aggregations": {
    "total_orders": {
                    "filter": {
                      "term": {
                        "order_type": "ORDER"
                      }
                    }
                  },
     "total_searches": {
                "filter": {
                        "term": {
                          "event_type": 
                            "fulltextsearch"
                        }
                }
              },
    "convertioncalculation": {
    "bucket_script": {
               "buckets_path": {
                 "totalorders": "total_orders.buckets.doc_count",
                 "totalsearches": "total_searches.buckets.doc_count"
               },
               "script": "params.totalorders / params.totalsearches * 100"
             }
    }
  }
}
}

But i get the following error when i run it:

    {
  "error" : {
    "root_cause" : [
      {
        "type" : "status_exception",
        "reason" : "Failed to test query"
      }
    ],
    "type" : "status_exception",
    "reason" : "Failed to validate configuration",
    "caused_by" : {
      "type" : "status_exception",
      "reason" : "Failed to test query",
      "caused_by" : {
        "type" : "action_request_validation_exception",
        "reason" : "Validation Failed: 1: No aggregation found for path [total_orders.buckets.doc_count];"
      }
    }
  },
  "status" : 503
}

can you provise some help on that one?

Thank you in advance

4

Hello Alexandrer,

I made it work with the following code:

  PUT _transform/testalextest7
{
"source": {
  "index": "webshop-events-000003"
},
"dest" : {
  "index" : "convertionpercustomer"
},

"pivot": {
  "group_by": {
    "carrier": { "terms": { "field": "customer_nr" }}
	  
  },
  "aggregations": {
    "total_orders": {
                    "filter": {
                      "term": {
                        "order_type.keyword": "ORDER"
                      }
                    }
                  },
     "total_searches": {
                "filter": {
                        "terms": {
                         "event_type.keyword": [
                        "fulltextsearch",
                        "fahrzeugsuche",
                        "teilesuche"
                      ]
                        }
                }
              },
    "convertioncalculation": {
    "bucket_script": {
               "buckets_path": {
                 "totalorders": "total_orders._count",
                 "totalsearches": "total_searches._count"
               },
               "script": " (params.totalorders / params.totalsearches) * 100"
             }
    }
  }
}
}

The only thing left is that i want the results of only last 30 days.

Can you help me on how to change my code in order to put the time period i am interested ?

Thank you

5

I am confused, you initially asked about a watcher issue and now you are using a transform? Maybe add some more context of why/how you are doing something? :slight_smile:

6

Hello Alexander,

Sorry for the confusion i just preferred the transform approach instead of the alert one and i found the solution.

Thank you a lot in advance,

Best regards,
Alexandros

7

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