Sunday, October 16, 2016

SSRS Reports Audit

The following T-SQL User Defined Functions running against Reporting Services database serve the following purposes

•        Post deployment audit of reports and data sets

•        Analysis of report dependencies across one or multiple reports
 
PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GO 
 
IF EXISTS
      (SELECT *
   FROM sys.objects
   WHERE name = 'Extract_Report_Info')

  DROP FUNCTION dbo.Extract_Report_Info
  PRINT 'DROP FUNCTION dbo.Extract_Report_Info'
GO

PRINT 'CREATE FUNCTION dbo.Extract_Report_Info'
GO

CREATE FUNCTION dbo.Extract_Report_Info(
/*****************************************************************************
Purpose: Extracts the contents of all Reports, Data Sources, Report Parts and Shared Datasets (Types 2,5,7, & 8) from the database

Change Log

 #    Date       Who  Change
******************************************************************************/
 @rptName NVARCHAR(255)
 ,@rptPath NVARCHAR(255))
RETURNS @tempTBL TABLE (
   ItemID NVARCHAR(255)
   ,Name NVARCHAR(255)
   ,[Path] NVARCHAR(MAX)
   ,[Type] NVARCHAR(255)
   ,TypeDescription NVARCHAR(MAX)
   ,Content varbinary(MAX)
   ,ContentVarchar NVARCHAR(MAX)
   ,ContentXML xml
)
AS
BEGIN

 --The first CTE gets the content AS a varbinary(MAX)
 --as well AS the other important columns for all reports,
 --data sources and shared datasets.

 WITH ItemContentBinaries AS
 (
  SELECT
  ItemID
  ,Name
  ,[Path]
  ,[Type]
  ,CASE Type
    WHEN 2 THEN 'Report'
    WHEN 5 THEN 'Data Source'
    WHEN 7 THEN 'Report Part'
    WHEN 8 THEN 'Shared Dataset'
    ELSE 'Other'
  END AS TypeDescription
  ,CONVERT(varbinary(MAX),Content) AS Content
  --FROM OPENROWSET('SQLNCLI', 'Server=COA-DARC-SQL05\DEV_INTEGRATION;Trusted_Connection=yes;',
  --    'SELECT * FROM ReportServer.dbo.Catalog')
  FROM ReportServer.dbo.Catalog
  WHERE Type IN (2,5,7,8)
 ),

 --The second CTE strips off the BOM if it exists...

 ItemContentNoBOM AS
 (
  SELECT
  ItemID
  ,Name
  ,[Path]
  ,[Type]
  ,TypeDescription
  ,CASE
    WHEN LEFT(Content,3) = 0xEFBBBF
   THEN CONVERT(varbinary(MAX),SUBSTRING(Content,4,LEN(Content)))
   ELSE
   Content
  END AS Content
  FROM ItemContentBinaries
 )

 --The outer query gets the content in its varbinary, varchar and xml representations...

 INSERT INTO @tempTBL
 SELECT
    ItemID
   ,Name
   ,[Path]
   ,[Type]
   ,TypeDescription
   ,Content           --varbinary
   ,CONVERT(varCHAR(MAX),Content) AS ContentVarchar --varchar
   ,CONVERT(xml,Content) AS ContentXML    --xml
 FROM ItemContentNoBOM
 WHERE (ISNULL(@rptName,'') = ''
  OR
  name LIKE '%' + @rptName + '%')
  AND
  (ISNULL(@rptPath,'') = ''
  OR
  [Path] LIKE '%' + @rptPath + '%')
 RETURN
END
GO
/*******************************************************************
Test Sample:
 SELECT * FROM dbo.Extract_Report_Info('{Report Name},'{Report Path}')
 SELECT * FROM dbo.Extract_Report_Info('{Report Name},',NULL)
 SELECT * FROM dbo.Extract_Report_Info(NULL,Null)
 ********************************************************************/
----------------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOPRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GOIF EXISTS
      (SELECT *
   FROM sys.objects
   WHERE name = 'Extract_Dataset_Commands')
  DROP FUNCTION dbo.Extract_Dataset_Commands
  PRINT 'DROP FUNCTION dbo.Extract_Dataset_Commands'
GOPRINT 'CREATE FUNCTION dbo.Extract_Dataset_Commands'
GOCREATE FUNCTION dbo.Extract_Dataset_Commands(
/****************************************************************************
Purpose : extracts the actual commands used for the each dataSET in each Report or Shared Dataset.Change Log
 #    Date       Who  Change
*****************************************************************************/
  @rptName NVARCHAR(255)
 ,@rptPath NVARCHAR(255)
 ,@cmdText NVARCHAR(255))
RETURNS @tempTBL TABLE ( ItemID NVARCHAR(255)
      ,Name NVARCHAR(255)
      ,[Path] NVARCHAR(MAX)
      ,[Type] NVARCHAR(255)
      ,TypeDescription NVARCHAR(MAX)
      ,ContentXML xml
      ,CommandType NVARCHAR(MAX)
      ,CommandText NVARCHAR(MAX)
)
AS
BEGIN
 --The first CTE gets the content AS a varbinary(MAX)
 --as well AS the other important columns for all reports,
 --data sources and shared datasets.
 WITH ItemContentBinaries AS
 (
  SELECT
  ItemID
  ,Name
  ,[Path]
  ,[Type]
  ,CASE Type
    WHEN 2 THEN 'Report'
    WHEN 5 THEN 'Data Source'
    WHEN 7 THEN 'Report Part'
    WHEN 8 THEN 'Shared Dataset'
    ELSE 'Other'
  END AS TypeDescription
  ,CONVERT(varbinary(MAX),Content) AS Content
  --FROM OPENROWSET('SQLNCLI', 'Server=COA-DARC-SQL05\DEV_INTEGRATION;Trusted_Connection=yes;',
   --   'SELECT * FROM ReportServer.dbo.Catalog')
  FROM ReportServer.dbo.Catalog
  WHERE Type IN (2,5,7,8)
 ),
 --The second CTE strips off the BOM if it exists...
 ItemContentNoBOM AS
 (
  SELECT
  ItemID
  ,Name
  ,[Path]
  ,[Type]
  ,TypeDescription
  ,CASE
    WHEN LEFT(Content,3) = 0xEFBBBF
   THEN CONVERT(varbinary(MAX),SUBSTRING(Content,4,LEN(Content)))
    ELSE
   Content
  END AS Content
  FROM ItemContentBinaries
 )
 --The old outer query is now a CTE to get the content in its xml form only...
 ,ItemContentXML AS
 (
  SELECT  ItemID
   ,Name
   ,[Path]
   ,[Type]
   ,TypeDescription
   ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
 )
 --now use the XML data type to extract the queries, and their command types and text....
 INSERT INTO @tempTBL
 SELECT
   ItemID
  ,Name
  ,[Path]
  ,[Type]
  ,TypeDescription
  ,ContentXML
  ,ISNULL(Query.value('(./*:CommandType/text())[1]','NVARCHAR(1024)'),'Query') AS CommandType
  ,Query.value('(./*:CommandText/text())[1]','NVARCHAR(MAX)') AS CommandText
 FROM ItemContentXML
 --Get all the Query elements (The "*:" ignores any xml namespaces)
 CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)
 WHERE (ISNULL(@rptName,'') = ''
  OR
  name LIKE '%' + @rptName + '%')
  AND
  (ISNULL(@rptPath,'') = ''
  OR
  [Path] LIKE '%' + @rptPath + '%')
  AND
  (ISNULL(@cmdText,'') = ''
  OR
  Query.value('(./*:CommandText/text())[1]','NVARCHAR(MAX)') LIKE '%' + @cmdText + '%')
 RETURN
END
GO
/*****************************************************************************
Test Sample:
 SELECT * FROM dbo.Extract_Dataset_Commands(‘{Report Name}','{Report Path}',NULL)
 SELECT * FROM dbo.Extract_Dataset_Commands('{Report Name}',NULL, 'select')
 SELECT * FROM dbo.Extract_Dataset_Commands(NULL,'{Report Path}’','SELECT')
*****************************************************************************/

 SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOPRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GOIF EXISTS(    SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
            WHERE VIEW_DEFINITION LIKE '%dbo.Shared_Data_Sources%')
BEGIN
    DROP VIEW dbo.Shared_Data_Sources
    PRINT 'DROP VIEW dbo.Shared_Data_Sources'
END
GOPRINT 'CREATE VIEW dbo.Shared_Data_Sources'
GO/************************************************************************************************************************************
Reports Audit - Extract Shared Data SourcesChange Log
 #    Date       Who  Change
*************************************************************************************************************************************/
CREATE VIEW dbo.Shared_Data_Sources
AS
    WITH XMLNAMESPACES
        -- XML namespace def must be the first in with clause.
        (
        DEFAULT
        'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
        ,
        'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
        AS rd
        )
        ,SDS
    AS (
        SELECT SDS.NAME AS SharedDsName
            ,SDS.[Path]
            ,CONVERT(XML, CONVERT(VARBINARY(max), content)) AS DEF
        FROM dbo.[Catalog] AS SDS
        WHERE SDS.Type = 5
        ) -- 5 = Shared Datasource
    SELECT CON.[Path]
        ,CON.SharedDsName
        ,CON.ConnString
    FROM (
        SELECT SDS.[Path]
            ,SDS.SharedDsName
            ,DSN.value('ConnectString[1]', 'varchar(150)') AS
            ConnString
        FROM SDS
        CROSS APPLY SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN
            )
        ) AS CON
GOSET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOPRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GOIF EXISTS(    SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
            WHERE VIEW_DEFINITION LIKE '%dbo.Report_Data_Sources%')
BEGIN
    DROP VIEW dbo.Report_Data_Sources
    PRINT 'DROP VIEW dbo.Report_Data_Sources'
END
GOPRINT 'CREATE VIEW dbo.Report_Data_Sources'
GO/************************************************************************************************************************************
Reports Audit - Extract Report Data SourcesChange Log
 #    Date       Who  Change
*************************************************************************************************************************************/
CREATE VIEW dbo.Report_Data_Sources
AS
SELECT   C.ItemID
        ,C.Name
        ,C.Path
        ,C.Type
        ,CASE C.Type  WHEN 2 THEN 'Report'
                    WHEN 5 THEN 'Data Source'
                    WHEN 7 THEN 'Report Part'
                    WHEN 8 THEN 'Shared Dataset'
                    ELSE 'Other' END AS TypeDescription
        , CONVERT(varbinary(MAX), C.[Content]) AS Content
        , D.Name DataSourceName
FROM     dbo.Catalog C LEFT JOIN
         dbo.DataSource D
         ON C.ItemID = D.ItemID
WHERE  (C.Type IN (2, 5, 7, 8))
GO

It allows:·        Extract from shared data source the connection string for each report
SELECT R.ItemID
             ,R.Name
             ,R.Path
             ,R.Type
             ,R.TypeDescription
             ,R.[Content]
             ,R.DataSourceName
FROM     dbo.Report_Data_Sources R LEFT OUTER JOIN
         dbo.Shared_Data_Sources S
             ON R.DataSourceName = S.SharedDsName
WHERE R.Name = '{Report Name}'·        Identify reports which have no shared data sources attachedSELECT R.ItemID
             ,R.Name
             ,R.Path
             ,R.Type
             ,R.TypeDescription
             ,R.[Content]
             ,R.DataSourceName
FROM     dbo.Report_Data_Sources R LEFT OUTER JOIN
         dbo.Shared_Data_Sources S
             ON R.DataSourceName = S.SharedDsName
WHERE DataSourceName IS NULL

The above udf’s can be executed from the following PowerShell Script: #Query a SQL database
 $SQLServer = "DB\INSTANCE";
 $SQLDBName = "ReportServer";
 $rptName = "Report Name";
 $rptPath = "Report Path";
 $SqlQuery = "SELECT * FROM [dbo].[Extract_Report_Info]('" + $rptName + "'," + "'" + $rptPath + "');";
 $smtp = "smtp address";
 $From = "noreply email address";
 $To = "destination email address";
 $connectionName = "Extract_RS_Info";
 $commandTimeout = 15; clear;
 $SqlQuery; Function Extract_RS_Info {  Param (  $server = $SQLServer
    ,$database = $SQLDBName
    ,$smtp
    ,$From
    ,$To
    ,$rptName
    ,$rptPath
    ,$SqlQuery
    ,$connectionName
    ,$commandTimeout)
  Try{
   $conn =new-object ('System.Data.SqlClient.SqlConnection')
   $connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName"
   $conn.ConnectionString = $connString
   Write-Debug ("Function: Query-SQL: $server $database")
   if (test-path variable:\conn) {
    $conn.close()
   } else {
    $conn =new-object ('System.Data.SqlClient.SqlConnection')
   }
   $conn.Open()
   $sqlCmd =New-Object System.Data.SqlClient.SqlCommand
   $sqlCmd.CommandTimeout = $CommandTimeout
   $sqlCmd.CommandText = $SqlQuery
   $sqlCmd.Connection = $conn
   $data = $sqlCmd.ExecuteReader()
   while ($data.read() -eq $true) {
    $max = $data.FieldCount -1
    $obj =New-Object Object
    For ($i = 0; $i -le $max; $i++) {
     $name = $data.GetName($i)
     if ($name.length -eq 0) {
     $name = "field$i"
    }
    $obj |Add-Member Noteproperty $name -value $data.GetValue($i) -Force
   }
   $obj
   }
   $conn.close()
   $conn = $null
  } Catch {
   $ErrorMessage = $_.Exception.Message;
   $FailedItem = $_.Exception.ItemName;
   $body = "We failed to read file $FailedItem. The error message was $ErrorMessage";
   Send-MailMessage -From $From -To $To -Subject "Extract_RS_Info.ps1 failed!" -SmtpServer $smtp -Body $body;
   Break
  }
 } Extract_RS_Info $SQLServer $SQLDBName $smtp $From $To $rptName $rptPath $SqlQuery $connectionName $commandTimeout; $str = "*" * 80;
 write-host $str;
 $SqlQuery = "SELECT * FROM [dbo].[Extract_Dataset_Commands]('" + $rptName + "','" + $rptPath + "','" + $cmdText + "');";
 $SqlQuery; Extract_RS_Info $SQLServer $SQLDBName $smtp $From $To $rptName $rptPath $SqlQuery $connectionName $commandTimeout;
 
If you find this script useful please donate generously.

No comments:

Post a Comment