How to compile 3 searches into one to get the following Information: SAML Group, Splunk Role, index?

[NanSplk01]

These are the 3 searches I have found, but I need to combine them so that I can get the information all out on one search.  Also, how can I then take this and use a rest API with Azure to get the SAML Group real name?

This search gives indexes attached to roles

| rest /services/authorization/roles | table title srchIndexesAllowed

This search gives you SAML ID and Roles

| rest /services/admin/SAML-groups
| table title roles
| rename title as SAML

This search has roles to indexes

| rest /services/authentication/users | mvexpand roles | table roles | join roles [ rest /services/authorization/roles | rename title as roles | search srchIndexesAllowed=* | table roles srchIndexesAllowed] | rename roles as Roles, srchIndexesAllowed as "Indexes this Role has access"
| dedup Roles

[yuanliu]

1. You forget to tell us how the combined results look like, and what logic is supposed to connect the three searches to get there.
2. The third search already joins the first search in a certain manner. ("title" in the first search is matched with "role".)  Why do you need the first search again?

If I take a wild guess, all that is left to do is to join the second search, again, with roles, in order to show SAML ID.  If I take the laziest route, you can do

| rest /services/authentication/users
| mvexpand roles
| table roles
| join roles
    [| rest /services/authorization/roles
    | rename title as roles
    | search srchIndexesAllowed=*
    | table roles srchIndexesAllowed ]
| join
    [| rest /services/admin/SAML-groups
    | table title roles
    | rename title as SAML]
| rename roles as Roles, srchIndexesAllowed as "Indexes this Role has access"
| dedup Roles

Using the entire users table merely for roles is quite wasteful.  A slightly more efficient search is

| rest /services/authentication/users
| stats count by roles
| fields - count
| join roles
    [| rest /services/authorization/roles
    | rename title as roles
    | search srchIndexesAllowed=*
    | table roles srchIndexesAllowed ]
| join roles
    [| rest /services/admin/SAML-groups
    | table title roles
    | rename title as SAML]
| rename roles as Roles, srchIndexesAllowed as "Indexes this Role has access"

Hope this helps.

[NanSplk01]

Just realized that the search is not bringing back all the details.  There are over 200 roles in our Splunk Cloud, but not all of them are showing.  How do I get all the information.  Say there are roles that have not been attached to an index or a SAML group?  How do I get all the information?  I'm guessing I would need to be able to see those that have null values?

Any assist would be greatly appreciated.  Trying to get this information so that we can keep track of and verify for security that everything we have is matching up with what we expect to see.

[yuanliu]

The reason my wild guess used authentication/users endpoint as the first of three REST search is because your original search 3 used that as the first.  As such, I speculated that your intention was to limit output to those of allocated users only.

Because that is not the case, the users search contributes nothing and can be dropped altogether.  If you know which search gives all roles, simply use that as the first search before performing inner join. (You can use outer join but that seems wasteful.)  Suppose authorization/roles has the complete list, you can do something like

| rest /services/authorization/roles
| rename title as roles
| search srchIndexesAllowed=*
| table roles srchIndexesAllowed
| join
    [| rest /services/admin/SAML-groups
    | table title roles
    | rename title as SAML]
| rename roles as Roles, srchIndexesAllowed as "Indexes this Role has access"

Yes, some rows will have null values for SAML.

Note the reason to use join is also because you already know join.  The general advice is to avoid join and use stats.  In your case, all searches start with rest which can only support one URI.  There is no gain to use stats.

[NanSplk01]

This was perfect, now I just need to combine this with Azure to get the final piece.  Appreciate the assist.

[NanSplk01]

Unfortunately the null value is not being used when one of the fields doesn't match.  For instance if I have a SAML value, but it hasn't been mapped to a role yet, I would want a null value as the value for that role field.  Is that possible with the search?

[yuanliu · Accepted solution]

In that case, you'll have to use left join - again, not much to be gained by using alternatives.

| rest /services/authorization/roles
| rename title as roles
| search srchIndexesAllowed=*
| table roles srchIndexesAllowed
| join type=left
    [| rest /services/admin/SAML-groups
    | table title roles
    | rename title as SAML]
| rename roles as Roles, srchIndexesAllowed as "Indexes this Role has access"