Hey Guys!
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:
|
1 2 3 4 5 6 7 8 |
SELECT MIN(TimeStart) AS Execucao_Mais_Antiga, MAX(TimeStart) AS Execucao_Mais_Nova FROM ReportServer..ExecutionLog3 WHERE RequestType = 'Interactive' AND ItemAction LIKE 'Render%' |
Result:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT A.[Path], COUNT(DISTINCT B.TimeStart) AS [Quantidade of 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
;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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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!
Portuguese 
English
Excelente artigo. Muito bem explicado. Obrigado!
Excelente artigo. Só que algo saiu errado aqui… Msg 547, Level 16, State 0, Line 51
The DELETE statement conflicted with the REFERENCE constraint “FK_DataSourceItemID”. The conflict occurred in database “ReportServer”, table “dbo.DataSource”, column ‘ItemID’.
Se alguém souber a causa pode ser útil e me poupar tempo, porque vou ter que excluir os menos usados aqui da base.
Solução usada: Excluí a FK, Deletei os relatórios, restaurei a FK. Feito.
USE [ReportServer]
GO
ALTER TABLE [dbo].[DataSource] DROP CONSTRAINT [FK_DataSourceItemID]
GO
–AQUI EXECUTA O SCRIPT DE DELETE
ALTER TABLE [dbo].[DataSource] WITH NOCHECK ADD CONSTRAINT [FK_DataSourceItemID] FOREIGN KEY([ItemID])
REFERENCES [dbo].[Catalog] ([ItemID])
GO
ALTER TABLE [dbo].[DataSource] CHECK CONSTRAINT [FK_DataSourceItemID]
GO