Splunk Search

Avoiding a double-lookup for an efficient search

98123722
Explorer

A user is only allowed to log in from one of their AllowedPlatform:

userAllowedPlatform.csv

| User     | AllowedPlatform |
+----------+-----------------+
| Mike     | PC              |
+----------+-----------------+
| Mike     | iPad            |
+----------+-----------------+
| Andrew   | PC              |
+----------+-----------------+
| Jennifer | iPad            |
+----------+-----------------+
| Jennifer | iPhone          |

The following datasets shows all user activities:

| User     | PlatformUsed |
+----------+--------------+
| Mike     | PC           |
+----------+--------------+
| Mike     | iPad         |
+----------+--------------+
| Mike     | iPhone       |
+----------+--------------+
| Jennifer | iPad         |
+----------+--------------+
| John     | iPhone       |

To find out who uses a platform that's not AllowedPlatform, I run a simple lookup for Users in the dataset against userAllowedPlatform.csv:

index="userLogs" | lookup userAllowedPlatform.csv User AS User, AllowedPlatform as PlatformUsed OUTPUT AllowedPlatform as PlatformUsed_Violation | eval description=if(isnull(PlatformUsed),PlatformUsed_Violation,description) | where NOT len(PlatformUsed_Violation) >1

Which gives me ALL unmatching results:

| User | PlatformUsed_Lookup |
+------+---------------------+
| Mike | iPhone              |
+------+---------------------+
| John | iPhone              |

My goal is perform this but exclude entries that are not part of userAllowedPlatform.csv (John, in this case). I can take the above results and match them again against userAllowedPlatform.csv, this time to remove entries with users that are not in userAllowedPlatform.csv, but this seems to me inefficient and over-complicated.

Looking for a simple and efficient way of solving this.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index="userLogs" 
| lookup userAllowedPlatform.csv User AS User OUTPUT AllowedPlatform 
| eval description=if(isnull(PlatformUsed),AllowedPlatform,description) 
| where isnotnull(AllowedPlatform) AND AllowedPlatform!=PlaformUsed

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index="userLogs" 
| lookup userAllowedPlatform.csv User AS User OUTPUT AllowedPlatform 
| eval description=if(isnull(PlatformUsed),AllowedPlatform,description) 
| where isnotnull(AllowedPlatform) AND AllowedPlatform!=PlaformUsed

98123722
Explorer

Thanks! worked perfectly.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...