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
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GO

IF EXISTS
      (SELECT *
 FROM sys.objects
 WHERE name = 'Extract_Dataset_Commands')
DROP FUNCTION dbo.Extract_Dataset_Commands
PRINT 'DROP FUNCTION dbo.Extract_Dataset_Commands'
GO

PRINT 'CREATE FUNCTION dbo.Extract_Dataset_Commands'
GO

CREATE 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
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GO

IF 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
GO

PRINT 'CREATE VIEW dbo.Shared_Data_Sources'
GO

/************************************************************************************************************************************
Reports Audit - Extract Shared Data Sources

Change 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
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GO

IF 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
GO

PRINT 'CREATE VIEW dbo.Report_Data_Sources'
GO

/************************************************************************************************************************************
Reports Audit - Extract Report Data Sources

Change 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 attached

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 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;


No comments:

Post a Comment