Have been trying to crack this for a long time. Would highly appreciate any help.
I have a lookup similar to this:
Merchant_ID Amount_threshold Count_threshold Description
Merchant1 15 1 ABC
Merchant2 11 5 XYZ
Merchant3 25 5 LMN
* 13 1 all_other_merchants
And my events are:
Merchant_ID Amount
Merchant1 10
Merchant1 20
Merchant2 10
Merchant3 40
Merchant7 30
Merchant7 20
I want my query to return something like this:
If for each merchant:
Total_amount>amount_threshold AND count>count_threshold
Then show it in the table below
Merchant_ID Description Total_amount Total_count Amount_threshold Count_threshold
Merchant1 ABC 30 2 15 1
Merchant7 all_other_merchants 50 2 13 1
Update: Added a new case - a wild card merchant ID that captures all merchants not explicitly specified in the lookup and applies thresholds to that merchant.
Like this
Your Base Search Here | appendpipe [|inputlookup YourLookupHere]
| stats values(*) AS * sum(Amount) AS Total_amount count AS Total_count BY Merchant_ID
| where Total_amount>Amount_threshold AND Total_count>Count_threshold
| table Merchant_ID Description Total_amount Total_count Amount_threshold Count_threshold
Did not work out for me. Trying to troubleshoot.
I tested and it works. It may be that you have not described something in your dataset (probably a field name) accurately? Double-check all the fieldnames and make sure that they match.
Use a left join against the lookup table, like this...
(your event search)
| stats count as Total_count sum(Amount) as Total_amount by Merchant_ID
| join type=left Merchant_ID
[| inputlookup yourlookup.csv | table Merchant_ID Amount_threshold Count_threshold Description ]
| where (Total_amount>Amount_threshold AND Total_count>Count_threshold) OR (isnull(Amount_threshold))
| fillnull value="((Not Found))" Description Amount_threshold Count_threshold
| table Merchant_ID Description Total_amount Total_count Amount_threshold Count_threshold
...and here's some run-anywhere code to show you that it works...
| makeresults
| eval mydata="Merchant1,10 Merchant1,20 Merchant2,10 Merchant3,40 Merchant4,40"
| makemv mydata
| mvexpand mydata
| rex field=mydata "(?<Merchant_ID>[^,]+),(?<Amount>.*)"
| stats count as Total_count sum(Amount) as Total_amount by Merchant_ID
| join type=left Merchant_ID
[| makeresults
| eval mylookup="Merchant1,15,1,ABC Merchant2,11,5,XYZ Merchant3,25,5,LMN"
| makemv mylookup
| mvexpand mylookup
| rex field=mylookup "(?<Merchant_ID>[^,]+),(?<Amount_threshold>[^,]+),(?<Count_threshold>[^,]+),(?<Description>.*)"
| table Merchant_ID Amount_threshold Count_threshold Description ]
| where (Total_amount>Amount_threshold AND Total_count>Count_threshold) OR (isnull(Amount_threshold))
| fillnull value="((Not Found))" Description Amount_threshold Count_threshold
| table Merchant_ID Description Total_amount Total_count Amount_threshold Count_threshold
Assuming the lookup file name is merchantlookup.csv
<YourBaseSearch>
| stats count as Total_count sum(Amount) as Total_amount by Merchant_ID
| lookup merchantlookup MerchantID OUTPUT Amount_threshold Count_threshold Description
| where Total_amount>Amount_threshold AND Total_count >Count_threshold
| table MerchantID Description Total_amount Total_count Amount_threshold Count_threshold
I tried this out. The threshold values display just one value for all rows instead of displaying threshold value applicable to the specific merchant.