BASED IN Antioch, TENNESSEE, CNM TECH IS A BLOG BY CHARLES MCDOWELL. HIS POSTS EXPLORE his studies on computer technology through photos, cool quotes, and study guides.

Useful Power Shell Scripts

Charles Mcdowell
Technology, SQL

By Charles McDowell

Comptia Certified Linux Networking Professional.

Cisco Certified Networking Technician.

Microsoft Technology Associate Database Administration

““The cat’s A.I. was realistic, right down to the total lack of loyalty.””
— Dennis E. Taylor, For We Are Many

A Collection of Useful power shell scripts.

#How to Download from a file transfer protocol server. 
 

## Script configuration
#$BackupPath = "\\Servername\DATAFOlder\DatabaseName\FTPHandling\HOLD"
$ScriptPath = "\\Servername\DATAFOlder\DatabaseName\FTPHandling"
$WorkingPath = "\\Servername\DATAFOlder\DatabaseName\FTPHandling\WORK"

## SQL configuration
$DatabaseInstance = "ServerName, Portnumber"
$Database = "Databasename"


## SFTP Settings
$Key = (array of numbers)
$SFTPSite = "ftp.nameofftpsite.com"
$SFTPSSHKey = "ssh-rsa key" 
$SFTPPassword = Get-Content "$ScriptPath\ftp.txt" | ConvertTo-SecureString -Key $Key #Created via: read-host -assecurestring | convertfrom-securestring -Key $Key| out-file $Scriptpath\olhsftp.txt
$SFTPCredentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList abccompanyint, $SFTPPassword 

##SMTP Settings (For error notifications)
$SMTPServer = "mail.yoursmtpserver.com"
$SMTPFrom = "youremail@yoursmtpserver.com"
$ErrorMessageRecepient = "insert monitored email"
$ErrorSubject = "Error downloading files from FTP"

## Import the WinSCP module
Import-Module -Name WinSCP
Import-Module SqlServer

## Connect to the FTP server 
$Session = New-WinSCPSession -Credential $SFTPCredentials -HostName $SFTPSite -Protocol Sftp -SshHostKeyFingerprint $SFTPSSHKey -ErrorAction Stop

## Check for expected incoming files and download them if they are available. 
$ExpectedFiles = ( Invoke-Sqlcmd -ServerInstance $SFPInstance  -Database $SFPDatabase -Query "EXEC [Staging].[FTPGetFiles]" )

$ExpectedFiles | Add-Member NoteProperty -Name AvailableFiles -Value $null

## Only look for files from ftp.yourftpserver.com
$ExpectedFiles = $ExpectedFiles | WHERE {$_.FTPSite -eq 'ftp.yourftpserver.com'};

try{
    ForEach ($ExpectedFile in $ExpectedFiles) { 
    $ExpectedFile.AvailableFiles = Get-WinSCPItem -WinSCPSession $Session -Path $ExpectedFile.FTPPath -Filter * 
        ForEach ($AvailableFile in $ExpectedFile.AvailableFiles){ 
            If($AvailableFile.Name -match $ExpectedFile.ExpectedFileName ){ 
                #Receive-WinSCPItem -WinSCPSession $Session -Path $AvailableFile -Destination $BackupPath -ErrorAction Stop | Out-Null
                Receive-WinSCPItem -WinSCPSession $Session -Path $AvailableFile -Destination $ExpectedFile.Destination -ErrorAction Stop | Out-Null
                Receive-WinSCPItem -WinSCPSession $Session -Path $AvailableFile -Destination $WorkingPath -Remove -ErrorAction Stop | Out-Null
             }
        }
    }
}

catch{
    Send-MailMessage -From $SMTPFrom -To $ErrorMessageRecepient -SmtpServer $SMTPServer -Subject $ErrorSubject -Body "The C:\ScriptFolder\FTPRetrieval\DownloadFTPFiles.ps1 routine on ServerName encountered a problem downloading files from the SFTP server. ERROR MESSAGE: $Error"
}

Exit

 








#How to Handle files from ftp 
 

## Script configuration
$ScriptPath = "\\Servername\DATAFOlder\DatabaseName\FTPHandling"
$WorkingPath = "\\Servername\DATAFOlder\DatabaseName\FTPHandling\WORK"

## SQL configuration
$SFPInstance = "ServerName, Port Number"
$SFPDatabase = "DatabaseName"

##SMTP Settings (For error notifications)
$SMTPServer = "mail.yoursmtpserver.com"
$SMTPFrom = "Email@yoursmtpserver.com"
$ErrorMessageRecepient = "Monitored email"
$ErrorSubject = "Error handling file: $file"

Import-Module -name \\Servername\DATAFOlder\DatabaseName\DataTools.psm1 -Global -Force
Import-Module SqlServer

Set-Location $WorkingPath

$Files = Get-ChildItem $WorkingPath
$Files = $Files | Where-Object {$_ -NotLike "*.filepart*"}

$ExpectedFiles =  ( Invoke-Sqlcmd -ServerInstance $DatabaseInstance  -Database $Database -Query "Stored proceedure to get the fles"  )


try{
    ForEach ($File in $Files) { 
        ForEach ($ExpectedFile in $ExpectedFiles) {
            If ( $File.Name -match $ExpectedFile.ExpectedFileName ) {
                If ([string]::IsNullOrWhiteSpace($ExpectedFile.FileHandling)) {
                    Switch -Wildcard ($File.Name) 
                    {
                    *.csv { Copy-Item $File -Destination $ExpectedFile.Destination -Force -ErrorAction Stop;  Remove-Item $File.FullName -Force; Set-Location $WorkingPath}
                    *.txt { Copy-Item $File -Destination $ExpectedFile.Destination -Force -ErrorAction Stop;  Remove-Item $File.FullName -Force; Set-Location $WorkingPath }
                    *.pgp { DecryptPgP -EncryptedFile $File.Fullname -DecryptedFile (Join-Path -Path $ExpectedFile.Destination -ChildPath $File.BaseName) | Out-Null;  Remove-Item $File.FullName -Force; Set-Location $WorkingPath }
                    *.gpg { DecryptPgP -EncryptedFile $File.FullName -DecryptedFile (Join-Path -Path $ExpectedFile.Destination -ChildPath $File.BaseName) | Out-Null;  Remove-Item $File.FullName -Force; Set-Location $WorkingPath }
                    *.zip {  Copy (Test-Path $File) { ExtractArchive -ProcessingDir $WorkingPath -File $File -Output $ExpectedFile.Destination; Remove-Item $File -Force -ErrorAction Stop; Set-Location $WorkingPath }}
                    *.json { Copy-Item $File -Destination $ExpectedFile.Destination -Force -ErrorAction Stop;  Remove-Item $File.FullName -Force; Set-Location $WorkingPath }
                    }
                }
                Else {
                Invoke-Expression $ExpectedFile.FileHandling 
                }
                $CustomerFileTypeKey = $ExpectedFile.CustomerFileTypeKey
                Invoke-Sqlcmd -ServerInstance $SFPInstance  -Database $SFPDatabase -Query "EXEC dbo.usp_InsertFile $CustomerFileTypeKey, '$File'"
            }
        }
    }
}
catch{
    Send-MailMessage -From $SMTPFrom -To $ErrorMessageRecepient -SmtpServer $SMTPServer -Subject $ErrorSubject -Body "The 'insert script file location' routine on 'insert server name' encountered a problem extracting, decrypting or moving $file to the output directory ERROR MESSAGE: $Error"
}

Exit 
 



#How to move files for Marketing campaign

 
## Script configuration
$ScriptPath = "C:\Scripts\LCOMM"
$FileSource = "\\Servername\DATAFOlder"
$FileDestination = "\\destinationServername\destinationDATAFOlder"

##SMTP Settings (For error notifications)
$SMTPServer = "mail.yoursmtpserver.com"
$SMTPFrom = "email@yoursmtpserver.com"
$ErrorMessageRecepient = "monitored email"
$ErrorSubject = "Error moving file: $file"
$Recipient = "their email"
$Subject = "SECURE: File For Review"

Set-Location $FileSource
$Files = Get-ChildItem $FileSource


try{
ForEach ($File in $Files) { 

    Switch -Wildcard ($File) 
    {
    filename*.txt {  If (Test-Path $File) { Send-MailMessage -Attachments $File -From $SMTPFrom -To $Recipient -CC "ADD email" -SmtpServer $SMTPServer -Subject $Subject -Body "New data have been identified and added to $file. Attached for your review." -ErrorAction Stop; Remove-Item $File -Force -ErrorAction Stop;}}
    Filename1*.txt {  If (Test-Path $File) { Move-Item -Path $File -Destination $FileDestination -Force -ErrorAction Stop;}}
    Filename2*.txt {  If (Test-Path $File) { Move-Item -Path $File -Destination $FileDestination -Force -ErrorAction Stop;}}
    Filename3*.txt {  If (Test-Path $File) { Move-Item -Path $File -Destination $FileDestination -Force -ErrorAction Stop;}}
    Filename4*.txt {  If (Test-Path $File) { Move-Item -Path $File -Destination $FileDestination -Force -ErrorAction Stop;}}
    Filename5*.txt {  If (Test-Path $File) { Move-Item -Path $File -Destination $FileDestination -Force -ErrorAction Stop;}}
    }
}
}
 
catch{
    Send-MailMessage -From $SMTPFrom -To $ErrorMessageRecepient -SmtpServer $SMTPServer -Subject $ErrorSubject -Body "There was a problem moving $file to pickup directory ERROR MESSAGE: $Error"
} 

Exit

#How to run SQL Scripts and limit to specific stored proceedures

Import-Module SqlServer

## SQL configuration
$Instance = "Servername, Port number"
$Database = "Database Name"

## Scripts file location path
$ScriptDIR = "\\ServerName\datafolder\Scripts\"
$SQLScriptDIR = "\\ServerName\datafolder\Scripts\sqlscripts\ProcessScripts\Environment\"
$SQLScriptArchive = "\\ServerName\datafolder\Scripts\sqlscripts\ScriptsArchive\Environment\"
$SQLScriptNotApproved = "\\ServerName\datafolder\Scripts\sqlscripts\ScriptsArchive\NotApproved\"
$ApprovedSQLScripts = @('Sproc 1', 'Sproc 2', 'Sproc 3','Sproc 4','Sproc 5') 
$InvalidContent = @('INSERT INTO', 'ALTER TABLE','Delete From','DROP DATABASE','CREATE DATABASE ','BACKUP DATABASE','Create View','CREATE OR REPLACE VIEW','DROP VIEW','Update\s.*..*\n*set.') 


# Check for sql scripts in the folder
$SQLScripts = Get-ChildItem -Path $SQLScriptDIR
If ([string]::IsNullOrWhitespace($SQLScripts)){ Exit Write-Host "No Scripts in $SQLScriptDIR" }
$SQLScripts | Add-Member -NotePropertyName ProcessingStatus -NotePropertyValue ''
$ApprovedSQLScripts =  @()
 
 
<# Email notifying the list of Script that was processed succesfully,
Scripts not processed succesfully with errors.#>

## Email setup
$to = "your email", "stake holder 1", "stake holder 2", "stake holder 3","stake holder 4"
$smtp = "mail.yoursmtpserver.com"
$from = "your email" 
$subject = "SQL Scripts recently ran"  
$body = "Hello All, <br>" 
$body += "The following scripts have been ran." 
$body += '<style>
table 


table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
th, td {
padding: 15px;
text-align: left;
}
table#t01 tr:nth-child(even) {
background-color: #eee;
}
table#t01 tr:nth-child(odd) {
background-color: #fff;
}
table#t01 th {
background-color: black;
color: white;
}
</style>
</head>
<body>
<h1>Configuration Script Status </h1>
<table id="t01"><tr><th>Filename</th> <th>Status</th></tr>'



#  Verify script content and mark approved scripts. 
ForEach($Script in $SQLScripts | Where-Object -Property ProcessingStatus -NE "Not Approved") {
    $Script.ProcessingStatus = 'Not Processed'
    ForEach( $Statement in $ApprovedSQLScripts){
        try{
            If( Get-Item $Script.FullName | Select-String -SimpleMatch $Statement ){
                Write-Host "$Script contains $Statement"
                Write-Host "Executing Script"
                Invoke-Sqlcmd -ServerInstance $Instance  -Database $Database -InputFile $Script.FullName
                $Scriptname = $Script.FullName
                $Script.ProcessingStatus = 'Script Executed'
                Write-Host "$Script.ProcessingStatus"
                $body += "<tr><td>$Script</td><td>Executed Successfully</td></tr>"
                
            }
        }
        catch{
            $Script.ProcessingStatus = "Error"
             
        }
            }
            If( $Script.ProcessingStatus -eq 'Script Executed' ){
                Move-Item -Path $Script.FullName -Destination ($SQLScriptArchive + $Script.Name) -Force
                }
            elseif( $Script.ProcessingStatus -eq "Error" ){
                $body +="<tr><td>$Scriptname</td><td> Failed in SQl Server with an error of:" + $Error + "</td></tr>"
                Move-Item -Path $Script.FullName -Destination ($SQLScriptNotApproved + $Script.Name) -Force
                }
     
}


# Verify script content and mark un-approved scripts. 
$SQLScripts2 = Get-ChildItem -Path $ConfigurationSQLScriptDIR
$SQLScripts2 | Add-Member -NotePropertyName ProcessingStatus -NotePropertyValue ''
ForEach( $Script in $SQLScripts2 ) {
    $Script.ProcessingStatus = 'Not Processed'
    ForEach( $Statement in $InvalidContent ){
        try{
            If ( Get-Item $Script.FullName | Select-String -SimpleMatch $Statement ){
                Write-Host "$Script contains $Statement"
                Write-Host "Script not approved"
                $Script.ProcessingStatus = 'Not Approved'
                $body += "<tr><td>$Script</td><td>Not Approved</td></tr>"
                Move-Item -Path $Script.FullName -Destination ($SQLScriptNotApproved + $Script.Name) -Force
            }
        }
        catch{  
            $Script.ProcessingStatus = "Error"
        }
    }
}


#Verify all scripts moved. 
$SQLScripts3 = Get-ChildItem -Path $ConfigurationSQLScriptDIR
$SQLScripts3 | Add-Member -NotePropertyName ProcessingStatus -NotePropertyValue ''
ForEach( $Script in $SQLScripts3 ) {
        try{
            Write-Host "$Script does not appear to be using an approved stored proceedure."
            Write-Host "Script is not recognized."
            $Script.ProcessingStatus = 'Not Approved'
            $body += "<tr><td>$Script</td><td>Not Approved</td></tr>"
            Move-Item -Path $Script.FullName -Destination ($SQLScriptNotApproved + $Script.Name) -Force
        }
        catch{  
            $Script.ProcessingStatus = "Error"
        }
    
}



$SQLScripts | Select-Object Name, ProcessingStatus 
$SQLScripts2 | Select-Object Name, ProcessingStatus
$SQLScripts3 | Select-Object Name, ProcessingStatus

# Close table in email
$body += "</table> <br><br>"
 
 # Send email.
 send-MailMessage -SmtpServer $smtp -To $to -From $from -Subject $subject -Body $body -BodyAsHtml -Priority high 
 Write-Host $body

Exit
 
#How to remove all files older than specified limit. 

$limit = (Get-Date).AddDays(-365)
$path = "file path"

# Get list of files older than the $limit.
$Files = Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit }

# Measure the size of the files
$TotalSize = ($files | Measure-Object -Sum Length).Sum / 1GB

# Remove the files
$Files | Remove-Item -Force

# Delete any empty directories left behind after deleting the old files.
Get-ChildItem -Path $path -Recurse -Force | Where-Object { $_.PSIsContainer -and (Get-ChildItem -Path $_.FullName -Recurse -Force | Where-Object   ) -eq $null }| Remove-Item -Force -Recurse


Write-Host " $TotalSize GB of data has been removed"

Checklist for testing connection to a webserver.

How to import text files into SQL Server Management Studio in 5 easy steps.