How to output a single result when matching multiple results within a lookup table.

[edhealea]

I have an application file imported to be used as a lookup table in order to set the priority on servers within Assets and Identity but the file uses risk tiers instead of priorities. To get around this, I have created a risk tier priority lookup table. I can get the priority assign to each server on the list but what I can't seem to accomplish is to have only the server with the highest priority field returned and if there is no defined Risk Tier, I want to auto assign it a low priority. Server names below are in order for visual simplicity.

applications_to_servers.csv
Server,RiskTier,Application
serverA,0,App1
serverA,1,App2
serverB,0,App1
serverC,2,App3
serverC,3,App4
serverD, ,App5

risktier_priority.csv
RiskTier,priority
0,critical
1,high
2,medium
3,low

| inputlookup applications_to_servers.csv
| lookup risktier_priority.csv RiskTier
| sort RiskTier
| fields Server RiskTier priority Application

My search result output:
Server RiskTier Priority Application
serverA 0 critical App1
serverA 1 high App2
serverB 0 critical App1
serverC 2 medium App3
serverC 3 low App4
serverD App5

Desired output:
Server RiskTier Priority Application
serverA 0 critical App1
serverB 0 critical App1
serverC 2 medium App3
serverD low App5

[knielsen · Accepted solution]

Hi,

This is one way to do it:

| inputlookup applications_to_servers.csv 
| eventstats min(RiskTier) as mr by Server 
| where RiskTier=mr OR isnull(mr) 
| lookup risktier_priority.csv RiskTier 
| fillnull value="low" priority 
| table Server RiskTier priority Application

The eventstats will keep track of the minimum RiskTier per Server, and the where clause will only keep the ones where the RiskTier is the same as minimum.

Hth,
-Kai.

[edhealea]

Kai, That works perfectly. Thanks for the help and explanation. -Ed