So I have a search which produces a table like this:
index=* source=/var/log/secure | table Loggedin_user, host_ip, timestamp | sort by Loggedin_user | WHERE timestamp NOT NULL | WHERE host_ip NOT NULL | append[dbxquery connection=splunkdb query=SELECT%20name%20FROM%20users]| fields - _raw, _time | rename "(001) users.name.VARCHAR" as "username"
Loggedin_user host_ip timestamp username
admin 192.168.1.10 Aug 18 09:36:08
root 192.168.1.105 Aug 18 08:58:3
What I'm looking to do is use the username column to verify whether the user logged in has been pre-authorized so I would have an output like such:
Loggedin_user host_ip timestamp pre-authorized
admin 192.168.1.1 Aug 18 yes
root 192.168.1.1 Aug 18 yes
user 192.168.1.1 Aug 18 no
Is there a way I can go through the username column comparing each field to the whole Loggedin_user column?
This is completely answered in the question below: