Skip to main content

Export SQL Reporting Services Execution Log to CSV

We recently needed to export the SQL Server Reporting Services execution log so we can audit usage of our reporting services environment.

I just love how PowerShell makes this easy to do.

[Export-SSRSExecutionLog3.ps1]
  1. param(  
  2.     [string]   $ConnectionString = 'Server=(local);Database=ReportServer;Integrated Security=SSPI;',  
  3.     [string]   $CommandText      = 'SELECT * FROM dbo.ExecutionLog3 WHERE TimeStart BETWEEN ''{0:MM/dd/yyyy} 12:00:00 AM'' AND ''{0:MM/dd/yyyy} 11:59:59 PM''',  
  4.     [string]   $Path             = "$($ENV:WINDIR)\system32\LogFiles\SQL",  
  5.     [string]   $FileName         = 'ReportServer-ExecutionLog3-{0:yyyyMMdd}.csv',  
  6.     [DateTime] $Date             = [DateTime]::Now.Subtract([TimeSpan]::FromDays(1))  
  7. )  
  8.   
  9. begin {  
  10.     $ErrorActionPreference = 'Stop'  
  11.       
  12.     $connection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString  
  13.     $connection.Open()  
  14. }  
  15.   
  16. process {  
  17.     $adapter = New-Object System.Data.SqlClient.SqlDataAdapter  
  18.     $adapter.SelectCommand = $connection.CreateCommand()  
  19.     $adapter.SelectCommand.CommandText = $CommandText -f $Date  
  20.       
  21.     $ds = New-Object System.Data.DataSet  
  22.     $adapter.Fill($ds)  
  23.       
  24.     if (!(Test-Path -Path $Path -PathType Container)) {  
  25.         New-Item -Path $Path -ItemType Container | Out-Null  
  26.     }  
  27.       
  28.     $fullPath = Join-Path $Path ($FileName -f $Date)  
  29.       
  30.     if (Test-Path -Path $fullPath -PathType Leaf) {  
  31.         Remove-Item -Path $fullPath -Force -Confirm:$false  
  32.     }  
  33.       
  34.     $ds.Tables[0] | Export-Csv -Path $fullPath -Encoding ASCII -NoClobber -NoTypeInformation -Force  
  35. }  
  36.   
  37. end {  
  38.     $connection.Close()  
  39. }  

Comments

Popular posts from this blog

PowerShell SupportsShouldProcess Worst & Best Practices

This has been a very big discussion within the Scripting Games 2013 community and I want to add my two cents in an official blog post.

I've left several people comments on how they might be misunderstanding how SupportsShouldProcess works, but I also realize, everyone of these individuals has given me more insight into its use and perhaps, how it should best be utilized.

For those of you that don't know, SupportsShouldProcess is a parameter on the CmdletBinding attribute you can place on your cmdlets that automatically adds the -WhatIf and -Confirm parameters. These will naturally flow into other cmdlets you use that also SupportsShouldProcess, e.g. New-Item, Move-Item.

The major discussion has been around, should you just let the other cmdlets handle the $PSCmdlet.ShouldProcess feature, and if not how should you implement it. ShouldProcess has the following definitions.
OverloadDefinitions�����������������������������������������������������������������������������������������…

PowerShell Error Handling Behavior Debunked

Note: I am using simple error messages as an example, please reference the best practices and guidelines I outlined on when to use custom error messages.

I have been churning in my mind for the last few days all the entries in the 2013 Scripting Games and how they handle errors, or lack thereof.

I am coming to the conclusion through some testing that the simple fact of seeing a try..catch or throw statements does not mean there is proper error handling.

I've been testing several variations and forms of error handling, so lets start with the basics.
function Test-WriteError {      [CmdletBinding()] param()  "Test-WriteError::ErrorActionPreference = $ErrorActionPreference"Move-Item -Path 'C:\Does\Not\Exists.log' -Destination 'C:\No\Where'"Test-WriteError::End"}   Test-WriteError::ErrorActionPreference = Continue
Move-Item : Cannot find path 'C:\Does\Not\Exists.log' because it does not exist.
At line:6 char:5
+     Move-Item -Path 'C:\Does\N…

Generate Random SecureString Key

Ever need to encrypt a SecureString that can be used across multiple servers? I suggest storing this BASE64 value in a secure location only accessible by the account(s) that need to decrypt the SecureString.
$secret = 'secret1234'$key    = [Convert]::ToBase64String((1..32 |% { [byte](Get-Random -Minimum 0 -Maximum 255) }))  $encryptedSecret = ConvertTo-SecureString -AsPlainText -Force -String $secret | ConvertFrom-SecureString -Key ([Convert]::FromBase64String($key))  $encryptedSecret | Select-Object @{Name='Key';Expression={$key}},@{Name='EncryptedSecret';Expression={$encryptedSecret}} | fl  $ss = ConvertTo-SecureString -Key ([Convert]::FromBase64String($key)) -String $encryptedSecret(New-Object System.Management.Automation.PSCredential 'SECURESTRING',$ss).GetNetworkCredential().Password