Introduction to Windows PowerShell for the SQL Server DBA Part 2
To continue this series on Introduction to Windows PowerShell for the SQL Server DBA, this tip will look at the pipeline and output processing.
2009-03-23
3,574 reads
cls ## import modules needed. import-module sqlps -disablenamechecking import-module posh-ssh -disablenamechecking ## using write-debug instead of write-host during development phase $DebugPreference = "Continue" ## using POSH-SSH calls for a credential file. for the first run of this script you will need to create you credential file with the Read-Host cmd. ## setup connection file ## enter DBA account password ##read-host "Password" -assecurestring | convertfrom-securestring | out-file C:\postgres.txt ##populate variable with POD server names ## this could be a list of servers. I am using a connection to a repository database $Servername = Invoke-sqlcmd -ServerInstance <Reporistory Server> -Database Repo -Query "select servername, id from pods where decomm = 0" $LINUX_SERVER = $Servername.Servername $LINUX_SERVER = $LINUX_SERVER.trim() ##Loop through list of Linux_Server Pods foreach($pod in $Servername){ $Linux_Server_ID = $pod.LINUX_SERVER_ID #set variable to pull password from encrypted string $pass = get-content C:\postgres.txt | convertto-securestring ## create credential file $mycred = new-object -typename System.Management.Automation.PSCredential -argumentlist "postgres",$pass try{ ## create SSH session passing the credential that was created above $session = New-SSHSession -ComputerName $($pod.Servername) -credential $mycred -AcceptKey -erroraction "stop" write-debug "working on $($pod.servername)......" ## command to execute in SSH session and return drive space info via df -h $invokeOutput = Invoke-SSHCommand -SessionId $($session.SessionId) -Command "df -h" -erroraction "silentlycontinue" ## split variables. df -h returns an array and split function breaks up that array, splitting on the empty string $separator = " " $option = [System.StringSplitOptions]::RemoveEmptyEntries ## populate collection time variable $collection_time = get-date ## set counter. don't set to zero as that is the header of df -h $i = 1 ##loop through the array based on count while($i -lt $invokeOutput.output.count){ ## break up the array splitting on the empty string or whitespace $splitarray = $invokeOutput.Output[$i].Split($separator,$option) ## foreach loop through each part of the array and insert or update records $splitarray is hard coded as there are only 6 elements in df -h foreach ($filesystem in $splitarray[0]) { Invoke-sqlcmd -ServerInstance <repo server> -Database Repo -query "Insert into collector.LINUX_SERVER_Drive_Space (Linux_Server_ID, filesystem, collection_time) values('$($Linux_Server_ID)','$($filesystem)','$collection_time')" } foreach($size in $splitarray[1]) { Invoke-sqlcmd -ServerInstance <repo server>-Database Repo -query "Update collector.LINUX_SERVER_Drive_Space set size = '$size' where Linux_Server_ID = '$($Linux_Server_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.LINUX_SERVER_Drive_Space WHERE Linux_Server_ID = '$($Linux_Server_ID)' and filesystem = '$filesystem') and filesystem = '$filesystem'" } foreach($Used in $splitarray[2]) { Invoke-sqlcmd -ServerInstance <repo server> -Database Repo -query "Update collector.LINUX_SERVER_Drive_Space set Used = '$Used' where Linux_Server_ID = '$($Linux_Server_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.LINUX_SERVER_Drive_Space WHERE Linux_Server_ID = '$($Linux_Server_ID)') and filesystem = '$filesystem'" } foreach($Available in $splitarray[3]) { Invoke-sqlcmd -ServerInstance <repo server> -Database Repo -query "Update collector.LINUX_SERVER_Drive_Space set Available = '$Available' where Linux_Server_ID = '$($Linux_Server_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.LINUX_SERVER_Drive_Space WHERE Linux_Server_ID = '$($Linux_Server_ID)') and filesystem = '$filesystem'" } foreach($Used_Percentage in $splitarray[4]) { Invoke-sqlcmd -ServerInstance <repo server>-Database Repo -query "Update collector.LINUX_SERVER_Drive_Space set Used_Percentage = '$Used_Percentage' where Linux_Server_ID = '$($Linux_Server_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.LINUX_SERVER_Drive_Space WHERE Linux_Server_ID = '$($Linux_Server_ID)') and filesystem = '$filesystem'" } foreach($Mount in $splitarray[5]) { Invoke-sqlcmd -ServerInstance <repo server> -Database Repo -query "Update collector.LINUX_SERVER_Drive_Space set Mount = '$Mount' where Linux_Server_ID = '$($Linux_Server_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.LINUX_SERVER_Drive_Space WHERE Linux_Server_ID = '$($Linux_Server_ID)') and filesystem = '$filesystem'" } $i++ } } ## error handling catch [Exception] { Write-Host GENERAL FAILURE! -ForegroundColor Red ##When I want to know the full exception type: ##Write-Host $Error[0].Exception.GetType().FullName -BackgroundColor blue -ForegroundColor Yellow Write-Host $Error[0].Exception.Message -BackgroundColor blue -ForegroundColor Yellow $errormessage = $Error[0].Exception.Message -replace "'","" $date1 = get-date Invoke-Sqlcmd -ServerInstance <repo server> -Database Repo -Query "Insert into failure.Collection_Failures(LINUX_SERVER,ErrorMessage,Collection_Time,Job_Name) values($LINUX_SERVER_ID, '$($Errormessage)','$date1','LINUX_SERVER_Drive_Space_Collector')" -SuppressProviderContextWarning } } he process.