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!

Enterprise Security Content Update (ESCU) | New Releases

In January, the Splunk Threat Research Team had one release of new security content via the Splunk ES Content ...

Expert Tips from Splunk Professional Services, Ensuring Compliance, and More New ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Observability Release Update: AI Assistant, AppD + Observability Cloud Integrations & ...

This month’s releases across the Splunk Observability portfolio deliver earlier detection and faster ...