Watcher report where the range in the report is dynamic

Elastic Stack Kibana
1

Created a query from a specific index and did the copy url to paste in to a watcher. Everything works except the report returns the same record set. I need to create the report with the previous weeks activity. I've put a few hours into trying to noodle this out without any luck. Can anyone give me some guidance here?

Robin

2

Hello Robin,

Can you post a screenshot of your watcher action? I'm confused how you would have a query without a time range.

3

I have posted my action here. The report has a range, but it's fixed and not dynamic.

"actions" : {
"email_admin" : {
"email": {
"to": ["stonerd@longwood.edu"],
"subject": "DBA Weekly Activity Report",
"body": {
"text": "DBA weekly activity report to be reviewed by department director."
},
"attachments" : {
"dba_report.csv" : {
"reporting" : {
"url": "https://visualize.longwood.edu:5601/api/reporting/generate/csv?jobParams=(conflictedTypesFields:!(),fields:!(extended_timestamp,username,ClientPC-UserName,Client-Computer,action_name,sql_text),indexPatternId:%270a9d2a90-7d62-11e9-a87f-6f4811b86fe0%27,metaFields:!(_source,_id,_type,_index,_score),searchRequest:(body:(_source:(excludes:!(),includes:!(extended_timestamp,username,ClientPC-UserName,Client-Computer,action_name,sql_text)),docvalue_fields:!(),query:(bool:(filter:!((match_all:())),must:!((bool:(minimum_should_match:1,should:!((match_phrase:(username:landisng)),(match_phrase:(username:eriksonjw)),(match_phrase:(username:pencefj))))),must_not:!((bool:(minimum_should_match:1,should:!((match_phrase:(action_name:LOGON)),(match_phrase:(action_name:LOGOFF)))))),should:!())),script_fields:(),sort:!((%27@timestamp%27:(order:desc,unmapped_type:boolean))),stored_fields:!(extended_timestamp,username,ClientPC-UserName,Client-Computer,action_name,sql_text),version:!t),index:%27oracle2-*%27),title:%27DBA%20Review%20File%20v2%27,type:search)",
"retries":6,
"interval":"20s",
"auth":{
"basic":{
"username":"elastic",
"password":"somepasswordhere"
}
}

4

Which index pattern are you attempting to query?

5

oracle2-*

6

I wonder if sorting by @timestamp is causing problems.

That said, it might be worthwhile to use the Kibana Watcher user interface (within Management) and recreate the query there.

Unfortunately I'm more or less guessing at which portion of your rather long query is causing the problem. It would be good to start as simple as possible and slowly add more complexity.

7

I think there is some confusion here....

The report and watcher fire and do there thing fine with the exception that the dates are absolute.

The report (filtered query) was created in "Discover" a few weeks ago with the time frame of now-7d/d which ended up putting the dates 08/11/2019 to 08/16/2019 in the report. For that week those dates are correct. When the watcher fired again this past weekend those dates were still in the report and basically delivered the same data.

What I'm trying to figure out is how can I use the report parameters that currently exist, but replace the date range with now-7d/d to now in the report so it delivers the latest seven days worth or data. I've tried directly altering the report parameters listed in the URL, but that throws an error in watcher.

I've written a watcher with an "input" section and have that working just fine. But I don't know how to get that data into a .csv or .pdf format so it can be reviewed. If there is a way to do that using the email attachment action I'd be fine with that as well.

Any help would be appreciated.

8

I think there is some confusion here

I think I understand your problem but its taking me a few tries to understand what needs to be changed.

What version of the elastic stack are you running? It would be nice to know that I'm looking at the same version as you are.

You mention using discover - did you use a absolute rather than relative date? I'm unclear because you state which ended up putting the dates 08/11/2019 to 08/16/2019 in the report

In my URL I see http://localhost:5601/app/kibana#/discover/a4988f80-c9c6-11e9-a5f6-ef645da9ed4c?_g=(refreshInterval:(pause:!t,value:0),time:(from:now-15h,to:now))&_a=(columns:!(_source),filters:!(),index:ff959d40-b880-11e8-a6d9-e546fe2bba5f,interval:auto,query:(language:kuery,query:''),sort:!(!(order_date,desc)))

which clearly has a relative time.

Can you verify this?

9

You are correct the URL that is presented in Kabana shows the relative date.
However when you use the Kibana watcher instructions for creating a watch that sends a report you end up with absolute dates. I've tried putting in relative dates in the params but this gives me an error. Thanks for the help. I guess I'll need to open a support ticket.

10

Please report back when the solution is found.

11

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