Hello everyone,
I am trying to combine the following:
- The query 1 looks for recent events (earliest=-10m@m latest=-5m@m) and check field_a1 against a lookup csv file and return the field_a2 associated to it and filter it based on field_a2 value (filtering_criteria).
- The query 2 look on the same index, and I try to count how many times a similar event has happened in the past without taking into account the last 5 days (earliest=-30d@d latest=-5d@d).
- What I want as result, is the events of the query 1 augmented with the count of the query 2.
I perform the searches in the order query 1 than query 2 for performance reasons (query 1 returns usually less than 10 events whereas query 2 returns 2,5 million results if not filtered).
To give a bit more context, I want to monitor recent traffic from or to specific addresses (addresses being contained in a lookup file with a reason for being in this lookup file) and count the number of similar traffic event in a larger past period without taking into the last 5 days.
I intend to use this "combined search" in scheduled alert that will run every 5 minutes and send email if there are results.
Starting point:
The index “index_a” has the following “columns”:
- field_a1, field_a2, ,field_a3, field_a4, field_a5
The inputlookup contains pair of values “values of field_a1” / “values of field_f1”
The query 1:
index=index_a earliest=-10m@m latest=-5m@m | inputlookup reference.csv field_a1 as field_a1 OUTPUT field_f1 as field_f1 | where field_f1="filtering_criteria" | stats list(field_a2) list(field_a3) list(field_a4) list(field_a5) by field_a1
- Generate a list of grouped events by field_a1
The query 2:
index=index_a earliest=-30d@d latest=-5d@d |inputlookup reference.csv field_a1 as field_a1 OUTPUT field_f1 as field_f1|stats count as NumberOfEvents by field_a1
- Generate a count by field_a1
Expected result:
- table field_a1, field_a2, field_a3,
field_a4, field_a5, NumberOfEvents
Thanks you already for your help,
Tom
