· 7 years ago · Nov 21, 2018, 05:12 PM
1 $StackID = "<StackID>" $EnvironmentID = "<EnvironmentID>" Function Set-EnvironmentConnectionString() { if( $env:USERDNSDOMAIN -eq "QA.LOCAL") { $LoggingEnvironmentConnectionString = "data source=IND2Q00DBA01\I1; Integrated Security=SSPI; initial catalog=ScriptAutomation" } else { if( $env:USERDNSDOMAIN -eq "CT.XT.LOCAL" -or $env:USERDNSDOMAIN -eq "ET.LOCAL") { if($ENV:ComputerName -like "FRA3*") { $LoggingEnvironmentConnectionString = "data source=FRA3S50DBA01I02.XT.LOCAL\I02,10001; Integrated Security=SSPI; initial catalog=ScriptAutomation" } else { $LoggingEnvironmentConnectionString = "data source=XTINP1DBA01\DBADMIN; Integrated Security=SSPI; initial catalog=ScriptAutomation" } } } return $LoggingEnvironmentConnectionString.ToString() } Function Get-DataTable ($Query, $ConnectionString) { try { $da = new-object System.Data.SqlClient.SqlDataAdapter ($Query, $ConnectionString); $da.SelectCommand.CommandTimeout = 30; $dt = new-object System.Data.DataTable; $da.fill($dt) | out-null; return ,$dt; } catch { $_ } } Function CheckDatabaseStatus($SQLInstance, $DatabaseName) { $StatusQuery = "SELECT name, state_desc AS DBState, DATABASEPROPERTYEX(name, 'Status') AS DBStatus, CASE COALESCE(DATABASEPROPERTYEX(name, 'Collation'), 'CollationIsNULL') WHEN 'CollationIsNULL' THEN 'CANNOT Accept Connections' ELSE 'CAN Accept Connections' END AS DBConnection FROM sys.databases WHERE name = '$DatabaseName'" #Checks the status, state, and Connection and inserts into datatable $DatabaseList = Get-DataTable -Query $StatusQuery -ConnectionString "data source=$SQLInstance; Integrated Security=SSPI; initial catalog=master" #Checks if for some reason that ConfigDB does not exist on SQL Instance, insert error in to table If(($DatabaseList.Rows.Count -lt 1) -and ($DatabaseList.Exception -eq $null)) { $DataItem = @($SQLInstance, $DatabaseName, "CANNOT Accept Connections", "ConfigDB Does NOT exist") } foreach($record in $DatabaseList) { If(($record.Exception -eq $null) -and ($record.DBConnection -eq "CAN Accept Connections")) { $DataItem = @($SQLInstance, $DatabaseName, $record.DBConnection, "NONE") } ElseIf($record.Exception -eq $null) { $DataItem = @($SQLInstance, $DatabaseName, $record.DBConnection, "Database: $($record.name), Database State: $($record.DBState), Database Status: $($record.DBStatus), $($record.DBConnection)") } Else { $DataItem = @($SQLInstance, $DatabaseName, "CANNOT Accept Connections", $record.Exception.Message) } } return $DataItem } #Get SystemDB to query against from the StackID and EnvironmentID $QueryGetSystemDB = "SELECT StackID, ServerName AS ServerFQDN FROM dbo.SystemDBConn WHERE StackID = '$StackID' and environmentid = '$EnvironmentID'" $EnvironmentConnectionString = Set-EnvironmentConnectionString $SystemDBSQLInstance = Get-DataTable -Query $QueryGetSystemDB -ConnectionString $EnvironmentConnectionString $SystemDB = $SystemDBSQLInstance.ServerFQDN #Query to get the list of ConfigDBs $QueryGetConfigDBs = "SELECT DBName, ServerFQDN FROM (SELECT DISTINCT 'ConfigDB' AS DBName, ServerName + CASE WHEN ServerName LIKE 'XT%' OR ServerName LIKE 'QA%' THEN ',100' + RIGHT(REPLACE('0' + RIGHT(ServerName, 2), 'I', ''), 2) ELSE ',10001' END AS ServerFQDN, IsUp FROM SystemDB.dbo.DBServers WHERE StackID = 1 AND DBID > 0 UNION SELECT DISTINCT 'ConfigDB' AS DBName, ServerName + CASE WHEN ServerName LIKE 'XT%' OR ServerName LIKE 'QA%' THEN ',100' + RIGHT(REPLACE('0' + RIGHT(ServerName, 2), 'I', ''), 2) ELSE ',10001' END AS ServerFQDN, 1 AS IsUp FROM SystemDB.dbo.DBServerExtension WHERE DatabaseTypeID = 30) AS a ORDER BY IsUp DESC, ServerFQDN" #Get List of ConfigDBs and insert into Datatable $ListofConfigDBInstances = Get-DataTable -Query $QueryGetConfigDBs -ConnectionString "data source=$SystemDB; Integrated Security=SSPI; initial catalog=SystemDB" #Validate you could connect to SystemDB If($ListofConfigDBInstances.Exception -ne $null) { $ErrorMessage = "Could NOT Connect to SystemDB on the SQL Instance '$SystemDB' due to ERROR: $($ListOfConfigDBInstances.Exception.Message)." $ErrorMessage Throw "Please check the results pane in Script Ninja for further details on error(s)" #Kill Session Exit } #Create datatable $DBStatusTable = New-Object System.Data.DataTable [void]$DBStatusTable.Columns.Add("Instance") [void]$DBStatusTable.Columns.Add("Database") [void]$DBStatusTable.Columns.Add("DatabaseConnection") [void]$DBStatusTable.Columns.Add("Error") #Loop through the ConfigDB instances foreach ($ConfigDBInstance in $ListofConfigDBInstances) { $RecordStatus = CheckDatabaseStatus -SQLInstance $ConfigDBInstance.ServerFQDN -DatabaseName $ConfigDBInstance.DBName [void]$DBStatusTable.Rows.Add($RecordStatus) } $ResultsTable = New-Object System.Data.DataTable [void]$ResultsTable.Columns.Add("OrderValue") [void]$ResultsTable.Columns.Add("FinalResults"); #Loop through and create kafka entry on COnfigDBs that are online and accessible. foreach ($Object in $DBStatusTable) { if($Object.DatabaseConnection -eq "CAN Accept Connections") { $SQLQuery = "IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'configdb' AND state_desc = 'online' AND is_read_only = 0 ) BEGIN DECLARE @ConfigSettingName nvarchar(50); SELECT @ConfigSettingName = 'LogV3.ConsumerEnabledForDbId50000' IF NOT EXISTS (SELECT * FROM ConfigDB.dbo.ConfigSetting WHERE Category = 'InteractionStudio' AND ConfigSettingName = @ConfigSettingName) BEGIN INSERT INTO ConfigDB.dbo.ConfigSetting (Category, ConfigSettingName, Value) SELECT 'InteractionStudio', @ConfigSettingName, 'java'; END; SELECT '1' AS [OrderValue], '*** SUCCESS *** Target Kafka record exists in ConfigDB.dbo.ConfigSetting for 50000' AS Message; END; ELSE BEGIN SELECT '2' AS [OrderValue], '*** WARNING *** ConfigDB does not exist. Unable to insert record in ConfigDB.dbo.ConfigSetting for 50000' AS Message; END;" Try { $Result = Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $Object.Instance -Database $Object.Database -ErrorAction Stop -QueryTimeout ([int]::MaxValue) [void]$ResultsTable.Rows.Add($Result.OrderValue, $Result.Message) } Catch { $ExceptionMessage = "*** WARNING *** - $($Object.Instance) - ConfigDB not updated due to ERROR: $($_.Exception.Message)`n" [void]$ResultsTable.Rows.Add("3", $ExceptionMessage) } } Else { $ExceptionMessage = "*** WARNING *** - $($Object.Instance) - ConfigDB not updated due to ERROR: $($Object.Error)`n" [void]$ResultsTable.Rows.Add("3", $ExceptionMessage) } } #Print all results in the order of Success, Warning, Failed $ResultsTable | Sort-Object OrderValue | ForEach-Object {$_.ItemArray[1]} #Print final Result of the script If($ResultsTable.Select("FinalResults LIKE '*FAILED*'").ItemArray -eq $null) { $FinalResultMessage = "`n*** SUCCESS ***" } Else { $FinalResultMessage = "`n*** FAILURE ***" } #Print Result Message $FinalResultMessage If($FinalResultMessage -like "*FAILURE*") { Throw "Please check the results pane in Script Ninja for further details on error(s)" } <############################################################################## Purpose: This script inserts new value into ConfigDB.dbo.ConfigSetting for JB Logging Config Settings for Kafka Consumption History: 20180209 tseltsova W-4653102 Created Comments: 20180315 tseltsova W-4811436 fixed bug 20180426 lmesa W-4900367 converted to powershell to fix connection issues ################################################################################>