Splunk Enterprise Security

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

edhealea
Path Finder

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

0 Karma
1 Solution

knielsen
Contributor

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.

View solution in original post

0 Karma

knielsen
Contributor

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.

0 Karma

edhealea
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...