+ Post New Thread
Results 1 to 11 of 11
Scripts Thread, Powershell Script to export Event Logs to CSV file (s) in Coding and Web Development; Been working on this for a bit and finally got it working: Code: $computer = Read-Host "Server" $creds = Read-Host ...
  1. #1

    Gatt's Avatar
    Join Date
    Jan 2006
    Posts
    6,820
    Thank Post
    883
    Thanked 685 Times in 454 Posts
    Rep Power
    505

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

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

    Code:
    $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
    Last edited by Gatt; 17th November 2011 at 07:30 PM.

  2. Thanks to Gatt from:

    DLAS (14th December 2011)

  3. #2

    Join Date
    Aug 2011
    Posts
    34
    Thank Post
    7
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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

  4. #3

    Gatt's Avatar
    Join Date
    Jan 2006
    Posts
    6,820
    Thank Post
    883
    Thanked 685 Times in 454 Posts
    Rep Power
    505
    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:

    Code:
    # +---------------------------------------------------------------------------
    # | 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 :
    Code:
     EventLogs -days 3

  5. #4

    Join Date
    Mar 2011
    Posts
    12
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    So once edited, do I just copy this to powershell and press enter?

  6. #5

    Ric_'s Avatar
    Join Date
    Jun 2005
    Location
    Boston, MA
    Posts
    7,601
    Thank Post
    110
    Thanked 771 Times in 599 Posts
    Rep Power
    183
    @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.

  7. #6

    Join Date
    Oct 2012
    Posts
    3
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    to work withou the login prompt

    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

    Quote Originally Posted by Gatt View Post
    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:

    Code:
    # +---------------------------------------------------------------------------
    # | 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 :
    Code:
     EventLogs -days 3

  8. #7

    Gatt's Avatar
    Join Date
    Jan 2006
    Posts
    6,820
    Thank Post
    883
    Thanked 685 Times in 454 Posts
    Rep Power
    505
    As long as the account you are using has the correct access rights, then you should be able to remove the $users section...

  9. #8

    Join Date
    Oct 2012
    Posts
    3
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by Gatt View Post
    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,M essage,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';Exp ression={$_.ConvertToDateTime($_.TimeWritten)}},So urceName,Message,Category,EventCode,User | `
    Export-Csv "$store\$computer-Application.csv"

  10. #9

    Gatt's Avatar
    Join Date
    Jan 2006
    Posts
    6,820
    Thank Post
    883
    Thanked 685 Times in 454 Posts
    Rep Power
    505
    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...

  11. #10

    Join Date
    Oct 2012
    Posts
    3
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by Gatt View Post
    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
    Last edited by MoOriginal; 17th October 2012 at 01:49 PM.

  12. #11

    Join Date
    Sep 2014
    Posts
    2
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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!)



SHARE:
+ Post New Thread

Similar Threads

  1. Script to delete profile at log off?
    By woody in forum Windows
    Replies: 74
    Last Post: 15th February 2013, 03:28 PM
  2. script to log dns cos some are wrong
    By browolf in forum Coding
    Replies: 0
    Last Post: 13th November 2008, 04:07 PM
  3. Replies: 8
    Last Post: 17th November 2007, 10:04 PM
  4. Replies: 13
    Last Post: 8th November 2007, 03:53 PM
  5. Replies: 9
    Last Post: 10th May 2007, 11:13 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •