I have a macro that I want to run on multiple subsets of a data source (a group-by field). I can set up the search manually to do this by using the append command, but i'd like to make this automatic and more efficient. For instance, I want to run something akin to the map command but that performs calculations on groups (multiple rows that share a common field).
The main reasons I want to do this is to avoid having to manually append searches for each group, but also for efficiency - I want to avoid having to go to the index and pull the data multiple times along with repeated calls to a lookup to get the group-by field. The current approach i'm taking is this:
index=test | lookup testLookup lookupField | search lookupGroup = "A" | `macrofnx' | append [ search index=test | lookup testLookup lookupField | search lookupGroup = "B" | `macrofnx' ] | append [ ...]
So it seems inefficient to have to manually do the index pull and lookup join just to get the subgroup by which to do the calculation. Ideally i'd want something like this functionality (where the groupBy command would send subsets of the data sequentially to the macrofnx and stitch them all together):
index=test | lookup testLookup lookupField | groupBy lookupGroup [`macrofnx']
Any thoughts on if there is a way to do something similar, and avoid having to make repeated calls to the index and lookup, would be appreciated.
So, in English, for each lookupField value, there is a line in the lookup table that contains some information, including up_range and lookupGroup
For the events that belong to each lookupGroup, you want to run the macro. Then you want each of the
stats lines at the end of each run of the macro to be output.
All you need is to update your macro with "by lookupGroup
in three places and run it once for the whole input. Here I assume that lookupGroup and up_range are the only fields that come from the lookup, and thatweight` field is on the events themselves.
index=test | fields lookupField weight | lookup testLookup lookupField | eval weight_low_val = if(weight=11, up_range, 100) | eval weight_new = if(weight=11, 0, weight) | eventstats sum(weight_new) as weight_sum by lookupGroup | eval wavg = (up_range*weight_new)/weight_sum | eventstats sum(wavg) as wavg_sum by lookupGroup | eval wavg_min_val = if(weight_low_val<wavg_sum, weight_low_val, wavg_sum) | stats min(wavg_min_val) as health_score by lookupGroup
The above code would accomplish the same thing as the macro run against the individual groups... but we are not certain the original macro code was correct. We are especially suspicious of the calculation between the two
eventstats commands. However, if the original code was correct, this will produce the same output in one pass.
I don't know what your macro does, but at a first sight you could simplify your search inserting in the macro other components of your search
| lookup testLookup lookupField and also
| search lookupGroup = "A" passing a parameter to the macro (A or B).
Every way think to group your search and manage different cases with a search similar to the following:
index=test | lookup testLookup lookupField | eval groupby=if(lookupGroup="A",field1,field2) | stats count by groupby
If you share your macro, maybe I could help you more.
Hi Giuseppe, thanks for the reply. Below is the macro:
eval weight_low_val = if(weight=11, up_range, 100) | eval weight_new = if(weight=11, 0, weight) | eventstats sum(weight_new) as weight_sum | eval wavg = (up_range*weight_new)/weight_sum | eventstats sum(wavg) as wavg_sum | eval wavg_min_val = if(weight_low_val<wavg_sum, weight_low_val, wavg_sum) | stats min(wavg_min_val) as health_score
I thought of a different way to simplify it using map so I don't need to do the append, e.g.:
| inputlookup testLookup | top 10 lookupField | map [ search index=test | lookup testLookup lookupField | search lookupGroup=$lookupGroup$ | `macrofnx` ]
Note: the macro just calculates a weighted average to replicate how health scores are calculated in ITSI (this is all so we can compute health scores for subsets of the KPIs within a service, e.g. an infrastructure health score, an application health score).