Powershell Script to export Event Logs to CSV file (s)

Gatt · November 17, 2011

Been working on this for a bit and finally got it working:

$computer = Read-Host "Server" 
$creds = Read-Host "Domain\User account to user"
$days = Read-Host "History (Days)"
$path = "C:\Logs"  #DO NOT add a trailing slash
$namespace = "root\CIMV2" 
$BeginDate=[system.Management.ManagementDateTimeConverter]::ToDMTFDateTime((get-date).AddDays(-$days))

Get-WmiObject -ComputerName $computer -Credential $creds `
   -Query "SELECT ComputerName,Logfile,Type,TimeWritten,SourceName,Message,Category,EventCode,User `
   FROM Win32_NTLogEvent WHERE (logfile='Application') AND (type='Error') AND (TimeWritten > '$BeginDate')" | `
   SELECT ComputerName,Logfile,Type,@{name='TimeWritten';Expression={$_.ConvertToDateTime($_.TimeWritten)}},SourceName,Message,Category,EventCode,User | `
   Export-Csv "$path\$computer-Application-Errors.csv" 

Get-WmiObject -ComputerName $computer `
   -Query "SELECT ComputerName,Logfile,Type,TimeWritten,SourceName,Message,Category,EventCode,User `
   FROM Win32_NTLogEvent WHERE (logfile='Application') AND (type='Warning') AND (TimeWritten > '$BeginDate')" | `
   SELECT ComputerName,Logfile,Type,@{name='TimeWritten';Expression={$_.ConvertToDateTime($_.TimeWritten)}},SourceName,Message,Category,EventCode,User | `
   Export-Csv "$path\$computer-Application-Warnings.csv" 

Get-WmiObject -ComputerName $computer `
   -Query "SELECT ComputerName,Logfile,Type,TimeWritten,SourceName,Message,Category,EventCode,User `
   FROM Win32_NTLogEvent WHERE (logfile='System') AND (type='Error') AND (TimeWritten > '$BeginDate')" | `
   SELECT ComputerName,Logfile,Type,@{name='TimeWritten';Expression={$_.ConvertToDateTime($_.TimeWritten)}},SourceName,Message,Category,EventCode,User | `
   Export-Csv "$path\$computer-System-Errors.csv" 
   
Get-WmiObject -ComputerName $computer `
   -Query "SELECT ComputerName,Logfile,Type,TimeWritten,SourceName,Message,Category,EventCode,User `
   FROM Win32_NTLogEvent WHERE (logfile='System') AND (type='Warning') AND (TimeWritten > '$BeginDate')" | `
   SELECT ComputerName,Logfile,Type,@{name='TimeWritten';Expression={$_.ConvertToDateTime($_.TimeWritten)}},SourceName,Message,Category,EventCode,User | `
   Export-Csv "$path\$computer-System-Warnings.csv"

The above example exports all the Errors and Warnings from the Application and System Logs

To export more logs simply copy the Get-WmiObject lines and WHERE (logfile='System') AND (type='Error') as appropriate

Comments welcome

Edited November 17, 2011 by Gatt

DLAS · December 14, 2011

This is brilliant, was looking for something just like this to audit logon events on our Terminal Services server and dump then in a folder somewhere for review, looks like you've saved me alot of the legwork!

I was planning on using Powershell as well.

Thanks

Gatt · December 14, 2011

I have actually updated the code so that it can check multiple servers at once - though the names need to be entered into the script

Also, it now has a paramter for the days and prompts only once for the credentials:

# +---------------------------------------------------------------------------
# | File : EventLogs.ps1                                          
# | Version : 1.5                                         
# | Purpose : Export Remote Event Logs to CSV. 
# | Synopsis: Creates a CSV file containing all Errors and Warnings from the 
# |           "Application", "System" & "Operations Manager" Event Logs 
# | Usage : .\EventLogs.ps1 -days NUMDAYS
# +----------------------------------------------------------------------------
# | Maintenance History                                            
# | -------------------                                            
# | Name            Date         Version         Description        
# | ------------------------------------------------------------------------------
# | Craig Wilson    25/11/2011   1.0            Initial Release
# | Craig Wilson    28/11/2011   1.1            Added '$store' variable for Log Location 
# | Craig Wilson    28/11/2011   1.2            Added Help Infomration
# | Craig Wilson    28/11/2011   1.3            BUG FIX: added "-Credential $user" switch in for all logs
# | Craig Wilson    28/11/2011   1.4            Added filter for Events
# | Craig Wilson    01/12/2011   1.5*           Added Array to loop through all servers in array and removed Paramter for servers. 
# +-------------------------------------------------------------------------------
##################
## HELP SECTION ##
##################
<#
.SYNOPSIS 
Script to export specific events from remote event logs to a CSV file
.DESCRIPTION 
This script will read the event logs of the array of Servers and export all but 
all relevant logs to a CSV File for the specified server over the period of history
requested at the command line.
Logs can be filtered by modifing the Query for the appropriate log..
.EXAMPLE 
.\EventLogs.PS1 -days 7
.NOTES 
Script may error if there are no events to record and will prompt for the password.
NO username or password information is stored by this script and nothing is written back
to the server. 
#>
#  Specify Command Line parameters
param([string]$days=$(throw "Days cannot be null"))
$servers = @("SERVER1", "SERVER2", "SERVER3")
$user = Get-Credential
#Set namespace and calculate the date to start from
$namespace = "root\CIMV2" 
$BeginDate=[system.Management.ManagementDateTimeConverter]::ToDMTFDateTime((get-date).AddDays(-$days))
$store = "C:\Logs"  # No trailing slash, Folder must already exist
foreach ($computer in $servers)
{
   # Get the Application Log and export to CSV
   Get-WmiObject -ComputerName $computer -Credential $user `
       -Query "SELECT ComputerName,Logfile,Type,TimeWritten,SourceName,Message,Category,EventCode,User `
           FROM Win32_NTLogEvent WHERE (logfile='Application') AND (type!='Information') AND (EventCode!='1062') `
           AND (EventCode!='9001') AND (EventCode!='1517') AND (EventCode!='16434') AND (EventCode!='16435') `
           AND (EventCode!='30969') AND (EventCode!='1202') AND (EventCode!='1517')  AND (EventCode!='257') `
           AND (TimeWritten > '$BeginDate')" | `
           SELECT ComputerName,Logfile,Type,@{name='TimeWritten';Expression={$_.ConvertToDateTime($_.TimeWritten)}},SourceName,Message,Category,EventCode,User | `
           Export-Csv "$store\$computer-Application.csv" 
   # Get the System Log and export to CSV
   Get-WmiObject -ComputerName $computer -Credential $user `
       -Query "SELECT ComputerName,Logfile,Type,TimeWritten,SourceName,Message,Category,EventCode,User `
           FROM Win32_NTLogEvent WHERE (logfile='System') AND (type!='Information') AND (EventCode!='257') AND (TimeWritten > '$BeginDate')" | `
           SELECT ComputerName,Logfile,Type,@{name='TimeWritten';Expression={$_.ConvertToDateTime($_.TimeWritten)}},SourceName,Message,Category,EventCode,User | `
           Export-Csv "$store\$computer-System.csv" 
}

You will need to change a few parameters to suit your environment :

$servers = Array of all servers you want to get the logs from

$store = Location where logs will be saved

In each of the -Query - amend the filters as needed to remove any events that aren't needed - you may need to play with this a bit to get it right - but it should be safe to remove anything after the tpe != 'information'..

Command to run to collect previous 3 days worth of logs is :

 EventLogs -days 3

ColonelHawx · August 31, 2012

So once edited, do I just copy this to powershell and press enter?

Ric_ · August 31, 2012

@ColonelHawx - You should put the script in a .ps1 file (use your favourite text editor) and follow the instructions in the quick start guide available at Download: Windows PowerShell Quick Reference - Microsoft Download Center - Confirmation for how to run it.

MoOriginal · October 11, 2012

Apologies as a noob powersheller - I dont have time to start at the beginning

this is useful script and ive run this and it works great.

However in order to run this as a SQL Scheduled task I can do myself call it from a bat file

- but I was wondering how to get rid of the login prompt that would stop it running as a scheduled task

Is it just a matter of removing all the the $user calls

eg

-Credential $user

Thanks in advance

I have actually updated the code so that it can check multiple servers at once - though the names need to be entered into the script

Also, it now has a paramter for the days and prompts only once for the credentials:

# +---------------------------------------------------------------------------
# | File : EventLogs.ps1                                          
# | Version : 1.5                                         
# | Purpose : Export Remote Event Logs to CSV. 
# | Synopsis: Creates a CSV file containing all Errors and Warnings from the 
# |           "Application", "System" & "Operations Manager" Event Logs 
# | Usage : .\EventLogs.ps1 -days NUMDAYS
# +----------------------------------------------------------------------------
# | Maintenance History                                            
# | -------------------                                            
# | Name            Date         Version         Description        
# | ------------------------------------------------------------------------------
# | Craig Wilson    25/11/2011   1.0            Initial Release
# | Craig Wilson    28/11/2011   1.1            Added '$store' variable for Log Location 
# | Craig Wilson    28/11/2011   1.2            Added Help Infomration
# | Craig Wilson    28/11/2011   1.3            BUG FIX: added "-Credential $user" switch in for all logs
# | Craig Wilson    28/11/2011   1.4            Added filter for Events
# | Craig Wilson    01/12/2011   1.5*           Added Array to loop through all servers in array and removed Paramter for servers. 
# +-------------------------------------------------------------------------------
##################
## HELP SECTION ##
##################
<#
.SYNOPSIS 
Script to export specific events from remote event logs to a CSV file
.DESCRIPTION 
This script will read the event logs of the array of Servers and export all but 
all relevant logs to a CSV File for the specified server over the period of history
requested at the command line.
Logs can be filtered by modifing the Query for the appropriate log..
.EXAMPLE 
.\EventLogs.PS1 -days 7
.NOTES 
Script may error if there are no events to record and will prompt for the password.
NO username or password information is stored by this script and nothing is written back
to the server. 
#>
#  Specify Command Line parameters
param([string]$days=$(throw "Days cannot be null"))
$servers = @("SERVER1", "SERVER2", "SERVER3")
$user = Get-Credential
#Set namespace and calculate the date to start from
$namespace = "root\CIMV2" 
$BeginDate=[system.Management.ManagementDateTimeConverter]::ToDMTFDateTime((get-date).AddDays(-$days))
$store = "C:\Logs"  # No trailing slash, Folder must already exist
foreach ($computer in $servers)
{
   # Get the Application Log and export to CSV
   Get-WmiObject -ComputerName $computer -Credential $user `
       -Query "SELECT ComputerName,Logfile,Type,TimeWritten,SourceName,Message,Category,EventCode,User `
           FROM Win32_NTLogEvent WHERE (logfile='Application') AND (type!='Information') AND (EventCode!='1062') `
           AND (EventCode!='9001') AND (EventCode!='1517') AND (EventCode!='16434') AND (EventCode!='16435') `
           AND (EventCode!='30969') AND (EventCode!='1202') AND (EventCode!='1517')  AND (EventCode!='257') `
           AND (TimeWritten > '$BeginDate')" | `
           SELECT ComputerName,Logfile,Type,@{name='TimeWritten';Expression={$_.ConvertToDateTime($_.TimeWritten)}},SourceName,Message,Category,EventCode,User | `
           Export-Csv "$store\$computer-Application.csv" 
   # Get the System Log and export to CSV
   Get-WmiObject -ComputerName $computer -Credential $user `
       -Query "SELECT ComputerName,Logfile,Type,TimeWritten,SourceName,Message,Category,EventCode,User `
           FROM Win32_NTLogEvent WHERE (logfile='System') AND (type!='Information') AND (EventCode!='257') AND (TimeWritten > '$BeginDate')" | `
           SELECT ComputerName,Logfile,Type,@{name='TimeWritten';Expression={$_.ConvertToDateTime($_.TimeWritten)}},SourceName,Message,Category,EventCode,User | `
           Export-Csv "$store\$computer-System.csv" 
}

You will need to change a few parameters to suit your environment :

$servers = Array of all servers you want to get the logs from

$store = Location where logs will be saved

In each of the -Query - amend the filters as needed to remove any events that aren't needed - you may need to play with this a bit to get it right - but it should be safe to remove anything after the tpe != 'information'..

Command to run to collect previous 3 days worth of logs is :

 EventLogs -days 3

Gatt · October 11, 2012

As long as the account you are using has the correct access rights, then you should be able to remove the $users section...

MoOriginal · October 17, 2012

As long as the account you are using has the correct access rights, then you should be able to remove the $users section...

I have another question please.

Got the non credential thing to work - so thanks

I have tried adding some extra event codes to the application log read section

specifically 18270 - to show sql differential backup changes and 18264 to show sql full backups.

After adding though - these are still not being added to the generated csv file

Im thought as you specify AND (type!='Information') and both the new event id's show as information they would ?

Is there something im doing wrong?

# Get the Application Log and export to CSV

Get-WmiObject -ComputerName $computer `

-Query "SELECT ComputerName,Logfile,Type,TimeWritten,SourceName,Message,Category,EventCode,User `

FROM Win32_NTLogEvent WHERE (logfile='Application') AND (type!='Information') AND (EventCode!='1062') `

AND (EventCode!='9001') AND (EventCode!='1517') AND (EventCode!='18270') AND (EventCode!='18264') AND (EventCode!='16434') AND (EventCode!='16435') `

AND (EventCode!='30969') AND (EventCode!='1202') AND (EventCode!='1517') AND (EventCode!='257') `

AND (TimeWritten > '$BeginDate')" | `

SELECT ComputerName,Logfile,Type,@{name='TimeWritten';Expression={$_.ConvertToDateTime($_.TimeWritten)}},SourceName,Message,Category,EventCode,User | `

Export-Csv "$store\$computer-Application.csv"

Gatt · October 17, 2012

Are you wanting to see eventcodes 18270 and 18264?

The event codes that are listed in the code are those you want to Exclude from the logs (Hence the != )

If you want to return them, then just remove them from the code above...

MoOriginal · October 17, 2012

Are you wanting to see eventcodes 18270 and 18264?
The event codes that are listed in the code are those you want to Exclude from the logs (Hence the != )
If you want to return them, then just remove them from the code above...

Thank you!

So as from your original code they weren't in there to start with so should have been reported anyways?

If that's right mine weren't being reported back hence my confusion

Edited October 17, 2012 by MoOriginal

shaadybates · September 4, 2014

What are the odds someone can still help me with this after 2 years!? I'm not sure what I have to change to get this to work for my pc... (completely new to EVERYTHING!)

Ranmatt · March 17, 2015

I am having an issue with getting the begin date to work correctly, I want it to only pull from the last 31 days but regardless of what I set it pulls everything.

Any help would be much appreciated.

Thank you,

Gatt · March 18, 2015

Sent you a PM @Ranmatt

Ranmatt · March 20, 2015

I finally got the entire script working, thank you for your help.

Below are the 2 scripts, I apologize if they look a bit clunky, but they work:

Below is the script to pull all Event Logs for each server, filter them to only display Warnings, Failures, and FailureAudits for Application, System, and Security logs and then remove all duplicate EventIDs so only 1 of each is shown. it then exports that info into a .CSV per server.

param([string]$days= "31" )

$servers = @("Server1", "Server2" "Server3", "Etc")

$user = Get-Credential

#Set namespace and calculate the date to start from

$namespace = "root\CIMV2"

$BeginDate=[System.Management.ManagementDateTimeConverter]::ToDMTFDateTime((get-date).AddDays(-$days))

$store = "C:\Powershell\MonthlyMaintenance"

foreach ($computer in $servers)

{

$filter="TimeWritten >= '$BeginDate' AND (type='Warning' OR type='Error' OR type='FailureAudit')"

Echo "Pulling Event Logs for $computer ..."

Get-WmiObject Win32_NTLogEvent -computername $computer -Filter $filter |

sort eventcode -unique |

select Computername,

Logfile,

Type,

@{N='TimeWritten';E={$_.ConvertToDateTime($_.TimeWritten)}},

SourceName,

Message,

Category,

EventCode,

User |

Export-CSV C:\Powershell\MonthlyMaintenance\$computer-Filter.csv

}

Echo "Done."

Garacesh · March 23, 2015

Can Get-WmiObject be used to pull back all event types and use filters? Or do you have to stick with using filters after-the-fact (pipe to FROM, WHERE, SELECT etc)

I have a script that uses Get-WinEvent to pull logon events but it's kludgy and has to run in Powershell 2 as Get-WinEvent doesn't work in P3+ if you're not en-US locale. Really could do with rewriting it from scratch.

Powershell Script to export Event Logs to CSV file (s)

Recommended Posts

Gatt

DLAS

Gatt

ColonelHawx

Ric_

MoOriginal

Gatt

MoOriginal

Gatt

MoOriginal

shaadybates

Ranmatt

Gatt

Ranmatt

Garacesh

Create an account or sign in to comment

Create an account

Sign in

Recent Threads

Confirm acknowledgement of parental consent requirement for the Additional Google services

Lenovo Tablet Firmware

Outloook 365 - Classic

Home

Forums

What's New