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!

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