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"

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" }

I use SecureCRT quite often to access Linux systems as well as network devices.

When going through one of the presentations on SecureCRT web page I found handy tip how to configure session logs to have it divided by date, time and session.

Screenshot below demonstrates settings for log file:

Basically parameters are set to:

• Log file name: %Y-%M-%D–%h-%m-%s.%t__%S(%H).txt
• Options / Start log upon connect: checked
• Custom log data / On each line: %h:%m:%s(%t):

These settings will create separate log file for each session every time connection will be established.

In addition to that each line will start with timestamp added by SecureCRT.

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…)