¡Hola, chicos!
En este artículo, me gustaría compartir con usted cómo utilizar vistas y tablas de catálogo de SQL Server Reporting Services (SSRS) para identificar a qué informes se accede con mayor frecuencia y cuáles no. Esta necesidad terminó surgiendo mientras atendía a un cliente, que está en el proceso de migrar informes RDL de Reporting Services a Power BI Report Server y le gustaría aprovechar este movimiento para identificar y eliminar informes que ya no se utilizan.

Como ya mencioné en el post.  SQL Server Reporting Services (SSRS): cómo iniciar sesión para ver informes e identificar qué usuario está accediendo, podemos identificar ejecuciones de informes utilizando vistas de catálogo SSRS % de registro de ejecución, que devuelven diversa información al respecto, como fecha/hora de ejecución, usuario, parámetros utilizados y mucho más.

Esta información es registrada automáticamente, por defecto, por SSRS, que almacena, por defecto, los datos de ejecución de informes de los últimos 60 días, información que podemos consultar a través de Management Studio (SSMS), en la pantalla de propiedades de SSRS:

Para ver el rango actual de datos del historial de ejecución, puede utilizar esta consulta:

SELECT 
    MIN(TimeStart) AS Execucao_Mais_Antiga,
    MAX(TimeStart) AS Execucao_Mais_Nova
FROM 
    ReportServer..ExecutionLog3
WHERE
    RequestType = 'Interactive' 
    AND ItemAction LIKE 'Render%'

Resultado:

Tenga en cuenta que en el ejemplo anterior, la consulta generó solo 2 días de historial, aunque la configuración de almacenamiento está configurada como 60 días. Esto sucede porque no estuve consultando los informes en los últimos 60 días en mi VM y solo los consulté en los últimos 2 días para hacer esta publicación, entonces Tenga esto en cuenta al identificar informes no utilizados en su entorno..

Por eso, en este post rápido, sencillo y objetivo, me gustaría compartir contigo una consulta que te permita identificar qué informes son más accedidos y cuáles ya no se utilizan en tu servidor SQL Server Reporting Services (SSRS) o Power BI Report Server:

SELECT 
    A.[Path], 
    COUNT(DISTINCT B.TimeStart) AS [Quantidade de Views],
    MIN(B.TimeStart) AS Execucao_Mais_Antiga,
    MAX(B.TimeStart) AS Execucao_Mais_Nova
FROM 
    ReportServer..[Catalog] A
    LEFT JOIN ReportServer..ExecutionLog3 B ON A.[Path] = B.ItemPath AND B.RequestType = 'Interactive' AND B.ItemAction LIKE 'Render%'
WHERE 
    A.[Type] IN (2, 12)
GROUP BY 
    A.[Path]
ORDER BY 
    2 DESC

Resultado de la ejecución:

Quiero eliminar informes no utilizados

¿Qué sucede si desea eliminar informes no utilizados? Imagine que hay varios informes no utilizados y desea eliminarlos sin tener que abrir informe por informe. Compartiré un código T-SQL para hacer esto. ¡RECUERDA PROBAR ANTES!

Consulta para validar los archivos que se eliminarán:

;WITH relatorios_nao_utilizados AS (
    SELECT 
        *
    FROM 
        ReportServer..[Catalog] A
        LEFT JOIN ReportServer..ExecutionLog3 B ON A.[Path] = B.ItemPath AND B.RequestType = 'Interactive' AND B.ItemAction LIKE 'Render%'
    WHERE
        A.[Type] IN (2, 12, 13) -- Relatório paginado (RDL) / Relatório móvel / Relatório Power BI
        AND B.TimeStart IS NULL
)
SELECT 
    A.ItemID, 
    A.[Path],
    A.[Description],
    A.[Hidden],
    (CASE A.[Type]
        WHEN 1 THEN 'Diretório'
        WHEN 2 THEN 'Relatório Paginado (RDL)'
        WHEN 3 THEN 'Arquivo'
        WHEN 4 THEN 'Linked Report'
        WHEN 5 THEN 'Fonte de dados (Datasource)'
        WHEN 6 THEN 'Model'
        WHEN 7 THEN 'ReportPart'
        WHEN 8 THEN 'Conjunto de dados compartilhado (Shared Dataset)'
        WHEN 11 THEN 'KPI'
        WHEN 12 THEN 'Relatório do Mobile Report (antigo Datazen)'
        WHEN 13 THEN 'Relatório do Power BI'
    END) AS [Type],
    A.CreationDate,
    C.UserName AS CreatedBy,
    A.ModifiedDate,
    D.UserName AS ModifiedBy,
    A.ParentID
FROM
    ReportServer..[Catalog] A
    JOIN relatorios_nao_utilizados B ON A.ParentID = B.ItemID OR A.ItemID = B.ItemID
    JOIN ReportServer.dbo.Users C ON C.UserID = A.CreatedByID
    JOIN ReportServer.dbo.Users D ON D.UserID = A.ModifiedByID

UNION

SELECT 
    A.ItemID, 
    A.[Path],
    A.[Description],
    A.[Hidden],
    (CASE A.[Type]
        WHEN 1 THEN 'Diretório'
        WHEN 2 THEN 'Relatório Paginado (RDL)'
        WHEN 3 THEN 'Arquivo'
        WHEN 4 THEN 'Linked Report'
        WHEN 5 THEN 'Fonte de dados (Datasource)'
        WHEN 6 THEN 'Model'
        WHEN 7 THEN 'ReportPart'
        WHEN 8 THEN 'Conjunto de dados compartilhado (Shared Dataset)'
        WHEN 11 THEN 'KPI'
        WHEN 12 THEN 'Relatório do Mobile Report (antigo Datazen)'
        WHEN 13 THEN 'Relatório do Power BI'
    END) AS [Type],
    A.CreationDate,
    D.UserName AS CreatedBy,
    A.ModifiedDate,
    E.UserName AS ModifiedBy,
    B.ParentID
FROM
    ReportServer..[Catalog] A
    JOIN ReportServer..[Catalog] B ON A.ParentID = B.ItemID
    JOIN relatorios_nao_utilizados C ON B.ParentID = C.ItemID OR B.ItemID = C.ItemID
    JOIN ReportServer.dbo.Users D ON D.UserID = A.CreatedByID
    JOIN ReportServer.dbo.Users E ON E.UserID = A.ModifiedByID
ORDER BY
    A.ParentID,
    A.ItemID

Resultado de la consulta:

Lista de informes de mi servidor:

Recordando que los tipos disponibles son:
1 = Directorio
2 = Informe paginado (RDL)
3 = Archivo
4 = Informe vinculado
5 = fuente de datos
6 = Modelo
7 = Parte del informe
8 = Conjunto de datos compartido
11 = KPI
12 = Informe Informe Móvil (anteriormente Datazen)
13 = Informe de Power BI

¿Ha verificado los resultados de la consulta anterior, ha realizado una copia de seguridad de su base de datos de ReportServer y está listo para eliminar estos informes no utilizados? Ahora puede utilizar el siguiente script para eliminar estos informes:

DECLARE @Items TABLE (
    ItemID UNIQUEIDENTIFIER,
    [Path] NVARCHAR(255)
)

;WITH relatorios_nao_utilizados AS (
    SELECT 
        *
    FROM 
        ReportServer..[Catalog] A
        LEFT JOIN ReportServer..ExecutionLog3 B ON A.[Path] = B.ItemPath AND B.RequestType = 'Interactive' AND B.ItemAction LIKE 'Render%'
    WHERE
        A.[Type] IN (2, 12, 13) -- Relatório paginado (RDL) / Relatório móvel / Relatório Power BI
        AND B.TimeStart IS NULL
)
INSERT INTO @Items
SELECT 
    A.ItemID,
    A.[Path]
FROM
    ReportServer..[Catalog] A
    JOIN relatorios_nao_utilizados B ON A.ParentID = B.ItemID OR A.ItemID = B.ItemID
    JOIN ReportServer.dbo.Users C ON C.UserID = A.CreatedByID
    JOIN ReportServer.dbo.Users D ON D.UserID = A.ModifiedByID

UNION

SELECT 
    A.ItemID,
    A.[Path]
FROM
    ReportServer..[Catalog] A
    JOIN ReportServer..[Catalog] B ON A.ParentID = B.ItemID
    JOIN relatorios_nao_utilizados C ON B.ParentID = C.ItemID OR B.ItemID = C.ItemID
    JOIN ReportServer.dbo.Users D ON D.UserID = A.CreatedByID
    JOIN ReportServer.dbo.Users E ON E.UserID = A.ModifiedByID


-----------------------------------------------------------------------
-- CUIDADO! AQUI ESTAMOS APAGANDO OS SEUS RELATÓRIOS NÃO UTILIZADOS
-----------------------------------------------------------------------

-- Apaga o relacionamento do relatório com o conjunto de dados
DELETE A
FROM
    ReportServer.dbo.DataSets A
    JOIN @Items B ON B.ItemID = A.ItemID


-- Apaga os relatórios e arquivos relacionados 
DELETE A
FROM
    ReportServer.dbo.[Catalog] A
    JOIN @Items B ON B.ItemID = A.ItemID

Ahora consultemos nuestro servidor SQL Server Reporting Services (SSRS) o Power BI Report Server (PBIRS) y verifiquemos si los informes han sido eliminados:

¡Listo! Se eliminaron los informes no utilizados.
Una vez más: recuerde PROBAR y HACER UNA COPIA DE SEGURIDAD de la base de datos del servidor de informes antes de utilizar estos scripts.

¿Aún no conoces Power BI Report Server? ¿Quieres saber más sobre esta herramienta? No olvides revisar mi artículo. Vídeo – Power BI vs Reporting Services: ¿Quién es mejor?.

Espero que hayas disfrutado de esta publicación y ¡hasta luego!
¡Abrazo!