I am trying to combine the search results from 3 separate sources logs and then compare the results against it against a employee list and display only the items in the employees.csv that don't show up in the search. The employees.csv contains some users that have a special status so I need to exclude those from the search as well. Here is my search term:
|inputlookup employees.csv | search NOT status="*" | search NOT [ search [ search index=main sourcetype="syslog" host="ssh-server" "session opened for user" | dedup userLogin | eval login = upper(userLogin)| fields login] | append [search sourcetype="MDT" | dedup EMP_ID | eval op_code = upper(EMP_ID)| fields op_code] | append [search host="ssh-server" who: | dedup userLogin | eval login = upper(userLogin)| fields login] | append [search source=webapps/var-log/httpd/log* | dedup userLogin | eval login = upper(userLogin)| fields login ] ]
employees.csv ---------- has the following fileds: full_name, login, op_code, status
search NOT status="*" -------- is used to exclude those employees with a special status from the CSV
search NOT [ search [ search index=main sourcetype="syslog" host="ssh-server" "session opened for user" | dedup userLogin | eval login = upper(userLogin)| fields login ------ pull the ssh logins from the server dedup the userLogin field, convert it to uppercase and call it login to match the CSV field.
search sourcetype="MDT" | dedup EMP_ID | eval op_code = upper(EMP_ID)| fields op_code] -------- pull the logins from MDT field is called EMP_ID and then converted to uppercase and converted to op_code for the CSV.
search host="ssh-server" who: | dedup userLogin | eval login = upper(userLogin)| fields login --------- a script dumps the users currently logged in to the syslog with the tag of "who:" in them. This is done to catch users who have been logged in for a long time frame.
search source=webapps/var-log/httpd/log* | dedup userLogin | eval login = upper(userLogin)| fields login -------- Pull user logins from the web server convert the userLogin to uppercase and call it login to match the CSV file.
I hope this breakdown helps explain my search. The goal of this is to find employees that have not logged into the system in a set time frame. I get good results running each search independently but when I combine them I get bad results so I am missing something.
... View more