Splunk Search

show only values from a lookup that are not returned in a search?

Aaron_Fogarty
Path Finder

My search events contain a userID e.g. 'b1234'. I am using a lookup file to show the name, manager and department of each user. I search by manager e.g. manager="john doe", and then table the results. This gives me table of all the users under the selected manager.

I would like to create a table that will show all of the employees that are not users.

This would be all of the other employees in the lookup that have the same manager but who's userID's are not in the search events.

This is my search that shows the users under the selected manager:

index=abc sourcetype="eq" getLoginInfo  User = *   |eval User=lower(User)  | lookup local=true Users.csv corp_id as User  | dedup User |search functional_mgr_name = "doe, john"   | table display_name department_name| rename display_name AS "Name" |rename department_name AS "Department" | sort "Department"

Thanks.

0 Karma

Aaron_Fogarty
Path Finder

Thanks for your help jKat54 and cb_usps, both of your answers helped me find the right query.

This is my working query:


| inputlookup Users.csv
| search NOT [search index=abc sourcetype="eq" getLoginInfo User = *
| eval User=lower(User)
| lookup local=true User.csv corp_id as User
| dedup User
| search functional_mgr_name ="doe, john"
| table display_name department_name]
| search functional_mgr_name = "doe, john"
| table display_name department_name
| rename display_name AS "Name"
| rename department_name AS "Department"
| sort "Department"

Thanks!

0 Karma

cb_usps
Explorer

To answer the question, "show only values from a lookup that are not returned in a search?", the contents of the lookup table have to be appended to the search and the search results negated.

index=abc sourcetype="eq" getLoginInfo User=*
 | eval User=lower(User)
 | stats count by User
 | append
   [
    | inputlookup Users.csv
    | rename corp_id as User
    | dedup User
    | search functional_mgr_name = "doe, john"
    | eval count=0
   ]
 | stats max(count) as count by User
 | where count = 0

Of course, my answer assumes that 'functional_mgr_name' is one of the fields in the lookup table.

0 Karma

jkat54
SplunkTrust
SplunkTrust
index=abc sourcetype="eq" getLoginInfo User=*
[   
 search index=abc sourcetype="eq" getLoginInfo User=*   
 | eval User=lower(User)  
 | dedup User
 | lookup local=true Users.csv corp_id as User  
 | search functional_mgr_name = "doe, john"   
 | fields User
 | format "NOT (" "(" "OR" ")" "OR" ")" 
]
0 Karma

jkat54
SplunkTrust
SplunkTrust

All of the other employees in the lookup that have the same manager but who';s userIDs are not in WHAT/WHICH search events? These? index=abc sourcetype="eq" getLoginInve User=*?

0 Karma

Aaron_Fogarty
Path Finder

Yes, the events returned from that complete search

index=abc sourcetype="eq" getLoginInfo User = * |eval User=lower(User) | lookup local=true Users.csv corp_id as User | dedup User |search functional_mgr_name = "doe, john" | table display_name department_name| rename display_name AS "Name" |rename department_name AS "Department" | sort "Department"

0 Karma

jkat54
SplunkTrust
SplunkTrust

Ok so the only thing is the department_name and manager name wont be available in the final results because the lookup provides them and you're looking for users that are in the lookup but not in the main search.

Please see my answer and let me know if it works for you.

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