Joining multiple fields of two searches together on certain conditions

[horsefez]

Hi fellow splunkers,

I currently try to do a splunk auditing by searching which user logged into the system using some sort of useragent and so on. Then I try to check if the user displayed has administration rights by appending the subsearch displayed below. After this I need to somehow check if the user and username of the two searches match. And then somehow write the matching role for it into the event.
I'll try to visualize what I want to do, so you hopefully understand it better.

First my current search:

index=_internal sourcetype=splunkd_ui_access useragent!="SplunkCli*" user!="-" 
| sort _time
| dedup user clientip useragent
| append [| rest /services/authentication/users splunk_server=* | dedup title roles | fields roles title | rename title as username]
| table _time index user clientip useragent roles username

The index=_internal sourcetype=splunkd_ui_access events look like the following example:

_time; index; user; clientip; useragent
2016-07-27 08:56:37.998; _internal; peter; 77.32.89.12; Mozilla Firefox

The rest /services/authentication/users splunk_server=* events look like the following example

roles; username
admin; olaf
admin; peter
user; frank
user; chris

When I | table _time index user clientip useragent roles username it looks like that:

_time; index; user; clientip; useragent; roles; username
2016-07-27 08:56:37.998; _internal; peter; 77.32.89.12; Mozilla Firefox; ;  
; ; ; ; ; admin; olaf
; ; ; ; ; admin; peter
; ; ; ; ; user; frank
; ; ; ; ; user; chris

---

My goal is to get events like the following example shows by somehow joining/appending the results by matching user==username:

_time; index; user; clientip; useragent; roles; username
2016-07-27 08:56:37.998; _internal; peter; 77.32.89.12; Mozilla Firefox;admin; peter 

Any ideas how to do that?

Thanks in advance!

Best regards,
pyro_wood

[horsefez]

Thank you to my past self for asking this question.
I was confronted with the same problem today. 🙂

[Raschko · Accepted solution]

You could use the mentioned "join" command to join both searches together on user. Rename field "title" to the same user field name as in the main search, here field name "user".

Search:

index=_internal sourcetype=splunkd_ui_access useragent!="SplunkCli*" user!="-" 
 | sort _time
 | dedup user clientip useragent
 | join type=left user [| rest /services/authentication/users splunk_server=* | dedup title roles | fields roles title | rename title as user]
 | table _time index user clientip useragent roles

[horsefez]

Hi Raschko,

I tried to search for a solution without a left join, but it seems to be not possible without it.

Thanks for the help!