Checking for similar fields in two columns

[adaam94]

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

[woodcock · Accepted solution]

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")

[adaam94]

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.

[woodcock]

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")

[adaam94]

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

[woodcock]

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")

[adaam94]

Thats it now! Thanks a lot for your help!