-
-
Save Desani/ba61b8b230072cff9af13d0a00735bf2 to your computer and use it in GitHub Desktop.
PowerShell used to process the PSSDIAG data with SQL Nexus and then launches an SSRS Report file.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <# | |
| ################################################################################### | |
| ################## MICROSOFT SQL SERVER AUTO REPORTING TOOL ####################### | |
| ################################################################################### | |
| Version: 0.8 | |
| Changelog: | |
| 0.1 - Initial script creation. | |
| 0.2 - Added some error correction and logging. Changed where the zip file | |
| was deleted to allow for multiple runs if there is an error. | |
| 0.3 - Added check to make sure it was run as administrator. | |
| Refactored lauching commands | |
| 0.4 - Added checking if the database existed and dropping it. | |
| 0.5 - Corrected Error Catching | |
| 0.6 - Added a lock file to prevent multiple intances from being run at the | |
| same time | |
| 0.7 - Added a step to import the raw data from the blg file to the database | |
| 0.8 - Added check to make sure a report with the same name did not exist | |
| ################################################################################### | |
| #> | |
| <# | |
| TODO: | |
| Create a custom query to grab the timezone on the server so that the times can be processed properly | |
| Find out what drives SQL server is using to store information to display on the report | |
| #> | |
| Write-Host "$(Get-Date -Format o) Starting script $($MyInvocation.MyCommand.Name)" | |
| Write-Host " " | |
| $scriptPath = split-path -parent $MyInvocation.MyCommand.Definition | |
| $Date = "$((Get-Date).ToString('yyyy-MM-dd'))" | |
| $LogPath = Join-Path -Path $scriptPath -ChildPath "Log" | |
| $Logfile = Join-Path -Path $LogPath -ChildPath "SQLNexus$Date.log" | |
| $DropboxFolder = Join-Path -Path $scriptPath -ChildPath "Dropbox" | |
| $SourceFolder = Join-Path -Path $scriptPath -ChildPath "Source" | |
| $ReportFolder = Join-Path -Path $scriptPath -ChildPath "Reports" | |
| $ScriptVersion = 0.8 | |
| $ServerConnectionString = "INSERTCONNECTIONSTRING" | |
| $ReportServerConnectionString = "http://REPORTSERVERCONNECTIONSTRING" | |
| $IncludedFiles = ".zip" | |
| $ErrorLevel = 0 | |
| $lockFile = Join-Path -Path $scriptPath -ChildPath ".lock" | |
| Clear-Host | |
| # Add additional functionality | |
| Add-Type -AssemblyName System.IO.Compression.FileSystem | |
| Add-Type -AssemblyName "Microsoft.ReportViewer.WinForms, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" | |
| # Function to write the output to a log file | |
| Function LogWrite | |
| { | |
| Param ([string]$logstring) | |
| Add-content $Logfile -value $logstring | |
| } | |
| # Function to unzip files | |
| function Unzip | |
| { | |
| param([string]$zipfile, [string]$outpath) | |
| [System.IO.Compression.ZipFile]::ExtractToDirectory($zipfile, $outpath) | |
| } | |
| Function ConnectSQL | |
| { | |
| param([string]$Database, | |
| [String]$phase) | |
| If ($phase -eq "pre"){ | |
| $query=@" | |
| USE master; | |
| GO | |
| ALTER DATABASE $Database SET SINGLE_USER WITH ROLLBACK IMMEDIATE ; | |
| ALTER DATABASE $Database Modify Name = CurrentReporting ; | |
| ALTER DATABASE CurrentReporting SET MULTI_USER ; | |
| GO | |
| "@ | |
| LogWrite "$(Get-Date -Format o) Changing the database name to: CurrentReporting" | |
| Write-Host "$(Get-Date -Format o) Changing the database name to: CurrentReporting" | |
| Try { | |
| Invoke-SqlCmd -ServerInstance $ServerConnectionString -Database "master" -Query $query -ErrorAction Stop | |
| } Catch { | |
| $ErrorMessage = $_.Exception.Message | |
| LogWrite "$(Get-Date -Format o) There was an error changing the database name: $ErrorMessage" | |
| Write-Host "$(Get-Date -Format o) There was an error changing the database name." -ForegroundColor Red | |
| Write-Host "$(Get-Date -Format o) Please Manually remove or rename the database and then run the script again." | |
| Write-Host "$(Get-Date -Format o) Exiting Script" | |
| Remove-Item -Force -Path $lockFile | |
| Exit | |
| } | |
| Return 0 | |
| } elseif ($phase -eq "post") | |
| { | |
| $query=@" | |
| USE master; | |
| GO | |
| ALTER DATABASE CurrentReporting SET SINGLE_USER WITH ROLLBACK IMMEDIATE ; | |
| ALTER DATABASE CurrentReporting Modify Name = $Database ; | |
| ALTER DATABASE $Database SET MULTI_USER ; | |
| GO | |
| "@ | |
| LogWrite "$(Get-Date -Format o) Changing the database name to: $Database" | |
| Write-Host "$(Get-Date -Format o) Changing the database name to: $Database" | |
| Try { | |
| Invoke-SqlCmd -ServerInstance $ServerConnectionString -Database "master" -Query $query -ErrorAction Stop | |
| } Catch { | |
| $ErrorMessage = $_.Exception.Message | |
| LogWrite "$(Get-Date -Format o) There was an error changing the database name: $ErrorMessage" | |
| Write-Host "$(Get-Date -Format o) There was an error changing the database name." -ForegroundColor Red | |
| Write-Host "$(Get-Date -Format o) Please Manually remove or rename the database and then run the script again." | |
| Write-Host "$(Get-Date -Format o) Exiting Script" | |
| Remove-Item -Force -Path $lockFile | |
| Exit | |
| } | |
| Return 0 | |
| } | |
| } | |
| Function DropDatabase | |
| { | |
| param([string]$Database) | |
| $query=@" | |
| USE master; | |
| GO | |
| DROP DATABASE IF EXISTS $Database ; | |
| GO | |
| "@ | |
| Try { | |
| Invoke-SqlCmd -ServerInstance $ServerConnectionString -Database "master" -Query $query -ErrorAction Stop | |
| } Catch { | |
| $ErrorMessage = $_.Exception.Message | |
| LogWrite "$(Get-Date -Format o) There was an error dropping database $Database : $ErrorMessage" | |
| Write-Host "$(Get-Date -Format o) There was an error dropping database $Database ." -ForegroundColor Red | |
| Write-Host "$(Get-Date -Format o) Please Manually remove or rename the database and then run the script again." | |
| Write-Host "$(Get-Date -Format o) Exiting Script" | |
| Remove-Item -Force -Path $lockFile | |
| Exit | |
| } | |
| Return 0 | |
| } | |
| Function GenerateReport | |
| { | |
| param([string]$Database) | |
| $rv = New-Object Microsoft.Reporting.WinForms.ReportViewer | |
| $rv.ServerReport.ReportServerUrl = $ReportServerConnectionString | |
| $rv.ServerReport.ReportPath = "/Nexus Reports/HealthReport" | |
| $rv.ProcessingMode = "Remote" | |
| $rv.RefreshReport() | |
| $rv.ServerReport.Refresh() | |
| $mimeType = $null | |
| $encoding = $null | |
| $extension = $null | |
| $streamids = $null | |
| $warnings = $null | |
| $bytes = $rv.ServerReport.Render("WORDOPENXML", | |
| $null, | |
| [ref] $mimeType, | |
| [ref] $encoding, | |
| [ref] $extension, | |
| [ref] $streamids, | |
| [ref] $warnings) | |
| $ReportFileName = $Database -replace "Nexus_","" | |
| $ReportFileName = $ReportFileName -replace "_"," " | |
| $fileName = $ReportFolder + "\" + $ReportFileName +" Technical Report.docx" | |
| If (Test-Path $fileName) | |
| { | |
| LogWrite "$(Get-Date -Format o) Removing the old Techinical report file $fileName " | |
| Write-Host "$(Get-Date -Format o) Removing the old Techinical report file $fileName " | |
| Remove-Item -Path $ReportFileName | |
| } | |
| $fileStream = New-Object System.IO.FileStream($fileName, [System.IO.FileMode]::OpenOrCreate) | |
| $fileStream.Write($bytes, 0, $bytes.Length) | |
| $fileStream.Close() | |
| LogWrite "$(Get-Date -Format o) Techinical Report Filename: $fileName " | |
| Write-Host "$(Get-Date -Format o) Techinical Report Filename: $fileName " | |
| explorer $ReportFolder | |
| } | |
| Clear-Host | |
| # Check to see if the powershell was lauched as Admin | |
| If (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole(` | |
| [Security.Principal.WindowsBuiltInRole] "Administrator")) | |
| { | |
| Write-Warning "You do not have Administrator rights to run this script!`nPlease re-run this script as an Administrator!" | |
| Break | |
| } | |
| # Create the log directory | |
| If(!(test-path $LogPath)) | |
| { | |
| New-Item -ItemType Directory -Force -Path $LogPath | Out-Null | |
| } | |
| LogWrite "$(Get-Date -Format o) Initializing Script" | |
| LogWrite "$(Get-Date -Format o) Script Version: $ScriptVersion" | |
| Write-Host "$(Get-Date -Format o) Script Version: $ScriptVersion" | |
| If (Test-Path $lockFile) | |
| { | |
| LogWrite "$(Get-Date -Format o) Lock file found. Please only run one intance of the program at a time." | |
| Write-Host "$(Get-Date -Format o) Lock file found. Please only run one intance of the program at a time." -ForegroundColor Red | |
| Write-Host "$(Get-Date -Format o) Exiting Script" | |
| Exit | |
| } else { | |
| New-Item -Force -ItemType File -Path $lockFile | Out-Null | |
| } | |
| Get-ChildItem -LiteralPath $DropboxFolder -File -Recurse | Where-Object { $IncludedFiles -contains $_.Extension } | % { | |
| LogWrite "$(Get-Date -Format o) Unzipping the file $_" | |
| Write-Host "$(Get-Date -Format o) Unzipping the file $_" | |
| $SourceName = Join-Path -path $SourceFolder -childpath $_.BaseName | |
| $SourceName = $SourceName -replace "-","" | |
| Unzip $_.FullName $SourceFolder | |
| $Database = "Nexus_" + $_.BaseName | |
| LogWrite "$(Get-Date -Format o) Launching SQLNexus to process the folder $SourceName" | |
| Write-Host "$(Get-Date -Format o) Launching SQLNexus to process the folder $SourceName" | |
| Try { | |
| LogWrite "$(Get-Date -Format o) Dropping the Database $Database if it exists" | |
| Write-Host "$(Get-Date -Format o) Dropping the Database $Database if it exists" | |
| If ($ErrorLevel -eq 0) { | |
| $ErrorLevel = DropDatabase $Database | |
| } else { | |
| DropDatabase $Database | |
| } | |
| } Catch { | |
| $ErrorMessage = $_.Exception.Message | |
| $ErrorLevel = 1 | |
| LogWrite "$(Get-Date -Format o) There was an error dropping the database $Database : $ErrorMessage" | |
| Write-Host "$(Get-Date -Format o) There was an error dropping the database $Database ." -ForegroundColor Red | |
| } | |
| Try { | |
| LogWrite "$(Get-Date -Format o) Processing the data with SQL Nexus" | |
| Write-Host "$(Get-Date -Format o) Processing the data with SQL Nexus" | |
| Start-Process -FilePath 'X:\SQLNexus\Binaries\sqlnexus.exe' "/S$ServerConnectionString /D$Database /E /I$SourceName /X" -Wait | |
| } Catch { | |
| $ErrorMessage = $_.Exception.Message | |
| $ErrorLevel = 1 | |
| LogWrite "$(Get-Date -Format o) There was an error processing the files with SQL Nexus: $ErrorMessage " | |
| Write-Host "$(Get-Date -Format o) There was an error processing the files with SQL Nexus. Please try running the script again." -ForegroundColor Red | |
| } | |
| # Truncate the tables created by SQL Nexus | |
| LogWrite "$(Get-Date -Format o) Truncating SQL Nexus tables for raw data import" | |
| Write-Host "$(Get-Date -Format o) Truncating SQL Nexus tables for raw data import" | |
| Try { | |
| Invoke-Sqlcmd -Database $database -ServerInstance $ServerConnectionString -Query "TRUNCATE TABLE [dbo].[CounterData] ;" -ErrorAction Stop | |
| Invoke-Sqlcmd -Database $database -ServerInstance $ServerConnectionString -Query "TRUNCATE TABLE [dbo].[CounterDetails] ;" -ErrorAction Stop | |
| Invoke-Sqlcmd -Database $database -ServerInstance $ServerConnectionString -Query "TRUNCATE TABLE [dbo].[DisplayToID] ;" -ErrorAction Stop | |
| } Catch { | |
| $ErrorMessage = $_.Exception.Message | |
| $ErrorLevel = 1 | |
| LogWrite "$(Get-Date -Format o) There was an error truncating SQL Nexus Tables : $ErrorMessage" | |
| Write-Host "$(Get-Date -Format o) There was an error truncating SQL Nexus Tables." -ForegroundColor Red | |
| } | |
| LogWrite "$(Get-Date -Format o) Importing the raw perfmon data" | |
| Write-Host "$(Get-Date -Format o) Importing the raw perfmon data" | |
| Get-ChildItem $SourceName -Filter *.BLG | | |
| Foreach-Object { | |
| $sourceBlg = $_.FullName | |
| $sqlDSNconection = "SQL:Nexus!$Database" | |
| $AllArgs = @($sourceBlg, '-f', 'SQL', '-o', $sqlDSNconection) | |
| & 'relog.exe' $AllArgs | |
| } | |
| LogWrite "$(Get-Date -Format o) Removing the source files after processing: $SourceName" | |
| Write-Host "$(Get-Date -Format o) Removing the source files after processing: $SourceName" | |
| Remove-Item -Recurse -Path $SourceName | |
| Try { | |
| LogWrite "$(Get-Date -Format o) Dropping the Database CurrentReporting if it exists" | |
| Write-Host "$(Get-Date -Format o) Dropping the Database CurrentReporting if it exists" | |
| If ($ErrorLevel -eq 0) { | |
| $ErrorLevel = DropDatabase CurrentReporting | |
| } else { | |
| DropDatabase CurrentReporting | |
| } | |
| } Catch { | |
| $ErrorMessage = $_.Exception.Message | |
| $ErrorLevel = 1 | |
| LogWrite "$(Get-Date -Format o) There was an error dropping the database $Database : $ErrorMessage" | |
| Write-Host "$(Get-Date -Format o) There was an error dropping the database $Database ." -ForegroundColor Red | |
| } | |
| Try { | |
| If ($ErrorLevel -eq 0) { | |
| $ErrorLevel = ConnectSQL $Database "pre" | |
| } else { | |
| ConnectSQL $Database "pre" | |
| } | |
| } Catch { | |
| $ErrorMessage = $_.Exception.Message | |
| $ErrorLevel = 1 | |
| LogWrite "$(Get-Date -Format o) There was an error connecting to the database for report generation: $ErrorMessage" | |
| Write-Host "$(Get-Date -Format o) There was an error connecting to the database for report generation." -ForegroundColor Red | |
| } | |
| Try { | |
| LogWrite "$(Get-Date -Format o) Generating the report with SSRS" | |
| Write-Host "$(Get-Date -Format o) Generating the report with SSRS" | |
| GenerateReport $Database | |
| } Catch { | |
| $ErrorMessage = $_.Exception.Message | |
| $ErrorLevel = 1 | |
| LogWrite "$(Get-Date -Format o) There was an error generating the SSRS report: $ErrorMessage" | |
| Write-Host "$(Get-Date -Format o) There was an error generating the SSRS report." -ForegroundColor Red | |
| } | |
| Try { | |
| If ($ErrorLevel -eq 0) { | |
| $ErrorLevel = ConnectSQL $Database "post" | |
| } else { | |
| ConnectSQL $Database "post" | |
| } | |
| } Catch { | |
| $ErrorMessage = $_.Exception.Message | |
| $ErrorLevel = 1 | |
| LogWrite "$(Get-Date -Format o) There was an error connecting to the database for report generation: $ErrorMessage" | |
| Write-Host "$(Get-Date -Format o) There was an error connecting to the database for report generation." -ForegroundColor Red | |
| } | |
| If ($ErrorLevel -eq 0) | |
| { | |
| LogWrite "$(Get-Date -Format o) Deleting the file $_" | |
| Write-Host "$(Get-Date -Format o) Deleting the file $_" | |
| Remove-Item -Path $_.FullName | |
| } | |
| } | |
| Remove-Item -Force -Path $lockFile | |
| LogWrite "$(Get-Date -Format o) Script Complete" | |
| Write-Host "$(Get-Date -Format o) Script Complete" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment