Metadata analysis examples

User information

All users and group names

User Lists : igcusers
Group Lists : igcdepts

SELECT
   n12.dname, n10.uid, n10.userid, n10.uname
FROM
   igcusers n10
   INNER JOIN igcdepts n12 ON n10.gid=n12.gid
ORDER BY n12.dname

 

User lists under group

User Lists : igcusers
Group Lists : igcdepts

SELECT
   n12.dname, n10.uid, n10.userid, n10.uname
FROM
   igcusers n10
   INNER JOIN igcdepts n12 ON n10.gid=n12.gid
WHERE
   n12.node LIKE (SELECT CONCAT(node, '%') FROM igcdepts WHERE dname='RootGroup')

 

Roles of user

User lists : igcusers
Roles  : igcduty
Mapping between user and roles : igcuserduty

SELECT
   n10.uid, n10.userid, n10.uname, n12.dname
FROM
   igcusers n10
   INNER JOIN igcuserduty n11 ON n10.uid=n11.uid
   INNER JOIN igcduty n12 ON n11.sid=n12.sid
ORDER BY n10.userid

 

Meta contents information

Lists of all reports that user can access

Contents : igcms
Contents * Role mapping : igcobjectduty
User * Role mapping : igcuserduty
List of users : igcusers

SELECT DISTINCT n10.uid, n10.cname, n11.writable, n11.manage 
FROM igcms n10   
   INNER JOIN igcobjectauth n11 ON n10.uid=n11.uid
   INNER JOIN igcuserduty n12 ON n11.sid=n12.sid  *
   INNER JOIN igcusers n20 ON n20.uid=n12.uid
WHERE
   n10.pstatus=1
   and n10.mctype IN (12)
   and n20.userid='demo';

 

Lists of reports that group can access

SELECT DISTINCT n10.uid, n10.cname, n11.writable, n11.manage
FROM igcms n10   
   INNER JOIN igcobjectauth n11 ON n10.uid=n11.uid
   INNER JOIN igcduty n12 ON n11.uid=n12.sid
WHERE
   n10.pstatus=1
   and n10.mctype IN (12)      
   and n12.dname='EveryOne';

 

All folder structure

Contents : igcms
Contents Role mapping : igcobjectauth
Roles : igcduty

SELECT DISTINCT n10.uid, n10.cname, n10.node, n10.mctype
FROM igcms n10
INNER JOIN igcobjectauth n11 ON n10.uid=n11.uid
INNER JOIN igcduty n12 ON n12.sid=n11.sid
WHERE
          n10.pstatus=1 AND
          n10.mctype IN (0, 1, 12) AND
          n12.dname='EveryOne'
ORDER BY n10.node;

 

Folder hierarchy lists

Top folders

SELECT n10.uid, n10.pid, n10.cname, n10.mctype
FROM igcms n10
          INNER JOIN igcobjectauth n11 ON n10.uid=n11.uid
          INNER JOIN igcduty n12 ON n12.sid=n11.sid
WHERE
          n10.pstatus=1 AND
          n10.mctype=0 AND n10.pid = n10.uid AND
          n12.dname='EveryOne';

 

Sub contents of folder using pid

SELECT n10.uid, n10.pid, n10.cname, n10.mctype
FROM igcms n10
            INNER JOIN igcobjectauth n11 ON n10.uid=n11.uid
            INNER JOIN igcduty n12 ON n12.sid=n11.sid
WHERE
            n10.pstatus=1 AND
            n10.mctype IN (0, 1, 12) AND
            n10.pid='013128fc-01339b91' AND n10.pid != n10.uid AND
            n12.dname='EveryOne';

 

Lists of System History

Version and Patch information

SELECT pname, content, mdate
FROM
   igcserver