How to create a query for user accesses and role indexes?

[hermeschu]

Hi Folks, I'm trying to create a query where it shows users logged in and indexes belonged or use (one of the two options...). Something like: LAST_LOGIN_DATE, USER, REAL_NAME, INDEX_NAME. But sadly I cannot retrieve the indexes on some of them and sometimes repeat them on the result. I created a query using some other examples on the web without success: Could you help me? Thank you so much!

| rest /services/authentication/users 
| search type=Splunk
| table title roles realname 
| rename title as user 
| rename roles as title 
| mvexpand title  
| join type=left max=0 title [
    | rest /services/authorization/roles splunk_server=local 
    | table title srchInd* 
    | eval indexes=mvappend(srchIndexesAllowed,srchIndexesDefault) 
    | table title indexes 
    | mvexpand indexes 
    | dedup title indexes 
    | eval indexes_orig=indexes 
    | join indexes max=0 type=left [
        | rest /services/data/indexes 
        | stats count by title 
        | table title
        | eval indexes=if(match(title,"^_"),"_*","*") 
        | rename title as indexes_new]
        | eval indexes=if(indexes_orig!=indexes_new,indexes_new, indexes_orig) 
        | table title indexes] 
| join user [
    search index=_audit action="login attempt" info=succeeded 
    | dedup user 
    | table user timestamp]
| dedup user timestamp
| table timestamp user realname indexes
| sort - timestamp

[hermeschu]

That worked fine! Thank you!

[larmesto · Accepted solution]

If you're talking about access permissions a user (or role) may have for some index. Try this approach (I've modified some of your base code, but I think you were going on the right way...):

| rest /services/authentication/users
| search type=Splunk
| table title roles realname
| rename title as user
| rename roles as title
| mvexpand title
| join type=left max=0 title
[| rest /services/authorization/roles
| table title srchIndexesAllowed
| eval indexes=coalesce(srchIndexesAllowed,srchIndexesDefault)
| table title indexes]
| join user
[ search index=_audit action="login attempt" info=succeeded
| dedup user
| table user timestamp]
| table timestamp user realname indexes
| sort - timestamp

Regards