How do I create a search that displays the amount of users on each app and how often it's been used in the last 60 days?

[SevenDos]

I'm trying to create a table that displays the following result

Appname	Amount of users with read access	amount of users that have accessed in the last 2 months	Open Access	Protected Access
AppX	<number>	<number>	O	P

I know that I can use the rest api for most (maybe all) of this.

The following tells me which apps there are and with what roles a user has read access.

| rest /servicesNS/-/-/apps/local splunk_server="local" 
| fields label, eai:acl.perms.read 
| rename eai:acl.perms.read as roles
| sort by label 
| search label!=_searchhead_config

The following tells me what users there are and what roles they have.

| rest /services/authentication/users splunk_server=local 
| fields title roles 
| mvexpand roles
| rename title as userName

What I want to do now is to combine those and by the roles, match which users have access to a certain app, and than count how many there are.

I'm a newbie and I've tried all kinds of things with join, append, appendcols but it never gives me the results I need. Can someone point me in the right direction?

[richgalloway]

Here's one way to combine those searches.  I'm sure there are other possibilities.  It depends on what the output needs to look like.

| rest /servicesNS/-/-/apps/local splunk_server="local" 
| fields label, eai:acl.perms.read 
| rename eai:acl.perms.read as roles 
| mvexpand roles 
| sort by label 
| search label!=_searchhead_config 
| append 
    [| rest /services/authentication/users splunk_server=local 
    | fields title roles 
    | eval roles=mvappend (roles, "*") 
    | mvexpand roles 
    | rename title as userName] 
| stats values(*) as * by roles 
| table userName roles label

[SevenDos]

Thanks for the response. What is happening in line 14? Is that an accidental paste that I should remove, or does there have to be an additional join there? Looks like the former as this is where the search starts with. Removing it tells me there is no roles.

I would want the output to look like:

label (name of the app)

amount of users that have read access to this app

[richgalloway · Accepted solution]

Sorry about the copy-paste error.  I've fixed it.

The current query produces a list of apps, the roles with access to the apps, and users with those roles.