Splunk Search

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

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

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

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

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

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

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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!