PowerShell Scripting

PowerShell Grep Recent Logfiles

I’ve written this PowerShell script to search for specific txt in recent logfiles (changed within last 24 hours). The output is returned to standard out.

# *****************************************************************************
# Scriptnaam: CHECKLOGFILES.PS1
# Written_by: Tim van Kooten Niekerk
# Versie: 20120522B
# Info: PowerShell script to search for specific text in files modified in the last 24 hours.
# *****************************************************************************

# Functions en Procedures...
function fGetFileModTime([string]$sFileName) {
  $vFileInfo = get-childitem $sFileName
  return $vFileInfo.lastwritetime 
}

function fSendMessage([string]$sNode, [string]$sApplication, [string]$sMessage) {
  $sMessage
  # And send result to operations...
  #return Start-Process...
}

# Start Main script...
if (($args[0]) -and ($args[1]) -and ($args[2]) -and ($args[3])) {
  # Check if path exists...
  if (Test-Path $args[0]) {
    # Check for searchterm in files modified in the last 24 hours...
    [boolean]$bResultFound = $false
    [string]$sSearchTerm = $args[1]
    Get-ChildItem $($args[0]) | where {$_.LastWriteTime -gt (Get-Date).AddHours(-24)} |
      % {
	    $oResult1 = select-string -path $_.fullname -pattern $sSearchTerm -context 0 
	    if (($oResult1.Linenumber -gt 0) -or ($oResult1.Count -gt 0)) { 
	      $sResult1 = $oResult1 | format-list -property line,path,linenumber | Out-String
	      [string]$sResultMerge = [string]$sResultMerge + $sResult1
	      $bResultFound = $true
	    }
	  }
    # $sResult1 = select-string -path $($args[0]) -pattern $($args[1]) -context 0 
    # Send message when searchterm is found...
    if ($bResultFound -eq $true) {
      $sMessage1 = "Searchterm [" + $args[1] + "] found in recent logfiles... `r`n`r`nRESULT:"
      fSendMessage $args[2] $args[3] $sMessage1 
    }
  } else {
    $sMessage1 = "Error executing ... `r`n`r`nERROR: Pad niet gevonden."
    fSendMessage $args[2] $args[3] $sMessage1  
  }
} else {
  "Error executing script... `r`n`r`nERROR: Arguments missing."
  "SYNTAX: checklogfiles.ps1 `"<c:pathtodir*.log>`" `"<searchterm>`" `"<hostname>`" `"<service>`""
}

 

PowerShell Check Database-backup

This next script can be used to check if Full and Transaction Log backups are made within the last two days. Databases are read from a CSV file. I use a slightly modified version which reads databases from a registration database.

# ********************************************************************
# Scriptnaam: CHECKBACKUP.PS1
# Geschreven_door: Tim van Kooten Niekerk
# Versie: 20110811A
# Info: Check if backup is made within last two days...
# ********************************************************************

# Global config settings...
[string]$sServerInstanceFile = ".ServerInstance.csv"
[string]$sSMTPServer1 = "smtp-server-name"
[string]$sToAddress1 = "email-adsress"

# Global objects...
$oNETIP = [System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()

# Register Snapins...
$oPSSnapinRegResult = PSSnapin SqlServerCmdletSnapin* -registered
if ($oPSSnapinRegResult) {
  $oPSSnapinResult = PSSnapin $oPSSnapinRegResult.Name
  if (-not $oPSSnapinResult) { 
    Add-PSSnapin $oPSSnapinRegResult.Name
  }
} else {
  break
}
  
# Functions and Procedures...
function fSendMail([string]$sSMTPSrvrName, [string]$sFrom, [string]$sTo, [string]$sSubject, [string]$sBody, [string]$sAttFileName) {
  if (($sSMTPSrvrName) -and ($sFrom) -and ($sTo) -and $sSubject) {
  
    # Declare mail objects...
    $oMessage = new-object Net.Mail.MailMessage
    $oSMTP = new-object Net.Mail.SmtpClient($sSMTPSrvrName)

    # Pass values to object...
    $oMessage.From = $sFrom
    $oMessage.To.Add($sTo)
    $oMessage.Subject = $sSubject
    if ($sBody) { $oMessage.Body = $sBody }

    # Add attachment...
    if ($sAttFileName) {
      $oAttachment = new-object Net.Mail.Attachment($sAttFileName)
      $oMessage.Attachments.Add($oAttachment)
    }

    $oSMTP.Send($oMessage)
    if ($? -eq "True") {
      return "INFO: Email has been sent."
    } else {
      return "ERROR: Error sending e-mail."
    }

  } else {
    return "ERROR: Argument(s) missing. [fSendMail `"SMTPServerName`" `"From`" `"To`" `"Subject`" (`"Body`") (`"AttachFileName`")]"
  }
}

function fSQLCmd([string]$sInstance, [string]$sQuery, [string]$sDatabase) {
  if ($sDatabase) {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -Database " + $sDatabase + " -QueryTimeout 30 -Query `"" `
                               + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  } else {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; `
                               if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  }
  return Invoke-Expression $sQuery
}

# Start Main script...
[int]$iCounter = 0
Import-Csv $($sServerInstanceFile) -header ("instance", "checkfull", "checktl") | foreach {
  # Skip header...
  if (($iCounter -ne 0) -and ($_.instance)) {
    $oResult1 = $null
    $oResult2 = $null
    # Run Queries on servers...
    if ($_.checkfull -eq 1) {
      [string]$sQuery1 = "/* ##### Begin T-SQL Query... ##### */ `
      SELECT sys.databases.name AS NoRecentFullBackup `
      FROM sys.databases `
      WHERE sys.databases.name NOT IN `
      (SELECT DISTINCT sys.databases.name from sys.databases `
      INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name `
      WHERE ((msdb..backupset.type = 'D' OR msdb..backupset.type = 'I') `
      AND msdb..backupset.is_copy_only = 0 `
      AND msdb..backupset.backup_start_date > (GetDate() -2))) `
      AND sys.databases.state = 0 `
      AND sys.databases.name  'tempdb' `
      /* ##### Einde T-SQL Query... ##### */"
      # Forceer in een object zodat we altijd kunnen tellen...
      [Object[]]($oResult1) = fSQLCmd $($_.instance) $sQuery1 "master"
    }


    if ($_.checktl -eq 1) {
      [string]$sQuery2 = "/* ##### Begin T-SQL Query... ##### */ `
      SELECT sys.databases.name AS NoRecentTLBackup `
      FROM sys.databases `
      WHERE sys.databases.name NOT IN `
      (SELECT DISTINCT sys.databases.name from sys.databases `
      INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name `
      WHERE (msdb..backupset.type = 'L' AND msdb..backupset.backup_start_date > (GetDate() -2))) `
      AND sys.databases.recovery_model != 3 and sys.databases.state = 0 `
      /* ##### Einde T-SQL Query... ##### */"
      # Forceer in een object zodat we altijd kunnen tellen...
      [Object[]]($oResult2) = fSQLCmd $($_.instance) $sQuery2 "master"
    }

    # Zodra er rows worden geteld een e-mail samenstellen...
    if (($oResult1.Count -gt 0) -or ($oResult2.Count -gt 0)) {
      $oResult1 = $oResult1 | Out-String
      $oResult2 = $oResult2 | Out-String
      [string]$sFromAddress1 = "noreply@" + $oNETIP.HostName + "." + $oNETIP.DomainName
      [string]$sBody1 = $oResult1 + $oResult2
      [string]$sSubject1 = $($_.instance.Replace("", "_")) + ":Database backup older than 2 days..."
      fSendMail $sSMTPServer1 $sFromAddress1 $sToAddress1 $sSubject1 $sBody1
    }
  }
  $iCounter++
}
# ********************************************************************

CSV File format:

instance,checkfull,checktl
SERVERA,1,1
SERVERBINS1,1,1
SERVERC,1,1

PowerShell E-mail Function

Met behulp van onderstaande functie kun je vanuit een script een e-mail verzenden. De procedure is in een functie gegoten waardoor deze gemakkelijk kan worden hergebruikt. Deze functie werkt zowel in PowerShell V1 als PowerShell V2. Deze functie maakt gebruik van een andere functie fnWriteEventLog t.b.v. schrijfacties naar het Windows EventLog.

function fnSendMail()
{
  param (
    [string]$sSMTPSrvrName, 
    [string]$sFrom, 
    [string]$sTo, 
    [string]$sSubject, 
    [string]$sBody, 
    [string]$sAttFileName, 
    [switch]$swIsBodyHTML = $false
    )

  if (($sSMTPSrvrName) -and ($sFrom) -and ($sTo) -and $sSubject) {
  
    # Declare mail objects...
    $oMessage = new-object Net.Mail.MailMessage
    $oSMTP = new-object Net.Mail.SmtpClient($sSMTPSrvrName)

    # Pass values to object...
    $oMessage.From = $sFrom
    $oMessage.To.Add($sTo)
    $oMessage.Subject = $sSubject
    $oMessage.IsBodyHtml = $swIsBodyHTML
    $oMessage.BodyEncoding = ([System.Text.Encoding]::UTF8)
    if ($sBody) { $oMessage.Body = $sBody }

    # Add attachment...
    if ($sAttFileName) {
      $oAttachment = new-object Net.Mail.Attachment($sAttFileName)
      $oMessage.Attachments.Add($oAttachment)
    }

    $oSMTP.Send($oMessage)
    if ($? -eq "True") {
      #fnWriteEventLog -sNode (fnGetFQDN) -sMessage "Email has been sent..." -iEventID 1 -sType "Information"
      return "INFO: Email has been sent..." 
    } else {
      fnWriteEventLog -sNode (fnGetFQDN) -sMessage "Error sending mail..." -iEventID 501
      return "ERROR: Error sending e-mail..."
    }

  } else {
    fnWriteEventLog -sNode (fnGetFQDN) -sMessage "fSendMail argument missing..." -iEventID 501
    return "ERROR: Argument(s) missing. [fnSendMail `"SMTPServerName`" `"From`" `"To`" `"Subject`" `"Body`" (`"AttachFileName`" `"isBodyHTML`")]"
  }
}

PowerShell Create MSSQL Agent Job

M.b.v. onderstaande PowerShell script voorbeeld kun je, door gebruik te maken van SQL PowerShell Modules, een SQL Agent Job aanmaken t.b.v. MS SQL Server.

$oSRV=Get-Item SQLSERVER:\SQLSERVERNAAM\INSTANCENAAM

# Maak een SQL Agent Job...
$oJOB=New-Object Microsoft.SqlServer.Management.Smo.Agent.Job
$oJOB.parent=$oSRV.Jobserver
$oJOB.Name="Backup System Database Job"
$oJOB.Create()

# Maak een JobStep t.b.v. backup master database in de nieuwe Job...
$oStep1=New-Object Microsoft.SqlServer.Management.Smo.Agent.JobStep
$oStep1.parent=$oJOB
$oStep1.Name="Master Backup"
$oStep1.SubSystem="TransactSQL"
$oStep1.Command="BACKUP DATABASE [master] TO DISK='X:\Path\To\File.bak'"
$oStep1.OnSuccessAction = "GotoNextStep"
$oStep1.Create()

# Maak een JobStep t.b.v. backup master database in de nieuwe Job...
$oStep2=New-Object Microsoft.SqlServer.Management.Smo.Agent.JobStep
$oStep2.parent=$oJOB
$oStep2.Name="Model Backup"
$oStep2.SubSystem="TransactSQL"
$oStep2.Command="BACKUP DATABASE [model] TO DISK='X:\Path\To\File.bak'"
$oStep2.OnSuccessAction = "GotoNextStep"
$oStep2.Create()