I construct a csv (output)lookup file containing the hourly average response time, the hourly number of events and the service concerned.
This file is updated daily (scheduled append).
| bin _time span=1h
| stats avg(responseTimeMilliseconds) as avgResponseTimeMilliseconds count(responseTimeMilliseconds) as numberOfEvents by _time Service
| table _time, Service,avgResponseTimeMilliseconds, numberOfEvents
| outputlookup hourlyaverage.csv append=true
This results in file containing the following columns:
_time | Service | avgResponseTimeMilliseconds | numberOfEvents
This "hourly" file is used for a certain audience.
Still another audience requests a report every quarter of the daily average response time.
Since I have the first file, I would like to avoid the generation of a second file as the daily average can be computed based on the hourly average and the number of events in each hour.
daily average=Sum(hourly average*hourly events)/daily events
I still can't figure out how to make it in Splunk.
Thanks already for your support,
... View more
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.
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
- table field_a1, field_a2, field_a3,
field_a4, field_a5, NumberOfEvents
Thanks you already for your help,
... View more