topic Re: Joining multiple fields of two searches together on certain conditions in Splunk Search

Joining multiple fields of two searches together on certain conditions

horsefez — Tue, 29 Sep 2020 10:24:02 GMT

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

Re: Joining multiple fields of two searches together on certain conditions

Raschko — Wed, 27 Jul 2016 12:33:48 GMT

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

Re: Joining multiple fields of two searches together on certain conditions

horsefez — Tue, 02 Aug 2016 14:54:03 GMT

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!

Re: Joining multiple fields of two searches together on certain conditions

horsefez — Fri, 28 Apr 2017 10:46:16 GMT

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