Powershell into Linux servers via SSH |
|
Needing to access a Linux server with Powershell. Check out the link below
http://www.powershellmagazine.com/2014/07/03/posh-ssh-open-source-ssh-powershell-module/
Execute this code to import the Posh-SSH module into your profile. This code is found from the link above.
iex (New-Object Net.WebClient).DownloadString("https://gist.github.com/darkoperator/6152630/raw/c67de4f7cd780ba367cccbc2593f38d18ce6df89/instposhsshdev")
http://www.powershellmagazine.com/2014/07/03/posh-ssh-open-source-ssh-powershell-module/
Execute this code to import the Posh-SSH module into your profile. This code is found from the link above.
iex (New-Object Net.WebClient).DownloadString("https://gist.github.com/darkoperator/6152630/raw/c67de4f7cd780ba367cccbc2593f38d18ce6df89/instposhsshdev")
Capture Linux drive space data with Powershell.. |
|
I work in an environment that is half SQL Server and half Postgres. I wanted to find a way to capture drive space info on my Postgres database servers. Using the POSH-SSH module mentioned in the above post I came up with this. This script I have scheduled to run from the SQL Agent using a Proxy Account with my creds so it can use the POSH-SSH module. This process captures the drive space info. The data comes in as an array. I then break up the array with the split function and insert each element into a table in my Repository server. From there you can do whatever you want with the data.
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
##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"
$VCIM = $Servername.Servername
$VCIM = $VCIM.trim()
##Loop through list of vCIM Pods
foreach($pod in $Servername){
$vCIM_ID = $pod.VCIM_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.VCIM_Drive_Space (vCIM_ID, filesystem, collection_time) values('$($vCim_ID)','$($filesystem)','$collection_time')"
}
foreach($size in $splitarray[1])
{
Invoke-sqlcmd -ServerInstance <repo server>-Database Repo -query "Update collector.VCIM_Drive_Space set size = '$size' where vCIM_ID = '$($vCim_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.VCIM_Drive_Space WHERE vCIM_ID = '$($vCim_ID)' and filesystem = '$filesystem') and filesystem = '$filesystem'"
}
foreach($Used in $splitarray[2])
{
Invoke-sqlcmd -ServerInstance <repo server> -Database Repo -query "Update collector.VCIM_Drive_Space set Used = '$Used' where vCIM_ID = '$($vCim_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.VCIM_Drive_Space WHERE vCIM_ID = '$($vCim_ID)') and filesystem = '$filesystem'"
}
foreach($Available in $splitarray[3])
{
Invoke-sqlcmd -ServerInstance <repo server> -Database Repo -query "Update collector.VCIM_Drive_Space set Available = '$Available' where vCIM_ID = '$($vCim_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.VCIM_Drive_Space WHERE vCIM_ID = '$($vCim_ID)') and filesystem = '$filesystem'"
}
foreach($Used_Percentage in $splitarray[4])
{
Invoke-sqlcmd -ServerInstance <repo server>-Database Repo -query "Update collector.VCIM_Drive_Space set Used_Percentage = '$Used_Percentage' where vCIM_ID = '$($vCim_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.VCIM_Drive_Space WHERE vCIM_ID = '$($vCim_ID)') and filesystem = '$filesystem'"
}
foreach($Mount in $splitarray[5])
{
Invoke-sqlcmd -ServerInstance <repo server> -Database Repo -query "Update collector.VCIM_Drive_Space set Mount = '$Mount' where vCIM_ID = '$($vCim_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.VCIM_Drive_Space WHERE vCIM_ID = '$($vCim_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(VCIM,ErrorMessage,Collection_Time,Job_Name) values($VCIM_ID, '$($Errormessage)','$date1','VCIM_Drive_Space_Collector')" -SuppressProviderContextWarning
}
}
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
##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"
$VCIM = $Servername.Servername
$VCIM = $VCIM.trim()
##Loop through list of vCIM Pods
foreach($pod in $Servername){
$vCIM_ID = $pod.VCIM_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.VCIM_Drive_Space (vCIM_ID, filesystem, collection_time) values('$($vCim_ID)','$($filesystem)','$collection_time')"
}
foreach($size in $splitarray[1])
{
Invoke-sqlcmd -ServerInstance <repo server>-Database Repo -query "Update collector.VCIM_Drive_Space set size = '$size' where vCIM_ID = '$($vCim_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.VCIM_Drive_Space WHERE vCIM_ID = '$($vCim_ID)' and filesystem = '$filesystem') and filesystem = '$filesystem'"
}
foreach($Used in $splitarray[2])
{
Invoke-sqlcmd -ServerInstance <repo server> -Database Repo -query "Update collector.VCIM_Drive_Space set Used = '$Used' where vCIM_ID = '$($vCim_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.VCIM_Drive_Space WHERE vCIM_ID = '$($vCim_ID)') and filesystem = '$filesystem'"
}
foreach($Available in $splitarray[3])
{
Invoke-sqlcmd -ServerInstance <repo server> -Database Repo -query "Update collector.VCIM_Drive_Space set Available = '$Available' where vCIM_ID = '$($vCim_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.VCIM_Drive_Space WHERE vCIM_ID = '$($vCim_ID)') and filesystem = '$filesystem'"
}
foreach($Used_Percentage in $splitarray[4])
{
Invoke-sqlcmd -ServerInstance <repo server>-Database Repo -query "Update collector.VCIM_Drive_Space set Used_Percentage = '$Used_Percentage' where vCIM_ID = '$($vCim_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.VCIM_Drive_Space WHERE vCIM_ID = '$($vCim_ID)') and filesystem = '$filesystem'"
}
foreach($Mount in $splitarray[5])
{
Invoke-sqlcmd -ServerInstance <repo server> -Database Repo -query "Update collector.VCIM_Drive_Space set Mount = '$Mount' where vCIM_ID = '$($vCim_ID)' and collection_time = (SELECT MAX(collection_time) FROM collector.VCIM_Drive_Space WHERE vCIM_ID = '$($vCim_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(VCIM,ErrorMessage,Collection_Time,Job_Name) values($VCIM_ID, '$($Errormessage)','$date1','VCIM_Drive_Space_Collector')" -SuppressProviderContextWarning
}
}