Splunk Search

Joining multiple fields of two searches together on certain conditions

horsefez
SplunkTrust
SplunkTrust

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

0 Karma
1 Solution

Raschko
Communicator

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

View solution in original post

horsefez
SplunkTrust
SplunkTrust

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

0 Karma

Raschko
Communicator

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
SplunkTrust
SplunkTrust

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!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...