Wednesday, 19 October 2016

Auditoría en Oracle Discoverer

Otro post técnico, en esta ocasión sobre Oracle Discoverer (Oracle Business Intelligence SE One) y cómo conocer, entre otras cosas, qué usuarios trabajan sobre qué libros, con qué frecuencia usan la herramienta, cuando fue usado por última vez un libro o el uso de la aplicación que cada semana hacen los usuarios.

Oracle guarda en las tablas del esquema propietario de la EUL toda la información relativa a los documentos o informes a ejecutar, los datos a utilizar por la capa de presentación y mucha otra información. Estos datos son usados por Oracle Discoverer para hacer una predicción de los queries que el usuario va a ejecutar y poder anticipar los datos a traer de la base de datos para dejarlos en caché por si los solicitase.

En la tabla eul?_qpp_stats se guarda esta información de predicción y es la que se usa en estos ejemplos para ejecutar una auditoría.


Auditoría por usuarios

Como ejemplo, la siguiente consulta muestra el nombre de todos los usuarios que han hecho uso de la aplicación, la fecha de la última vez que la usaron, el número total de veces que la han usado y la media de días entre cada uso:

SELECT 
       stats.qs_created_by AS "Usuario",
       TRUNC (MAX (stats.qs_created_date)) AS "Fecha último uso",
       COUNT (stats.qs_created_date) AS "Nº de veces que se ha usado",
       TO_CHAR ((MAX (stats.qs_created_date) - MIN (stats.qs_created_date)) / COUNT (stats.qs_created_date), '99990.00' ) AS "Media de días entre cada uso"

FROM eul5_qpp_stats stats
GROUP BY (stats.qs_created_by)
ORDER BY MAX (stats.qs_created_date) DESC


Auditoría por documentos

Con esta otra consulta SQL es posible obtener un listado de los informes/reports creados por los usuarios y guardados en la base de datos, el nombre del usuario que los creó y la fecha de la última vez que fue ejecutado el documento (los informes sin estas fechas son informes que nunca fueron lanzados después de guardar):

SELECT DISTINCT
       DOCS.DOC_CREATED_BY AS "Usuario",
       DOCS.DOC_NAME as "Informe",
       (SELECT TRUNC(MAX(DM.QS_CREATED_DATE)) 
        FROM EUL5_QPP_STATS DM
        WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME) as "Última ejecución"
FROM EUL5_QPP_STATS STATS, EUL5_DOCUMENTS DOCS
WHERE DOCS.DOC_NAME = STATS.QS_DOC_NAME(+)
GROUP BY DOCS.DOC_CREATED_BY, DOCS.DOC_NAME, STATS.QS_DOC_NAME
ORDER BY "Última ejecución" DESC, DOCS.DOC_CREATED_BY, DOCS.D
OC_NAME;

Auditoría por semanas


En esta consulta se obtiene la lista de usuarios que usaron la aplicación cada semana y el número de veces que lo hicieron. Para cada usuario se muestra:
  • Año al que corresponde la semana.
  • Fecha del lunes de esa semana.
  • Fecha del domingo de esa semana.
  • Usuario que usó la aplicación esa semana.
  • Número de veces que ese usuario usó la aplicación en la semana.
La consulta sería la siguiente:

SELECT
       TO_CHAR (stats.qs_created_date, 'yyyy') "Año",
       TRUNC (stats.qs_created_date, 'day') "Lunes",
       TRUNC (stats.qs_created_date, 'day') + 6 "Domingo"
       u.nombre "Usuario",
       COUNT (stats.qs_created_date) "Nº de usos" 
FROM eul5_qpp_stats stats, usuarios u
GROUP BY 
       TO_CHAR (stats.qs_created_date, 'yyyy'),
       TRUNC (stats.qs_created_date, 'day'),
       TRUNC (stats.qs_created_date, 'day') + 6,
       u.nombre
ORDER BY 
       TO_CHAR (stats.qs_created_date, 'yyyy') DESC,
       TRUNC (stats.qs_created_date, 'day') DESC,
       TRUNC (stats.qs_created_date, 'day') + 6 DESC,
       u.nombre ASC 

No comments:

Post a Comment