We have some internal people asking what is the percentage of users with at least 1 bound device separated by org. What is the best way to get this data?
We have some internal people asking what is the percentage of users with at least 1 bound device separated by org. What is the best way to get this data?
There is NO such "out-of-the-box" option available. However, one can directly query database to fetch this information at own risk. The query is fairly complex and will consume fair amount of DB resources (CPU, I/O and Memory) during the execution and therefore, peak traffic hours should be avoided. Also this query first should be tested in Dev and Testing Environments.
The output of these queries will be in the following format:
org_name | bind_user_count | org_user_count | perc_user
where
org_name: Organization name
bind_user_count: No. of users that are bound to at least 1 device
org_user_count: No. of users mapped against each organization
perc_user: % of users that are bound to at least 1 device
Query for Oracle Database:
select bind_user.org_name as org_name, bind_user.cnt as bind_user_count, usr.cnt as org_user_count,
(bind_user.cnt/usr.cnt) * 100 perc_user from
(select org_id, COUNT(1) cnt from users group by ORG_ID) usr,
(select o.org_id, o.org_name, count(b.id) cnt from orgs o full outer join
(select a.id, a.org_id from users a where exists (select 1 from bindings b where a.ID = b.USERID)) b
on o.ORG_ID = b.ORG_ID
group by o.org_id, o.ORG_NAME) bind_user
where nvl(usr.ORG_ID,0) = nvl(bind_user.org_id,0);
Query for SQL Server Database:
select bind_user.org_name as org_name, bind_user.cnt as bind_user_count, usr.cnt as org_user_count,
(cast(bind_user.cnt as float)/cast(usr.cnt as float)) * 100 perc_user from
(select org_id, COUNT(1) cnt from users group by ORG_ID) usr,
(select o.org_id, o.org_name, count(b.id) cnt from orgs o full outer join
(select a.id, a.org_id from users a where exists (select 1 from bindings b where a.ID = b.USERID)) b
on o.ORG_ID = b.ORG_ID
group by o.org_id, o.ORG_NAME) bind_user
where coalesce(usr.ORG_ID,0) = coalesce(bind_user.org_id,0);
We recommend you to use AAOP_Questions (AAOP_Questions@emc.com) forum for any such questions.
There is NO such "out-of-the-box" option available. However, one can directly query database to fetch this information at own risk. The query is fairly complex and will consume fair amount of DB resources (CPU, I/O and Memory) during the execution and therefore, peak traffic hours should be avoided. Also this query first should be tested in Dev and Testing Environments.
The output of these queries will be in the following format:
org_name | bind_user_count | org_user_count | perc_user
where
org_name: Organization name
bind_user_count: No. of users that are bound to at least 1 device
org_user_count: No. of users mapped against each organization
perc_user: % of users that are bound to at least 1 device
Query for Oracle Database:
select bind_user.org_name as org_name, bind_user.cnt as bind_user_count, usr.cnt as org_user_count,
(bind_user.cnt/usr.cnt) * 100 perc_user from
(select org_id, COUNT(1) cnt from users group by ORG_ID) usr,
(select o.org_id, o.org_name, count(b.id) cnt from orgs o full outer join
(select a.id, a.org_id from users a where exists (select 1 from bindings b where a.ID = b.USERID)) b
on o.ORG_ID = b.ORG_ID
group by o.org_id, o.ORG_NAME) bind_user
where nvl(usr.ORG_ID,0) = nvl(bind_user.org_id,0);
Query for SQL Server Database:
select bind_user.org_name as org_name, bind_user.cnt as bind_user_count, usr.cnt as org_user_count,
(cast(bind_user.cnt as float)/cast(usr.cnt as float)) * 100 perc_user from
(select org_id, COUNT(1) cnt from users group by ORG_ID) usr,
(select o.org_id, o.org_name, count(b.id) cnt from orgs o full outer join
(select a.id, a.org_id from users a where exists (select 1 from bindings b where a.ID = b.USERID)) b
on o.ORG_ID = b.ORG_ID
group by o.org_id, o.ORG_NAME) bind_user
where coalesce(usr.ORG_ID,0) = coalesce(bind_user.org_id,0);
We recommend you to use AAOP_Questions (AAOP_Questions@emc.com) forum for any such questions.