Recently I went through requirement of running SQL scripts on multiple databases on different servers. As the list of databases was different from script to script I decided to create universal solution, which will allow to easily prepare for deployment and can be simply reused at any time.

As a platform to execute scripts I went with PowerShell as that provides flexibility in case additional functionality will be required.

Whole solution contains 3 files:

• SqlExecutionInflow.csv – file contains list of databases and servers where particular database is located. It is simple CSV file with 2 columns
• SqlExecQuery.sql – contains SQL script which will be executed against all databases listed in SqlExecutionInflow.csv
• SqlExec.ps1 – main script which load SqlExecInflow.csv and executes query from SqlExecQuery.sql

All files have to be placed in same folder. As a result script will create transcript file with output from all executed commands.

And here are example files and script itself…

(more…)

If you want to send email from PowerShell script via Google Mail:

$SMTPServer = "smtp.gmail.com"
$SMTPPort = "587"
$SMTPUsername = "sender@gmail.com"
$SMTPPassword = "password"

$EmailSenderAddress = New-Object System.Net.Mail.MailAddress("sender@gmail.com", "PowerShell Automated Email")

$EmailFrom = $EmailSenderAddress
$EmailTo = "recipient@gmail.com"
$EmailCc = "recipient@hotmail.com"
$EmailAttachment = "C:\Scripts\Attachment.txt"
$EmailSubject = "Automated Email with Report."
$EmailBody = @"
Please find attached report.
"@

$EmailMessage = New-Object System.Net.Mail.MailMessage
$EmailMessage.Subject = $EmailSubject
$EmailMessage.Body = $EmailBody
$EmailMessage.To.Add($EmailTo)
$EmailMessage.CC.Add($EmailCc)
$EmailMessage.From = $SMTPUsername
$EmailMessage.Attachments.Add($EmailAttachment)

$SMTPSession = New-Object System.Net.Mail.SmtpClient($SMTPServer, $SMTPPort);
$SMTPSession.EnableSSL = $true
$SMTPSession.Credentials = New-Object System.Net.NetworkCredential($SMTPUsername, $SMTPPassword);
$SMTPSession.Send($EmailMessage)
Write-Host "Mail Sent"

Many times there is a need to store password in PowerShell script. Unfortunately to leave password in script is insecure solution and might cause an issue.

So, what to do if there is a need to have password in a script and we don’t want to have it embedded in clear-text in script?

Best way is to encrypt it and store in the file and then re-use it when required.

Create file with encrypted password

$secureString = Read-Host -AsSecureString "Enter a secret password"
$secureString | ConvertFrom-SecureString | Out-File C:\Scripts\storedPassword.txt

Use encrypted password in script

$secureString = Get-Content -Path C:\Scripts\storedPassword.txt | ConvertTo-SecureString

If you want to replace string in multiple files name you can simply use PowerShell for that.

Dir -Recurse | Rename-Item -NewName { $_.name -replace "file","name-of-file" }

If there is a need to pass specific credentials to Get-WmiObject in order to gather information from remote machine, here is how you can do it:

$LAdmin = "DOMAIN\Administrator"
$LPassword = ConvertTo-SecureString "Password!" -AsPlainText -Force
$Credentials = New-Object -Typename System.Management.Automation.PSCredential -ArgumentList $LAdmin, $LPassword

Once $Credentials object is created you can easily use it to gather information from remote PC:

Get-WmiObject -ComputerName $computername -Class Win32_VideoController -Namespace "root\cimv2" -Credential $Credentials

During mass reainstallation/redeployment/migration of large number of PCs, always question about applications is raised.

How to deploy/deliver applications once all machines are resintalled?

If you have SCCM in the infrastructure, this might help a lot with automation. All you need is mapping between computer name and applications (to be more specific collection ids) and PowerShell console on SCCM server.

Then you just need to prepare input files for the script below and you can automate application deployment on mass scale.

(more…)