Splunk Search

How to run a macro with a group by field?

Communicator

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.

Thanks

0 Karma

SplunkTrust
SplunkTrust

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 lookupGroupin 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.

0 Karma

Legend

Hi wcooper003,
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.
Bye.
Giuseppe

0 Karma

Communicator

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` ]
0 Karma

Communicator

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).

0 Karma