Hi!
Iv created the following search:
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"
to give the following output:
Loggedin_user host_ip timestamp username
root 192.168.1.105 Aug 17 09:04:52
root 192.168.1.105 Aug 12 09:42:07
root 192.168.1.105 Aug 12 10:22:43
root 192.168.1.105 Aug 12 10:01:47 root
admin
test
What i want to do here is compare the username column which is a list of authenticated user names from a database and the loggedin_user column which is users which have successfully logged on to the system. I want to have an additional column which will verify where the loggedin_users username is in the username column.
Can anyone point me in the right direction?
Thanks
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 | rename Loggedin_user AS username | append [dbxquery connection=splunkdb query=SELECT%20name%20FROM%20users]| fields - _raw, _time | rename "(001) users.name.VARCHAR" as "username" | stats values(*) AS * dc(source) AS numSources by username | eval inBoth=if(numSources=2, "YES", "NO")
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 | rename Loggedin_user AS username | append [dbxquery connection=splunkdb query=SELECT%20name%20FROM%20users]| fields - _raw, _time | rename "(001) users.name.VARCHAR" as "username" | stats values(*) AS * dc(source) AS numSources by username | eval inBoth=if(numSources=2, "YES", "NO")
I cant seem to get that working, I have tried using
eval Results=if(match(Loggedin_user,username), "yes","no")
as well but that doesn't work? I need to go through each value from the username column and see if it appears in the Loggedin_user column then say yes if it does and display the timestamp and host_ip.
The match
command does not work that way. Did you run my search exactly the way I wrote it? It absolutely should work as-is without any modification. I just noticed that it is not optimized well so try this one:
index=* source=/var/log/secure timestamp="*" host_ip="*"| table Loggedin_user, host_ip, timestamp | rename Loggedin_user AS username | append [dbxquery connection=splunkdb query=SELECT%20name%20FROM%20users | rename "(001) users.name.VARCHAR" as "username"] | stats values(*) AS * dc(source) AS numSources by username | eval inBoth=if(numSources=2, "YES", "NO")
Yeah i copied and pasted it in, i dont really understand whats going on with the stats command here. And still i cant get it working, the output i got is;
username host_ip inBoth timestamp numSources
adam 192.168.1.105 NO Aug 18 09:35:58 0
admin 192.168.1.105 NO Aug 18 09:36:08 0
other 192.168.1.105 NO Aug 18 11:16:43 0
root 192.168.1.105 NO Aug 18 11:16:43 0
I don't know why the dc(source)
is not working but we can do that part another way. Try this:
index=* source=/var/log/secure timestamp="*" host_ip="*"| table Loggedin_user, host_ip, timestamp | rename Loggedin_user AS username | eval type="Log" | append [dbxquery connection=splunkdb query=SELECT%20name%20FROM%20users | rename "(001) users.name.VARCHAR" as "username" | eval type="DB"] | stats values(*) AS * dc(type) AS numSources by username | eval inBoth=if((numSources==2), "YES", "NO")
Thats it now! Thanks a lot for your help!