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.
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!
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.
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" ")"
]
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=*
?
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"
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.