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
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
Thank you to my past self for asking this question.
I was confronted with the same problem today. 🙂
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
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!