Getting Data In

Compare Usernames from Search with Lookup (csv)

franz__
Engager

So I read a few answer from here, but I could't ge this to work.

My Seach:

search.... | dedup user | table user

and I want to to compare this with usernames from a csv. I've uploaded a lookup "test.csv" file one colum named username. I want an Output that shows all Usernames that are not in the csv

.... | dedup username | search NOT username [inputlookup test.csv] | table username

Shows only the Usernames that are in the List. Can someone help to show all User that are not in the Lookup?

0 Karma
1 Solution

woodcock
Esteemed Legend

Use the lookup capability, NOT a subsearch:

First this:

.... | dedup username | lookup test.csv username OUTPUT username AS foundInLookup

Then either this for "is in file":

| where isnotnull(foundInLookup)

Or this for "is NOT in file":

| where isnull(foundInLookup)

View solution in original post

woodcock
Esteemed Legend

Use the lookup capability, NOT a subsearch:

First this:

.... | dedup username | lookup test.csv username OUTPUT username AS foundInLookup

Then either this for "is in file":

| where isnotnull(foundInLookup)

Or this for "is NOT in file":

| where isnull(foundInLookup)

franz__
Engager

I had to add the Columname but then it worked, thanks!

... | dedup username | lookup test.csv username OUTPUT username AS foundInLookup | where isnull(foundInLookup) | table username
0 Karma

woodcock
Esteemed Legend

Right, fixed in my answer, too.

0 Karma

gcusello
SplunkTrust
SplunkTrust

hi franz__,
try something like this

your_search
| eval username=upper(username)
| stats count by username 
| append [| inputlookup test.csv | eval count=0, username=upper(username) | fields username count]
| stats sum(count) AS Total by username

usernames > 0 are present both in table and in lookup
usernames = 0 are only in lookup

Bye.
Giuseppe

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

This mod gets the users not in the list

your_search
 | eval username=upper(username)
 | stats count by username 
 | append [| inputlookup test.csv  | fields username| username=upper(username), count=0, flag="okay user"]
 | stats sum(count) AS Total, values(flag) as flag by username
 | where isnull(flag)
0 Karma

franz__
Engager

hey, thats for the quick comment. The test.csv contains a List of "OK" Users, I want to filter all Users that are not in that List.

0 Karma

gcusello
SplunkTrust
SplunkTrust

hi franz__,
did you inserted in the subsearch also count=0?
because in this way all lookup usernames have at least count=0 and in the sum(count) are present.
Bye.
Giuseppe

0 Karma

franz__
Engager

Yes I copied your Query. Both

earliest=-24h host="*" sudo:session | rex "pam_unix(sudo:session): session opened for user root by (?[[:alnum:]_.]+)" | dedup user | table user

earliest=-24h host="*" sudo:session | rex "pam_unix(sudo:session): session opened for user root by (?[[:alnum:]_.]+)" | eval username=upper(username)
| stats count by username
| append [| inputlookup test.csv | eval count=0, username=upper(username) | fields username count]
| stats sum(count) AS Total by username

Both shows the same Users, your shows the ammont of Logins (nice the stats count command), but the inputlookup / append commands seems to do nothing.

Thanks

0 Karma

gcusello
SplunkTrust
SplunkTrust

what's the output of

| inputlookup test.csv | eval count=0, username=upper(username) | fields username count

?
username field name is written in the same way both in search and in lookup?
Bye.
Giuseppe

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 ...