Functions/Test-RSCSQLConnection.ps1
|
################################################ # Function - Test-RSCSQLConnection - Testing SQL connectivity by returning a list of tables from the DB specified ################################################ Function Test-RSCSQLConnection { <# .SYNOPSIS Tests you are able to connect to a SQL server using a SqlServer PowerShell module (required, should already be installed) for proving connectivity before using the Write-RSCEvent functions. .DESCRIPTION This function requires you to already have the Microsoft Sqlserver PowerShell module installed, use Install-Module Sqlserver, which then allows you to test connectivity to your SQL server. .LINK GraphQL schema reference: https://rubrikinc.github.io/rubrik-api-documentation/schema/reference .PARAMETER SQLInstance The server name to connect to. I.E "localhost" for a local SQL server. If using a named instance include it in the string. I.E "localhost\MSSQLSERVER" .PARAMETER SQLDB Verify you can access the specified SQL DB (needs to already exist, no Write-RSCEvent scripts will ever create a DB, only tables) .OUTPUTS Returns a list of the table structure of the database specified. .EXAMPLE Test-RSCSQLConnection -SQLInstance "localhost" -SQLDB "RubrikReporting" .NOTES Author: Joshua Stenhouse Date: 05/11/2023 #> ################################################ # Paramater Config ################################################ Param ( [Parameter(Mandatory=$true)]$SQLInstance,[Parameter(Mandatory=$true)]$SQLDB ) ################################################ # Importing Module & Running Required Functions ################################################ Import-Module RSCReporting # Checking SQL module, exiting function with error if not availabile Test-RSCSQLModule ################################################ # Importing SQL Server Module ################################################ $PSModules = Get-Module -ListAvailable | Select-Object -ExpandProperty Name # Specify the name of the SQL Server module to use (either SqlServer or SQLPS) $SQLModuleName = $PSModules | Where-Object {(($_ -contains "SQLPS") -or ($_ -contains "sqlserver"))} | Select-Object -Last 1 # Checking to see if SQL Server module is loaded $SQLModuleCheck = Get-Module $SQLModuleName # If SQL module not found in current session importing IF ($SQLModuleCheck -eq $null) { # Importing SqlServer module Import-Module $SQLModuleName -ErrorAction SilentlyContinue } ELSE { # Nothing to do, SQL module already in the current session } ########################## # SQL - Checking Tables ########################## # Creating query $SQLTableListQuery = "USE $SQLDB; SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;" # Run SQL query Try { $SQLTableList = Invoke-SQLCmd -Query $SQLTableListQuery -ServerInstance $SQLInstance -QueryTimeout 300 } Catch { $Error[0] | Format-List -Force } # Selecting $SQLTableList = $SQLTableList | Select-Object -ExpandProperty TABLE_NAME # Returning null Return $SQLTableList # End of function } |