Skip navigation

Client Management

1 Post authored by: Alex Steinwachs

Gathering Data

Posted by Alex Steinwachs Aug 26, 2014
Share:|

Good Morning fellow Asset Core fans!

 

I was asked to gather results of three queries from customers. Can you run the following queries then private message me the results?

These queries will not make any changes to your asset core.

 

--Query #1
select count(*) as Count_Admin_Rights FROM
                        (
                            SELECT a.AdministratorID, a.ObjectTypeID, a.ObjectID, a.ReadAccess, a.WriteAccess, a.AssignAccess, a.DirectAccess, a.RemoteAccess
                            FROM AdminAssignedRights a
                            UNION ALL
                            SELECT DISTINCT m.MemberID AS AdministratorID, r.ObjectTypeID, r.ObjectID, r.ReadAccess, r.WriteAccess, r.AssignAccess, r.DirectAccess, r.RemoteAccess
                            FROM GroupMembers m, AdminGroupAssignedRights r
                            WHERE m.GroupTypeID = 103
                            AND r.AdminGroupID = m.GroupID
                        ) o

--Query #2
Select top 10 AdministratorID, count(AdministratorID) as Num_Duplicate_rows, ObjectID

FROM
                (
                    SELECT AdministratorID, ObjectTypeID, ObjectID
                    , CASE WHEN (MIN(ReadAccess) = 0) THEN 0
                        WHEN (MAX (ReadAccess) = 2) THEN 2
                        ELSE 1
                        END AS ReadAccess
                    , CASE WHEN (MIN(WriteAccess) = 0) THEN 0
                        WHEN (MAX (WriteAccess) = 2) THEN 2
                        ELSE 1
                        END AS WriteAccess
                    , CASE WHEN (MIN(AssignAccess) = 0) THEN 0
                        WHEN (MAX (AssignAccess) = 2) THEN 2
                        ELSE 1
                        END AS AssignAccess
                    , CASE WHEN (MIN(DirectAccess) = 0) THEN 0
                        WHEN (MAX (DirectAccess) = 2) THEN 2
                        ELSE 1
                        END AS DirectAccess
                    , CASE WHEN (MIN(RemoteAccess) = 0) THEN 0
                        WHEN (MAX (RemoteAccess) = 5) THEN 5
                        WHEN ( (MAX (RemoteAccess) = 4) AND (MIN (RemoteAccess) = 3)) THEN 5
                        WHEN (MAX (RemoteAccess) = 4) THEN 4 WHEN (MAX (RemoteAccess) = 3) THEN 3
                        WHEN (MAX (RemoteAccess) = 2) THEN 2
                        ELSE 1
                        END AS RemoteAccess
                    FROM
                        (
                            SELECT a.AdministratorID, a.ObjectTypeID, a.ObjectID, a.ReadAccess, a.WriteAccess, a.AssignAccess, a.DirectAccess, a.RemoteAccess
                            FROM AdminAssignedRights a
                          UNION ALL
                            SELECT DISTINCT m.MemberID AS AdministratorID, r.ObjectTypeID, r.ObjectID, r.ReadAccess, r.WriteAccess, r.AssignAccess, r.DirectAccess, r.RemoteAccess
                            FROM GroupMembers m, AdminGroupAssignedRights r
                            WHERE m.GroupTypeID = 103
                                AND r.AdminGroupID = m.GroupID

                        ) o
                    GROUP BY AdministratorID, ObjectID, ObjectTypeID
                ) v
    Group by AdministratorID, ObjectID, ReadAccess, WriteAccess, AssignAccess, DirectAccess, RemoteAccess
    Order by Num_Duplicate_rows desc

--Query #3
SELECT COUNT(*) FROM Administrators

 

Thank you!

Filter Blog

By date:
By tag: