required a analytics report query for number of users having access to a application

[Growit]

Could we please help to get a report of number of users which have access to a application per month since April last year?

For example: 2023.04 - 212 users
2023.05- 100 users etc.

[rushikeshvartak]

SELECT ep.endpointname,
       Date_format(a.created_on, '%Y.%m') AS Month,
       Count(DISTINCT ua.userkey)         AS user_count
FROM   user_accounts ua
       JOIN accounts a
         ON ua.accountkey = a.accountkey
       JOIN account_entitlements1 ae
         ON ae.accountkey = a.accountkey
       JOIN entitlement_values e
         ON e.entitlement_valuekey = ae.entitlement_valuekey
       JOIN endpoints ep
         ON ep.endpointkey = a.endpointkey
WHERE  ep.endpointname = 'AD_endpoint_test'
GROUP  BY Date_format(a.created_on, '%Y.%m')
ORDER  BY month ASC; 

[Growit]

no data found i m getting for the query if i run in the data analyzer, there are somany users eventhough no data found

[rushikeshvartak]

Did you update endpoint as per your environment 

[Growit]

yes i have given in the place of 

SELECT ep.endpointname,
       Date_format(a.created_on, '%Y.%m') AS Month,
       Count(DISTINCT ua.userkey)         AS user_count
FROM   user_accounts ua
       JOIN accounts a
         ON ua.accountkey = a.accountkey
       JOIN account_entitlements1 ae
         ON ae.accountkey = a.accountkey
       JOIN entitlement_values e
         ON e.entitlement_valuekey = ae.entitlement_valuekey
       JOIN endpoints ep
         ON ep.endpointkey = a.endpointkey
WHERE  ep.endpointname = 'XYZ'
GROUP  BY Date_format(a.created_on, '%Y.%m')
ORDER  BY month ASC

[rushikeshvartak]

Share logs

[Growit]

thanks i got the data, but user is asking in this report what it shows  in user count

number of users who requested an account in application asked (new accounts), or a number of active users in a given month?

can you pls help on this. pls help me on both queries separately to understand so i can share the user

[rushikeshvartak]

Please raise new thread for same.✅Please click the 'Accept As Solution' button on the reply (or replies) that best answers your original question and hit 'Kudos' button 👍.

Refer https://forums.saviynt.com/t5/identity-governance/extract-request-history-only-one-endpoint-for-the-...

[Growit]

Pls help me on this

[rushikeshvartak]

I have given link of report use same.

[rushikeshvartak]

@Growit  ✅Please click the 'Accept As Solution' button on the reply (or replies) that best answers your original question and hit 'Kudos' button 👍.