elasticsearch watcher not displaying correct time with format

Question

I'm building a watcher to send alerts based on when this index gets reports of a failed job. In the "Discover" tab the date NextRunDate shows perfectly fine:

JobName      MyJobName
NextRunDate  Feb 29, 2020 @ 06:30 AM

But when I try to pull it with the watcher it is somehow behind:

"key" : "MyJobName",
"NextRunDate" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
    {
    "key_as_string" : "Feb 29 @ 11:30 AM (Minus 5 hours)",
    "doc_count" : 24,
    "key" : 1582975800000
    }
  ]
}

I've had to add the "(Minus 5 hours)" part to the query in order for it to display correctly for the time being. Here's the full watcher:

POST _watcher/watch/_execute
{
    "watch": {
        "trigger": {
            "schedule": {
                "hourly": {
                    "minute": [
                        0,
                        15,
                        30,
                        45
                    ]
                }
            }
        },
        "input": {
            "search": {
                "request": {
                    "search_type": "query_then_fetch",
                    "indices": [
                        "prod-jobs-*"
                    ],
                    "rest_total_hits_as_int": true,
                    "body": {
                        "size": 0,
                        "query": {
                            "bool": {
                                "filter": {
                                    "range": {
                                        "timestamp": {
                                            "gte": "now-15h"
                                        }
                                    }
                                },
                                "must": [
                                    {
                                        "match_all": {}
                                    }
                                ]
                            }
                        },
                        "aggs": {
                            "JobName": {
                                "terms": {
                                    "field": "JobName.keyword",
                                    "size": 5000,
                                    "order": {
                                        "_key": "desc"
                                    }
                                },
                                "aggs": {
                                    "PackageName": {
                                        "terms": {
                                            "field": "Package_Name.keyword",
                                            "size": 5000,
                                            "order": {
                                                "_key": "desc"
                                            }
                                        },
                                        "aggs": {
                                            "Error_Message": {
                                                "terms": {
                                                    "field": "Error_Message.keyword",
                                                    "size": 5000,
                                                    "order": {
                                                        "_key": "desc"
                                                    }
                                                }
                                            }
                                        }
                                    },
                                    "FailedDate": {
                                        "terms": {
                                            "field": "StopExecutionDate",
                                            "format": "MMM d @ h:mm a '(Minus 5 hours)'"
                                        }
                                    },
                                    "NextRunDate": {
                                        "terms": {
                                            "field": "NextRunDate",
                                            "format": "MMM d @ h:mm a '(Minus 5 hours)'"
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        },
        "condition": {
            "compare": {
                "ctx.payload.hits.total": {
                    "gt": 0
                }
            }
        },
        "actions": {
            "email_1": {
                "email": {
                    "profile": "standard",
                    "to": [],
                    "subject": "{{ctx.metadata.name}} has triggered",
                    "body": {
                        "html": "<html> <head> <style> body { font-family: 'Lucida Sans', 'Lucida Sans Regular', 'Lucida Grande', 'Lucida Sans Unicode', 'Geneva', 'Verdana', 'sans-serif'; } table { margin-left: 15px; border-left: 1.5px solid gray } tr, th { font-size: x-small; white-space: nowrap; text-align: left; padding: 7.5px; } td { font-size: x-small; text-align: left; padding: 7.5px; } </style> </head> <body> <h3>Job Failures with Errors</h3> {{#ctx.payload.aggregations.JobName.buckets}} <table style='border-collapse: collapse; border-spacing: 0;'> <tr> <th>Job Name</th> <td>{{key}}</td> <tr> <th>{{#PackageName.buckets}}Package Name</th> <td>{{key}}</td> </tr> <tr> <th>Error Message(s)</th> <td>{{#Error_Message.buckets}}{{key}}<br>{{/Error_Message.buckets}}</td>{{/PackageName.buckets}} </tr> <tr> <th>Job Fail Date/Time: </th> <td>{{#FailedDate.buckets}}{{key_as_string}}</td>{{/FailedDate.buckets}} </tr> <tr> <th>Next Run Date/Time: </th> <td>{{#NextRunDate.buckets}}{{key_as_string}}</td>{{/NextRunDate.buckets}} </tr> </table> <br>{{/ctx.payload.aggregations.JobName.buckets}}<br> </body> </html>"
                    }
                }
            }
        },
        "metadata": {
            "time_window": "5m",
            "time_period": "1m"
        }
    }
}

Not sure what the problem is since kibana itself is showing the date correctly on the Discover tab. Is my format field off?

Answer 1

So, after a while of toying with this, I got help from the elastic support team and this is the result:

Watchers, although sometimes configured in kibana, are ultimately an elasticsearch feature. Which, in short, means that the time that the watcher retrieves when called ("key" : 1582975800000) is the time from the actual elasticsearch node.

So I had to create a transform property in my watcher, like so:

return 
[ 
    'new_payload': ctx.payload.aggregations.JobName.buckets.stream().map(job -> 
    { 
        return [ 
        'JobName': job.key, 
        'FailedDate': LocalDateTime.ofEpochSecond((job.FailedDate.buckets[0].key) / 1000 , 0, ZoneOffset.of(\"-04:00\")).format(DateTimeFormatter.ofPattern('MMM d @ h:mm a')), 
        'PackageName': job.PackageName.buckets.0.key, 
        'ErrorMessages': job.PackageName.buckets.0.Error_Message.buckets.stream().map(errors -> { return errors }).collect(Collectors.toList()), 
        'NextRunOffset': LocalDateTime.ofEpochSecond((job.NextRunDate.buckets[0].key) / 1000 , 0, ZoneOffset.of(\"-04:00\")).format(DateTimeFormatter.ofPattern('MMM d @ h:mm a')) 
    ]}).collect(Collectors.toList()) 
]