Splunk Search

Filtering events where extracted field is not in the lookup file

LegalPrime
Path Finder

Hello, I am extracting a lot of values during search (using eval & split as recommended here), one of them being `username`.

I also have a lookup table called "expected_usernames.csv" that contains "service_expected_usernames" column and usernames in it.

I am having a hard time writing a search query that would return only events where extracted username field is not equal to any of the usernames in the lookup file.

I thought this answer would help, but it give me all the results not really caring about whether username matches or not.

 

index="mycustomindex"
| rex field=source "(.*)\_(?<logtype>(connectionlog|userlog|useractivitylog))\_(\d{4})\-(\d{2})-(\d{2})T(\d{2}):(\d{2})\.gz" 
| search (logtype="connectionlog") | eval temp=split(_raw,"|") 
... some extraction omitted for brevity ...
| eval username=mvindex(temp,6)
| fields - temp 
| search NOT 
    [| inputlookup expected_usernames.csv 
    | fields username 
    | rename username AS service_expected_usernames 
    | format
        ] 

 

This still returns all the records, no filtering applied. What am I doing wrong?

Labels (4)
0 Karma
1 Solution

LegalPrime
Path Finder

Looks like the issue was with renaming in the subsearch (I think it is incorrectly described in the answer I linked and used as a reference - the poster got it backwards (according to my testing)).

In the subsearch this gets it to work:

[| inputlookup expected_usernames.csv 
    | fields service_expected_usernames 
    | rename service_expected_usernames AS username
    | format
        ] 

 

View solution in original post

0 Karma

LegalPrime
Path Finder

Looks like the issue was with renaming in the subsearch (I think it is incorrectly described in the answer I linked and used as a reference - the poster got it backwards (according to my testing)).

In the subsearch this gets it to work:

[| inputlookup expected_usernames.csv 
    | fields service_expected_usernames 
    | rename service_expected_usernames AS username
    | format
        ] 

 

0 Karma
Get Updates on the Splunk Community!

Observability | How to Think About Instrumentation Overhead (White Paper)

Novice observability practitioners are often overly obsessed with performance. They might approach ...

Cloud Platform | Get Resiliency in the Cloud Event (Register Now!)

IDC Report: Enterprises Gain Higher Efficiency and Resiliency With Migration to Cloud  Today many enterprises ...

The Great Resilience Quest: 10th Leaderboard Update

The tenth leaderboard update (11.23-12.05) for The Great Resilience Quest is out &gt;&gt; As our brave ...