Powershell Get-EventLog and Event Log messages

This post explains how to list Event Log Messages with Powershell Get-EventLog  , on multiple servers and output the results to a HTML file.

It is only one extra step to send an email attachment.

This example iterates through a list of servers and returns Error messages from the System Event Log generated in the last 24 hrs

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath


$serverpath=$basepath + "\config\servers.txt"
$outputfile="\logs\sql_server_health_eventviewer_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile


#invoke stylesheet
. .\modules\stylesheet.ps1

#intro smtp function 
. .\modules\smtp.ps1


$dt = new-object "System.Data.DataTable"
foreach ($server in get-content $serverpath)
{

	Clear-Host
	$ServerCurrent = "$server"
	$dt = Get-Eventlog -computername $server -logname system -EntryType Error -After $(Get-Date).AddHours(-24) | sort eventid |`

}
$dt | select * -ExcludeProperty Data,Category,Index,Category,CategoryNumber,ReplacementStrings,CategoryNumber,Site,SiteContainer,TimeWritten,Container,InstanceId,UserName | ConvertTo-Html -head $reportstyle -body "Server System Errors on sqlserver-dba.com Servers" | Set-Content $outputfilefull

 

See Also

SQL Server – Send email using Powershell

List failed SQL server Jobs with Powershell

 

SQL Server – Powershell Active Directory search

 Working with Powershell and Active Directory simplifies some complex tasks for the DBA.

Active Directory is LDAP compliant. This means the RFC 1779 and RFC 2247 standards are met.

This example lists all employees on an LDAP path. This method requires knowledge of the LDAP path .

 Common Name (cn)

Organizational Unit (ou)

Domain Component (dc)

 

$group = [ADSI] "LDAP://cn=MYORGAllEmployees,ou=MYORG,dc=south,dc=syborg,dc=net" 
foreach ($member in $group.member) 
{ 
$member 
} 

 

See Also

Powershell sql server security audit

Powershell default start directory

This post discusses some methods in configuring the Powershell  default start directory

 By default , the installation process will add a value to  the Start In parameter.

When you start Powershell , the default path will be  %HOMEDRIVE%%HOMEPATH%.

 Without opening Powershell , view the variable values through the DOS cmd window.

 Type: echo %HOMEDRIVE%%HOMEPATH% 

  To set up a new new start up Powershell directory , change the value in Start in.

 Method 2 – Amend $PROFILE  and Set-Location

 In your  $PROFILE file, you can add a

Set-Location "type_a_path"

where type_a_path is the path you want to start on.

See Also

Powershell sql server security audit

 

Send email from Powershell with attachment

This post  describes how to call  to add an email attachment using Powershell.

In an earlier post - SQL Server – Send email using Powershell – the new-object Net.Mail.SmtpClient() Class was used.

To add an email attachment , use New-Object System.Net.Mail.MailMessage and New-Object System.Net.Mail.Attachment, which are part of the .Net Framework

The example script includes creating the variables, the function and calling the function .

 

$emailFrom = "jvamvas@sqlserver-dba.com"
$emailTo = "jvamvas@sqlserver-dba.com"
$subject = "SQLServer-DBA.com: Powershell Function calling an SMTP server with attachment"
$body = "SQLServer-DBA.com : Send an email through SMTP in Powershell with attachment "
$smtpServer = "mysmtpserver"
$filePath = "C:\sql_server_health_sqlserver_jobs_2012-02.html"


Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath)
{
#initate message
$email = New-Object System.Net.Mail.MailMessage 
$email.From = $emailFrom
$email.To.Add($emailTo)
$email.Subject = $subject
$email.Body = $body
# initiate email attachment 
$emailAttach = New-Object System.Net.Mail.Attachment $filePath
$email.Attachments.Add($emailAttach) 
#initiate sending email 
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($email)
}

#Call Function 
sendEmail $emailFrom $emailTo $subject $body $smtpServer $filePath

 

See Also

Powershell - run script on all sql servers

Author: Jack Vamvas (http://www.sqlserver-dba.com)

List failed SQL server Jobs with Powershell

This post explains how to list failed SQL Server Jobs , on multiple SQL Server Instances and output the results to a HTML file.

 It is only one extra step to send this file as an attachment.

The example script , returns failed SQL Server Agent Jobs within the last 48 hrs, for a SQL Server Instance.

 

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath
$instancepath=$basepath + "\instances.txt"
$outputfile="\logs\sql_server_health_sqlserver_jobs_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile
#invoke stylesheet
. .\modules\stylesheet.ps1
 
$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{

$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandText = "SELECT sjh.server,sj.name, sjh.message, sjh.run_date, sjh.run_time , ‘http://www.sqlserver-dba.com/contact-me/’ as Explain
FROM msdb..sysjobhistory sjh
JOIN msdb..sysjobs sj on sjh.job_id = sj.job_id
JOIN (SELECT job_id, max(instance_id) maxinstanceid 
FROM msdb..sysjobhistory 
WHERE run_status NOT IN (1,4) 
GROUP BY job_id) a ON sjh.job_id = a.job_id AND sjh.instance_id = a.maxinstanceid 
WHERE    DATEDIFF(hh, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), GETDATE()) <= 48
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
 
}

$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle | Set-Content $outputfilefull

 

See Also

SQL Server – Send email using Powershell

SQL Agent Jobs – Schedule in seconds

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)