PowerShell

Getting to know Powershell, from an old DBA

My team was recently tasked with finding and resolving all Errors and Warnings for every server in our environment, from Prod all the way down to Dev.  Developers are on their own for their laptops. This is a bunch of servers and a small admin team that does mostly SQL server, so I knew I needed automation.

I tried just exporting directly from each server’s Event Viewer, but that was far too cumbersome, and not really repeatable for the future.

Finally, I have an actual use for PowerShell, since SQL Server is not involved here. Best tool for the job, yes?

What follows is a step by step of how I expanded on the most basic command to get more and more out of it. I spent a lot of time on MSDN looking up cmdlets, and on Stack Overflow twice with basic questions. Even went to my “DBA” Twitter feed early in the process using #sqlhelp since a lot of my colleagues are veteran PoSH users.

Warning…its very possible I’ve gained a syntax error here and there when copying and formatting this into a post…run at your own peril 😉

Start here to see the specifics of the Get-EventLog cmdlet:
MSDN Get-EventLog

Step 1: Get a minimal command to actually execute:

#This will bring every entry from your local machines’s System log

Get-EventLog -LogName System

(Feel free to run these in your non-prod/test environment,

Step 2: Cut that list down a bit:

#Lets filter out everything but Errors and Warnings...
Get-EventLog -LogName System -EntryType "Error","Warning"

Step 3: Add a date parameter and line wrap the code (note the ` at the end of line 1):

#Still too much, how about just the last three days?
Get-EventLog -LogName System -EntryType "Error","Warning" `
-After (Get-Date).AddDays(-3)

Step 4: Connect to remote computer:

#Switch from local pc to remote machine (local is default)
Get-EventLog -LogName System -EntryType "Error","Warning" `
-After (Get-Date).AddDays(-3) -ComputerName SERVER08

Step 5: Connect to multiple remote computers:

#Add a second server to the list, note the “” around each name
Get-EventLog -LogName System -EntryType "Error","Warning" `
-After (Get-Date).AddDays(-3) -ComputerName "SERVER08","SERVER07"

Results:

PS C:\Users\Kevin3NF> Get-EventLog -LogName System -EntryType “Error”,”Warning” `
-After (Get-Date).AddDays(-3) -ComputerName “SERVER08″,”SERVER07”

Index Time EntryType Source InstanceID Message
—– —- ——— —— ———- ——-
162296 Dec 06 03:40 Error Schannel 36888 A fatal alert was generated and sent to the remote endpoint. This may r…
162295 Dec 06 03:40 Error Schannel 36874 An TLS 1.2 connection request was received from a remote client applica…

Step 6: Pick and choose the columns we care about:

# Pipe results to Select-Object to choose the properties (columns) we want
Get-EventLog -LogName System -EntryType "Error","Warning" `
-After (Get-Date).AddDays(-3) -ComputerName "SERVER08","SERVER07" `
| Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message

Results:

MachineName : SERVER08.DallasDBAs.com
EntryType : Error
EventID : 36888
Source : Schannel
TimeGenerated : 12/6/2016 3:40:24 AM
Message : A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS
protocol defined fatal error code is 40. The Windows SChannel error state is 1205.

MachineName : SERVER08.DallasDBAs.com
EntryType : Error
EventID : 36874
Source : Schannel
TimeGenerated : 12/6/2016 3:40:24 AM
Message : An TLS 1.2 connection request was received from a remote client application, but none of the cipher suites supported by the
client application are supported by the server. The SSL connection request has failed.

Step 7: let’s get something that looks more table-ish…

# Ugh. I want a table I can work with:
Get-EventLog -LogName System -EntryType "Error","Warning" `
-After (Get-Date).AddDays(-3) -ComputerName "SERVER08","SERVER07" `
| Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message `
| Format-Table -Property MachineName, TimeGenerated, EntryType, Source, EventID, Message

Results:

SERVER08.DallasDBAs.com 12/6/2016 3:39:05 AM Error Schannel 36888 A fatal alert was ge…
SERVER08.DallasDBAs.com 12/6/2016 3:38:38 AM Warning LsaSrv 40968 The Security System …
SERVER07.DallasDBAs.com 12/6/2016 4:06:30 AM Error Schannel 36888 A fatal alert was ge…
SERVER07.DallasDBAs.com 12/6/2016 4:06:30 AM Error Schannel 36874 An TLS 1.2 connectio…

(this looks like a table when run in PowerShell)

Step 8: 2 servers? Yeah, right…more like 20+!

Make a ServerList_Sample.txt file (Name it whatever you want):

Save it somewhere.

Step 9: Reference that file, refer to it in the PS script in a variable

# Store each server in ServerList_Sample.txt in a variable
$computers = Get-Content "B:\DBA\ServerList_Sample.txt";

# Loop through each server and get the logs
foreach($computer in $computers)

    {
    Get-EventLog -LogName System -EntryType "Error","Warning" `
    -After (Get-Date).AddDays(-3) -ComputerName $computer `
    | Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message `
    | Format-Table -Property MachineName, TimeGenerated, EntryType, Source, EventID, Message
    }

——————————————————————————————-
New items:
• foreach,
• $computers
• -ComputerName $computer (variable call)
• Statement braces {}

In English: For every computer in the file I give, Get the event log info.

Result:

SERVERACA18.DallasDBAs.com 12/6/2016 2:45:28 PM Warning ebdrv 34 NTPNP_PCI0184 Unqual…
SERVERACA18.DallasDBAs.com 12/6/2016 2:45:28 PM Error hpipmibt 1001 The description for …
SERVERACA18.DallasDBAs.com 12/6/2016 2:34:41 PM Warning WinRM 10149 The description for …

MachineName TimeGenerated EntryType Source EventID Message

---

SERVERACA21.DallasDBAs.com 12/9/2016 10:34:48 AM Error DCOM 10016 The description for …
SERVERACA21.DallasDBAs.com 12/9/2016 10:13:03 AM Error Service Control Manager 7034 The Acme-Calc s…

Step 10: Oh crud…a server in my server list is offline for patching!

# Store each server in ServerList_Sample.txt in a variable
$computers = Get-Content "B:\DBA\ServerList_Sample.txt";

# loop through each server
foreach($computer in $computers)

{
  # Try to make a connection to each server

  if(Test-Connection $computer -Quiet -Count 1)

  # on successful connection, go get the logs
    {
    Try
        {
        Get-EventLog -LogName System -EntryType "Error","Warning" `
        -After (Get-Date).AddDays(-3) -ComputerName $computer `
        | Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message `
        | Format-Table -Property MachineName, TimeGenerated, EntryType, Source, EventID, Message
        }
    # if an error in retrieving the logs, write it out
    Catch
        {
        Write-Verbose "Error $($error[0]) encountered when attempting to get events from $computer"
        }
    }
# if the Connection attempt failed, write this error…
    else
        {
        Write-Verbose "Failed to connect to $computer"
        }
}

---

New Items:
• if(Test-Connection…)/else…(basically a ping)
• Try / Catch blocks
• Write-Verbose for error reporting
• Nested statement Braces {}

Step 11: Output this to a .txt file for using offline in Excel

 
# Store each server in ServerList_Sample.txt in a variable
$computers = Get-Content "B:\DBA\ServerList_Sample.txt";

# loop through each server
foreach($computer in $computers)

{
  # Try to make a connection to each server

  if(Test-Connection $computer -Quiet -Count 1)

  # on successful connection, go get the logs
    {
    Try
        {
        Get-EventLog -LogName System -EntryType "Error","Warning" `
        -After (Get-Date).AddDays(-3) -ComputerName $computer `
        | Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message `
        | Format-Table -Property MachineName, TimeGenerated, EntryType, Source, EventID, Message `
        | Out-File -append B:\DBA\System.txt ;

        Get-EventLog -LogName Application -EntryType "Error","Warning" `
        -After (Get-Date).AddDays(-3) -ComputerName $computer `
        | Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message `
        | Format-Table -Property MachineName, TimeGenerated, EntryType, Source, EventID, Message `
        | Out-File -append B:\DBA\Application.txt ;
        } 
    # if an error in retrieving the logs, write it out 
    Catch 
        { 
        Write-Verbose "Error $($error[0]) encountered when attempting to get events from $computer" 
        } 
    } 
# if the Connection attempt failed, write this error… 
  else 
    { 
    Write-Verbose "Failed to connect to $computer" 
    } 
} 

---

New Items:
• Out-File –append #Simply sends the output to the specified file without overwriting
• Added a second statement to do all the same for the Application log, inside the foreach loop, writing to Application.txt

Results from system.txt:

SERVERACA18.DallasDBAs.com 12/8/2016 2:18:21 PM Error 10016 The description for…
SERVERACA18.DallasDBAs.com 12/8/2016 1:36:37 PM Warning 1014 Name resolution for…
SERVERACA18.DallasDBAs.com 12/8/2016 11:54:19 AM Warning 1014 Name resolution for…
SERVERACA18.DallasDBAs.com 12/8/2016 11:44:20 AM Error 10016 The description for…

MachineName TimeGenerated EntryType Source EventID Message

---

SERVERACA22.DallasDBAs.com 12/9/2016 10:11:00 AM Warning 1101 The SNMP Service is…
SERVERACA22.DallasDBAs.com 12/9/2016 10:11:00 AM Warning 1101 The SNMP Service is…
SERVERACA22.DallasDBAs.com 12/9/2016 10:10:55 AM Error 5719 This computer was n…

Not great…Message got chopped off, and I don’t really want the headers for each server…but still getting there.

Step 12/Final: Add variables, append to it, and write the contents out to a .csv

#Drop the existing files
Remove-Item B:\DBA\System_Test.csv
Remove-Item B:\DBA\Application_test.csv

# SERVER LIST PROPERTIES
# Get computer list to check disk space. This is just a plain text file with the servers listed out.
$computers = Get-Content "B:\DBA\ServerList.txt";
$sys = @()
$app = @()

# QUERY COMPUTER SYSTEM EVENT LOG
foreach($computer in $computers)


{ 
    # Try to make a connection to each server
    if(Test-Connection $computer -Quiet -Count 1)
      { 
      Try 
          {
          $sys += Get-EventLog -LogName System -EntryType "Error","Warning" ` 
          -After (Get-Date).AddDays(-3) -ComputerName $computer ` 
          | Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message ;

          $app += Get-EventLog -LogName Application -EntryType "Error","Warning" ` 
          -After (Get-Date).AddDays(-3) -ComputerName $computer ` 
          | Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message ; 
          } 
       # if an error in retrieving the logs, write it out 
       Catch 
          { 
          Write-Verbose "Error $($error[0]) encountered when attempting to get events from $computer" 
          } 
     } 
   # If the connection attempt failed, write this error...
   else 
     { 
     Write-Verbose "Failed to connect to $computer" 
     } 
} 
$sys | Export-Csv B:\DBA\System_Test.csv -NoTypeInformation -Append;
$app | Export-Csv B:\DBA\Application_Test.csv -NoTypeInformation -Append; 

---

New Items:
• Remove-Item – delete the existing files
• $sys = @() – declare a variable (one for system, one for application)
• $sys += – append the results to the variable
• Format-Table – removed
• Out-File – removed
• $sys | Export – Send the contents of the variable to a .csv file

Results:

A screen grab of my System.txt file:

There is only one set of headers, and the full message is there in column ‘F’, just not showing.   Some details may be missing, as not everything is exposed to Get-EventLog

This allows me to use Excel to sort, filter, group, count, and prioritize the effort needed to cleanup dozens or hundreds of servers. I expect to run this weekly until we get to minimal results, then set up alerts.

I hope you got something useful out of this. It all works on my machine, but is heavily edited for public consumption. Work on your local or in a test lab before you even think about moving to Prod. PowerShell 3.0. May or may not work in lower versions…I have no desire to check.

Shout out to Chris for helping with the very early parts of this process, as well as those who answered my Question1 and Question2 at Stack Overflow.

Update: 1/14/2017:

I decided to enhance this by dumping all the data into one file, and changing the core CmdLet from Get-EventLog to Get-WinEvent, so I can pick up “Critical” events as well as “Error” and “Warning”:

    #Drop the existing files
    Remove-Item C:\Get_WinEvent.csv
    

    # SERVER LIST PROPERTIES
    # Get computer list to check disk space. This is just a plain text file with the servers listed out.
     $computers = Get-Content "C:\ServerList.txt"; 
     $sys = @()
     $st= (Get-Date).AddDays(-7)

    # QUERY COMPUTER SYSTEM EVENT LOG
    foreach($computer in $computers)

{        
 if(Test-Connection $computer -Quiet -Count 1)
        {
   Try {$sys += Get-WinEvent -Computername $computer -FilterHashtable @{logname="System","Application";level=1,2,3;starttime=$st} `
            | Select-Object -Property machinename, LevelDisplayName, LogName, ID, ProviderName, TimeCreated, Message

       } 
   Catch 
       {
       Write-Verbose "Error $($error[0]) encountered when attempting to get events from  $computer"
       }
       } 
   else {
         Write-Verbose "Failed to connect to $computer"
        }
    
}
$sys | Export-Csv C:\Get_WinEvent.csv -NoTypeInformation -Append;

The part that defines the Critical/Error/Warning:  level=1,2,3

Thanks for reading!

Kevin3NF