Technical Article

Capture Linux Drive Space data with Powershell

,

I work in an environment that is half SQL Server and half Postgres, around 1400 servers total.  I wanted to find a way to capture drive space info on my Postgres database servers.   I am connecting to the Linux servers via POSH-SSH module.  Info regarding this module can be found at http://www.powershellmagazine.com/2014/07/03/posh-ssh-open-source-ssh-powershell-module/ .  This script is scheduled to run from the SQL Agent using a Proxy Account with my credentials so it can use the POSH-SSH module.    
This process captures 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.  Also included is error handling that logs any errors that might occur during the process.  
All the best,
Michael D'Spain
http://thesurfingdba.weebly.com/connect-to-linux-with-powershell.html
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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating