Olá pessoal!
Nesse artigo, eu gostaria de compartilhar com vocês como utilizar views e tabelas de catálogo do SQL Server Reporting Services (SSRS) para identificar quais os relatórios mais acessados e quais não estão sendo utilizados. Essa necessidade acabou surgindo pra mim durante o atendimento a um cliente, que está em processo de migração de relatórios RDL do Reporting Services para o Power BI Report Server e ele gostaria de aproveitar esse movimento para identificar e remover os relatórios que não estão sendo mais utilizados.

Conforme eu já havia comentado no post SQL Server Reporting Services (SSRS) – Como logar a visualização dos relatórios e identificar qual usuário está acessando, podemos identificar as execuções dos relatórios utilizando as views de catálogo do SSRS ExecutionLog%, que retornam várias informações sobre isso, como data/hora da execução, usuário, parâmetros utilizados e muito mais.

Essas informações são gravadas automaticamente, por padrão, pelo SSRS, que armazena por padrão, os dados de execução de relatórios dos últimos 60 dias, informação a qual podemos conferir pelo Management Studio (SSMS), na tela de propriedades do SSRS:

Para visualizar qual o intervalo atual dos dados do histórico de execução, você pode utilizar essa 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:

Reparem que no exemplo acima, a consulta trouxe apenas 2 dias de histórico, mesmo que a configuração de armazenamento esteja configurada como 60 dias. Isso acontece porque eu não estava consultando os relatórios nos últimos 60 dias na minha VM e só consultei nos últimos 2 dias para fazer esse post, então fique atento quanto à isso quando for identificar relatórios não utilizados em seu ambiente.

Sendo assim, nesse post rápido, simples e objetivo, gostaria de compartilhar com vocês uma consulta que permite identificar quais os relatórios mais acessados e quais não estão sendo mais utilizados no seu servidor de SQL Server Reporting Services (SSRS) ou 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 da execução:

Quero apagar os relatórios não utilizados

E se você quiser apagar os relatórios não utilizados? Imaginem que existem vários relatórios não utilizados e você queira removê-los sem ter que abrir relatório por relatório. Vou compartilhar um código T-SQL para fazer isso. LEMBREM-SE DE TESTAR ANTES!

Consulta para validar os arquivos que serão excluídos:

;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 da consulta:

Lista dos relatórios do meu servidor:

Lembrando que os tipos disponíveis são:
1 = Diretório
2 = Relatório Paginado (RDL)
3 = Arquivo
4 = Linked Report
5 = Fonte de dados (Datasource)
6 = Model
7 = ReportPart
8 = Conjunto de dados compartilhado (Shared Dataset)
11 = KPI
12 = Relatório do Mobile Report (antigo Datazen)
13 = Relatório do Power BI

Conferiu o resultado da consulta acima, fez um backup da sua base do ReportServer e está pronto para apagar esses relatórios não utilizados ? Agora você pode utilizar o script abaixo para apagar esses relatórios:

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

Agora vamos consultar o nosso servidor do SQL Server Reporting Services (SSRS) ou Power BI Report Server (PBIRS) e conferir se os relatórios foram apagados:

Pronto! Relatórios não utilizados apagados.
Mais uma vez: lembre-se de TESTAR e fazer um BACKUP do database do Report Server antes de utilizar esses scripts.

Você ainda não conhece o Power BI Report Server? Gostaria de saber mais sobre essa ferramenta ? Não deixe de conferir o meu artigo Vídeo – Power BI vs Reporting Services: Quem é melhor?.

Espero que tenham gostado desse post e até mais!
Abraço!