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')
*****************************************************************************/

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