https://community.microstrategy.com/s/article/KB16048-List-of-all-object-types-and-object-descriptions-in
https://community.microstrategy.com/s/question/0D54400004zY1HdCAK/query-to-get-all-users-in-a-group-from-metadata
http://bipostit.com/en/2014/12/28/queries-metadata-i/
https://mstrtechpub.blogspot.com/2012/01/useful-metadata-queries.html
http://www.infocepts.com/pdf/Solutions/BI-Application-Management-and-Optimization/Key_Metrics_for_Microstrategy_Administrators.pdf
https://it.toolbox.com/question/how-to-get-complete-list-of-the-mstr-metrics-reports-071706
How can we list the projects?
SELECT OBJECT_NAME AS Projektname
FROM DSSMDOBJINFO Proj
WHERE OBJECT_TYPE = 32
How can we see the number of objects by project?
SELECT C.OBJECT_NAME AS PROJECT_NAME,
OP.COUNT_OBJETOS_PUBLICOS,
OE.COUNT_OBJETOS_DE_ESQUEMA
FROM ( SELECT PROJECT_ID,
COUNT (OBJECT_ID) AS COUNT_OBJETOS_DE_ESQUEMA
FROM DSSMDOBJINFO
WHERE OBJECT_TYPE IN (14, 12, 13, 11, 15, 43)
AND SUBTYPE IN (3585, 3072, 3328, 2816, 3840, 11009)
GROUP BY PROJECT_ID) OE
JOIN ( SELECT PROJECT_ID,
COUNT (OBJECT_ID) AS COUNT_OBJETOS_PUBLICOS
FROM DSSMDOBJINFO
WHERE OBJECT_TYPE IN (3, 55, 6, 47, 1, 56, 1, 4, 10, 39, 2)
AND SUBTYPE IN
(14081,
1536,
12032,
257,
14336,
256,
1024,
2556,
2544,
9984,
512,
768,
769,
770,
774,
777,
776)
GROUP BY PROJECT_ID) OP
ON OE.PROJECT_ID = OP.PROJECT_ID
JOIN DSSMDOBJINFO C
ON C.OBJECT_ID = OE.PROJECT_ID
ORDER BY 1;
How can we determine the objects that were modified in the last 7 days?
SELECT DISTINCT B.OBJECT_NAME AS NOMBRE,
A.USER_ID,
C.OBJECT_NAME AS CAMBIADO_POR,
D.CREATE_TIME AS F_CREACION,
D.MOD_TIME AS F_MODIFICACION
FROM DSSMDJRNINFO A
JOIN DSSMDJRNOBJD B
ON A.TRANSACTION_ID = B.TRANSACTION_ID
JOIN DSSMDOBJINFO C
ON A.USER_ID = C.OBJECT_ID
JOIN DSSMDOBJINFO D
ON B.OBJECT_NAME = D.OBJECT_NAME
AND (SYSDATE - 7) <= (D.MOD_TIME - 0);
How can we determine the list of objects in a folder?
To determine a list of objects in a folder, for example: in ‘MY REPORTS’:
SELECT C.OBJECT_NAME AS USUARIO,
D.OBJECT_NAME AS NOMBRE_PROYECTO,
A.OBJECT_NAME AS NOMBRE_OBJETO,
B.OBJECT_NAME AS CARPETA
FROM (SELECT *
FROM DSSMDOBJINFO
WHERE PARENT_ID IN
(SELECT OBJECT_ID
FROM DSSMDOBJINFO
WHERE OBJECT_TYPE = 8
AND SUBTYPE = 2048
AND OBJECT_NAME LIKE 'MY REPORTS')) A
JOIN DSSMDOBJINFO B
ON A.PARENT_ID = B.OBJECT_ID
JOIN DSSMDOBJINFO C
ON B.PARENT_ID = C.OBJECT_ID
JOIN DSSMDOBJINFO D
ON C.PROJECT_ID = D.OBJECT_ID
ORDER BY 1;
Which table contain the LOGIN ID?
SELECT g.OBJECT_NAME as, u.OBJECT_NAME as
FROM DSSMDOBJDEPN d
JOIN DSSMDOBJINFO u
ON d.OBJECT_ID = u.OBJECT_ID
JOIN DSSMDOBJINFO g
ON d.DEPN_OBJID = g.OBJECT_ID
WHERE u.OBJECT_TYPE = 34
AND u.SUBTYPE = 8704 --Users
AND g.OBJECT_TYPE = 34
AND g.SUBTYPE = 8705 --Groups
How can we find disabled users?
SELECT info.OBJECT_NAME, def.*
FROM DSSMDOBJDEF2 def
INNER JOIN
DSSMDOBJINFO info
ON (def.OBJECT_ID = info.OBJECT_ID)
WHERE object_type = 34 AND subtype = 8704 AND DEFINITION LIKE '%3JAE%'
How can we retrieve a list of users that ran report during a certain time window?
SELECT A.OBJECT_NAME AS USER_NAME, SUM (B.NUM_JOBS_EXEC) JOBS_EXECUTED
FROM MICROSTRATEGY.DSSMDOBJINFO A
JOIN
( SELECT A1.USERID AS USERID, COUNT (REPORTID) AS NUM_JOBS_EXEC
FROM ESTADISTICA.IS_REPORT_STATS A1
JOIN
ESTADISTICA.IS_SESSION_STATS A2
ON (A1.SESSIONID = A2.SESSIONID)
WHERE ROUND (SYSDATE - TRUNC (A1.DAY_ID)) <= 1
GROUP BY A1.USERID, A2.SESSIONID
ORDER BY 2 DESC) B
ON (A.OBJECT_ID = B.USERID)
WHERE A.OBJECT_TYPE = 34
GROUP BY A.OBJECT_NAME
ORDER BY 2 DESC;
The above code utilize both the metadata database and the statistics database.
How can we make a list of reports / documents with or without faults, executed over one period of time?
We can make a list of reports / documents with or without faults, executed over one period of time, for example in the last 24 hours:
SELECT TIPO, SUM (OK) AS NUM_EXEC_OK, SUM (KO) AS NUM_EXEC_KO
FROM (SELECT ‘INFORME’ AS TIPO,
CASE WHEN A.JOBSTATUS = 4 THEN 1 ELSE 0 END AS KO,
CASE WHEN JOBSTATUS = 3 THEN 1 ELSE 0 END AS OK
FROM ESTADISTICA.IS_REPORT_STATS A
WHERE ROUND (SYSDATE - TRUNC (A.DAY_ID)) <= 1
AND A.JOBSTATUS IN (3, 4)
UNION ALL
SELECT ‘DOCUMENTO’ AS TIPO,
CASE WHEN B.EXECSTATUS = 4 THEN 1 ELSE 0 END AS KO,
CASE WHEN EXECSTATUS = 3 THEN 1 ELSE 0 END AS OK
FROM ESTADISTICA.IS_DOCUMENT_STATS B
WHERE ROUND (SYSDATE - TRUNC (B.DAY_ID)) <= 1
AND B.EXECSTATUS IN (3, 4))
GROUP BY TIPO;
How can we find error message in the reports which have failed in a given period?
We can find error message in the reports which have failed in a given period, for example, in the last 24 hours:
SELECT D.OBJECT_UNAME AS PROYECTO,
C.OBJECT_UNAME AS NOMBRE_INFORME,
B.OBJECT_UNAME AS USUARIO,
A.ERRORMESSAGE AS MENSAJE_ERROR,
MAX (A.RECORDTIME) HORA_FIN,
MAX (A.REQUESTRECTIME) HORA_INICIO
FROM ESTADISTICA.IS_REPORT_STATS A
JOIN MICROSTRATEGY.DSSMDOBJINFO B
ON (B.OBJECT_ID = A.USERID)
JOIN MICROSTRATEGY.DSSMDOBJINFO C
ON (C.OBJECT_ID = A.REPORTID)
JOIN MICROSTRATEGY.DSSMDOBJINFO D
ON (D.OBJECT_ID = A.PROJECTID)
WHERE A.JOBERRORCODE <> 0
AND UPPER (A.ERRORMESSAGE) NOT LIKE '%CANCELED%'
AND UPPER (A.ERRORMESSAGE) NOT LIKE '%CANCELADO%'
AND (ROUND (SYSDATE - TRUNC (A.RECORDTIME)) >= 1)
GROUP BY D.OBJECT_UNAME,
C.OBJECT_UNAME,
B.OBJECT_UNAME,
A.SERVERMACHINE,
A.ERRORMESSAGE;
How can we get the user and group details excluding the group Everyone?
SELECT GROUPNAME.object_name Customer_group, USERNAME.object_name User_name
FROM DSSMDOBJINFO GROUPNAME
JOIN
(SELECT object_name, OBJDEPN.depn_objid
FROM DSSMDOBJINFO b
JOIN
(SELECT DISTINCT Object_id, depn_objid
FROM DSSMDOBJDEPN
WHERE object_id IN (SELECT object_id
FROM DSSMDUSRACCT
WHERE isgroup = 0)
AND depn_objid <>
(SELECT object_id
FROM DSSMDOBJINFO
WHERE object_name = 'Everyone')) OBJDEPN
ON OBJDEPN.object_id = b.object_id) USERNAME
ON USERNAME.depn_objid = GROUPNAME.object_id
WHERE GROUPNAME.object_name <> 'Everyone'
ORDER BY GROUPNAME.object_name
How can we get a list of users?
select count (distinct login) from DSSMDUSRACCT where isgroup=0;
How can we list all object which is having access to particular group?
SELECT OBJECT_ID, OBJECT_TYPE
FROM DSSMDOBJSECU
WHERE OBJECT_ID IN
(SELECT OBJECT_ID
FROM DSSMDOBJSECU
WHERE TRUST_ID = (SELECT OBJECT_ID
FROM DSSMDOBJINFO
WHERE OBJECT_NAME LIKE ('USER GROUP'))
AND PROJECT_ID = 'PROJECT ID')
